PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

31-Jan-2005 Issue: 95

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see

pg_settings: Configuration Data
Configuration Information in your database 29-Jan-2005

Did you know that your GUC settings are available from psql? (Your GUC variables are the Global User Configuration variables that you set usually in postgresql.conf. I knew you knew that already.) The updateable view pg_settings is where to look for your configuration settings.

A select * from pg_settings will whiz by information about your settings that you really wanted to have at hand immediately. Try it.

These columns are available for querying.

	 db=# \d pg_settings
	 View "pg_catalog.pg_settings"
	   Column   | Type | Modifiers
	 name       | text |
	 setting    | text |
	 category   | text |
	 short_desc | text |
	 extra_desc | text |
	 context    | text |
	 vartype    | text |
	 source     | text |
	 min_val    | text |
	 max_val    | text |

The information in the columns is very good stuff. See here for report formatted output of select * on a new 8.0 database.

This is a view that is updateable for the current session. You cannot INSERT or DELETE, but you can UPDATE settings for the current session. Some settings may not be appropriate on a per session basis. Whether the settings can be changed depends on the context of the variable. For example, it would be a Bad Idea to reset the number of wal_buffers per session. Also some of the settings can only be changed by the database super user.

	db=# update pg_settings set setting=10 where name='wal_buffers';
	ERROR:  parameter "wal_buffers" cannot be changed after server start

This updateable view is clearly a half of a feature. It is begging for a little reporting script to format it nicely to hang on your wall. It could also be the basis of that GUC configurator that you were meaning to write some day.

What?! A Contest?
I will be happy to publish a script formatting the output of pg_settings. Send your entries to with the Subject: pg_settings scripts and I will choose one or more entries for a future publication.

Contributors: elein at
Migration to version 8.0
8.0 Release Notes 30-Jan-2005

This article continues the Overview of the 8.0 Release notes begun in Issue #94. That article dealt with new features. This article reviews the migration and incompatibility between 7.4 and 8.0. This issue does not cover all incompatibilities listed in the Release Notes. Some will be covered in a future issue.

The Release Notes for all releases are in Appendix E in the PostgreSQL documentation.


A dump/restore using pg_dump is required for those wishing to migrate data from any previous release.
This is expected and standard procedure for major releases. An alternative to dump and restore is to use Slony-I to replicate the 7.4 database to an 8.0 replica and then promote the 8.0 replica to master.


Functions declared STABLE or IMMUTABLE always use the snapshot of the calling query, and therefore do not see the effects of actions taken after the calling query starts, whether in their own transaction or other transactions. Such a function must be read-only, too, meaning that it cannot use any SQL commands other than SELECT.
STABLE or IMMUTABLE functions always give the same result when given the same input for the duration of the statement or transaction. Nothing in the function should affect that. Therefore the dataset that is visible to the statement which called the function should be the dataset visible to the function.

Non-deferred AFTER triggers are now fired immediately after completion of the triggering query, rather than upon finishing the current interactive command. This makes a difference when the triggering query occurred within a function: the trigger is invoked before the function proceeds to its next operation.
For non-deferred triggers the order of firing has changed so the trigger is fired on completion of the statement that invoked it instead of the top level statement in the query. For example, if a statement called a function which in turned called two more statements and the first statement had a trigger on it, the trigger execution would be fired earlier than before.

8.0 Order of Events

      calls --> function() 
         calls --> (
               invokes trigger --> trigger_on_statement1_in_function
         calls --> statement2_in_function)
7.4 Order of Events
      calls --> function() 
         calls --> (
         calls --> statement2_in_function)
      invokes trigger --> trigger_on_statement1_in_function
This can affect the trigger function if, for example, it was relying on the completion of statement2_in_function before firing.

As irritating as it is, several configuration variables changed names and meanings. This is the overview of the differences. The enhanced functionality is quite welcome, however.

Old NameNew NameComment
virtual_hostlisten_addr Two collapsed into one variable. Example listen_addr=localhost (default)
sort_memwork_memsort_mem supported by SET and SHOW
vacuum_memmaintenance_work_memvacuum_mem supported by SET and SHOW
log_pidlog_line_prefix A printf style format string which can include %u (user), %r (remote host), %p (process id), %t (timestamp), %c (session id), %i (command tag) and other useful bits of data.
sysloglog_destinationName change.
log_statement Now controls which statements to log: ddl (Data Definitions), mod (Data Modification), or all (Both).
log_duration Now only prints when log_statement prints the query
max_expr_depthmax_stack_depthNow measures physical stack size rather than expression nesting depth. Set to the actual stack size limit enforced by the kernel (as set by ulimit -s or local equivalent), less a safety margin of a megabyte or so.

The length() function no longer counts trailing spaces in CHAR(n) values.

This is in keeping with the changes to the CHAR() datatype to handle spaces correctly.

Casting an integer to BIT(N) selects the rightmost N bits of the integer, not the leftmost N bits as before.
This change can be a big problem if you are relying on casting an int to a BIT() to pick up the left bits. Now is the time to check your code.

Updating an element or slice of a NULL array value now produces a non-NULL array result, namely an array containing just the assigned-to positions.

Syntax checking of array input values has been tightened up considerably. Junk that was previously allowed in odd places with odd results now causes an error. Empty-string element values must now be written as "", rather than writing nothing. Also changed behavior with respect to whitespace surrounding array elements: trailing whitespace is now ignored, for symmetry with leading whitespace (which has always been ignored).

Many syntax changes were made to arrays and array operators. They now behave more as expected, particularly with NULL arrays, arrays with NULL values and whitespace.

Overflow in integer arithmetic operations is now detected and reported as an error.
Everyone who has been bitten by this and all the people who were not breathe a sigh of relief for this change.

The arithmetic operators associated with the single-byte "char" data type have been removed.

Anyone generating alphanumeric sequences by adding and subtracting characters should have a look at their code. I know of at least one instance where this was being used...

The extract() function (also called date_part) now returns the proper year for BC dates. It previously returned one less than the correct year. The function now also returns the proper values for millennium and century.
This bug fix is helpful for people calculating a wide range of dates.

psql's \copy command now reads or writes to the query's stdin/stdout, rather than psql's stdin/stdout. The previous behavior can be accessed via new pstdin/pstdout parameters.
This is a nice quick way to enter small amounts of data into your table without resorting to INSERT statements or redirect from the shell. However, if you do like redirecting from the shell, now use pstdin and pstdout instead of stdin and stdout. One more thing, remember \copy does not take a semi-colon, but an SQL copy does.
	db=# \copy foo from stdin
	Enter data to be copied followed by a newline.
	End with a backslash and a period on a line by itself.
	>> 9    nine    0
	>> \.
	db=# \copy foo to stdout
	5       five    0
	6       six     0
	7       seven   0
	9       nine    0

The JDBC client interface has been removed from the core distribution, and is now hosted at The Tcl client interface has also been removed. There are several Tcl interfaces now hosted at
Several projects have moved from core over time to different locations. JDBC has its own server. Other projects can be found at both and .

Contributors: elein at

Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Search General Bits & Search WWW