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.
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?
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
statement_top calls --> function() calls --> ( statement1_in_function invokes trigger --> trigger_on_statement1_in_function calls --> statement2_in_function)7.4 Order of Events
statement_top calls --> function() calls --> ( statement1_in_function calls --> statement2_in_function) invokes trigger --> trigger_on_statement1_in_functionThis 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.
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.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 http://jdbc.postgresql.org. The Tcl client interface has also been removed. There are several Tcl interfaces now hosted at http://gborg.postgresql.org.Several projects have moved from core over time to different locations. JDBC has its own server. Other projects can be found at both http://gborg.postgresql.org and http://www.pgfoundry.org .
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