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 elein@varlena.com with the Subject: pg_settings scripts and I will choose one or more entries for a future publication.

Contributors: elein at varlena.com

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.

Initialization

A dump/restore using pg_dump is required for those wishing to migrate data from any previous release.

Incompatibilities

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.

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.

8.0 Order of Events

   statement_top 
      calls --> function() 
         calls --> (
           statement1_in_function 
               invokes trigger --> trigger_on_statement1_in_function
         calls --> statement2_in_function)

   statement_top 
      calls --> function() 
         calls --> (
           statement1_in_function 
         calls --> statement2_in_function)
      invokes trigger --> trigger_on_statement1_in_function

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 Name	New Name	Comment
virtual_host	listen_addr	Two collapsed into one variable. Example listen_addr=localhost (default)
tcpip_socket	listen_addr
sort_mem	work_mem	sort_mem supported by SET and SHOW
vacuum_mem	maintenance_work_mem	vacuum_mem supported by SET and SHOW
log_pid	log_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.
log_log_timestamp
log_log_source_port
syslog	log_destination	Name 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_depth	max_stack_depth	Now 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.

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).

Overflow in integer arithmetic operations is now detected and reported as an error.

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.

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.

	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 http://jdbc.postgresql.org. The Tcl client interface has also been removed. There are several Tcl interfaces now hosted at http://gborg.postgresql.org.

Contributors: elein at varlena.com