The latest draft of Josh Berkus' 8.0 Annotated postgresql.conf is now available in html and pdf formats.

Things have changed a bit for the new major release of 8.0 so as you prepare to upgrade you will certainly want to review the changed options in the postgresql.conf file in your data directory.

For those of us also managing the configuration of 7.4 databases, the original 7.4 Annotated postgresql.conf in html and OpenOffice formats are still available.

All of these links will remain available from General Tidbits.

Contributors: josh at agliodbs.com, elein at varlena.com

Use Perl to handle difficult Expressions [GENERAL] regular expressions in query 11-Feb-2005

A database column containing a phone number may have other characters besides digits embedded in it. Usually, this type of data is stored as digits only, and formatted on input or output, enabling the queries to be against just the numbers.

However, in this case, the data in the column may have a variety of other characters to be ignored for querying. The query desired is

	SELECT telephone FROM addresses
	WHERE user_id = 'bob' 
	AND telephone kind_of_like '1115551212';

This is perl function that takes a string and eliminates all of the non-digit characters.

	create or replace function just_digits(text)
	returns text as
	$$
        	my $innum = $_[0];
        	$innum =~ s/\D//g;
        	return $innum;
	$$ language 'plperl'

This enables the query to use a function to do the qualification.

	SELECT telephone FROM addresses
	WHERE user_id = 'bob' 
	AND just_digits(telephone) = '1115551212';

	CREATE INDEX teldigits ON addresses ( just_digits(telephone) );

Note that the $$ delimiters used in the function definition are only available on 8.0. In 7.4 you must use single quotes. If you do not have plperl defined in your database, you can create it using:

	createlang plperl dbname

Contributors: fiona fbissett at blueyonder.co.uk, Jeff Davis jdavis-pgsql at empires.org, Scott Marlowe smarlowe at g2switchworks.com, Tom Lane tgl at sss.pgh.pa.us, elein at varlena.com

Dump and Reload for Upgrades Use the latest pg_dump 13-Feb-2005

When upgrading major versions, a database dump from the old version needs to be loaded into the new version. If you have access to the pg_dump executable to the latest version it is highly recommended that you use it to dump the old version's database. pg_dump is constantly being improved, particularly with regard to dependencies and it is backwards compatible.

You can dump and load in one shell command. Assuming that the pg_dump on your PATH is the latest pg_dump, for example 8.0.1's pg_dump, and the old database is on 'otherhost' and the new database is on 'localhost' you can do this:

	pg_dump -h otherhost dbname | psql -h localhost dbname

Dependency issues usually show up as errors indicating something was referenced, but not yet defined. The dependency correction in pg_dump tries to ensure that the objects are loaded in an order to eliminate these types of errors.

An example of this type of error is if a view is defined on a table before the table is defined. Or if a reference is made to a function before the function is defined.

If you see these sorts of errors, try the most up to date version of pg_dump.

Contributors: elein at varlena.com

Using SELECT FOR UPDATE [GENERAL] basic pg lock question 02-Feb-2005

Suppose you have an application used by several users. This application selects several rows and allows the users to edit and update the rows. The question is: how best to prevent deadlocks and errors.

The first and most important note is that the application should never hold locks on sets of rows at the whim of users. Select the rows for the user to edit without locking them. This will prevent the problem where the user goes out to lunch, home, or on vacation while holding the locks because they did not hit the commit button.

To prevent collisions and invalid updates, when the user hits the submit button, you can reselect the row for update. Then you can compare the selected row with the original row. If they do not match, then someone else has updated the row. At this point you need to decide how to handle this situation. You can carefully code a merge of the changes and update that. You can release the lock and tell the user that someone else has updated the row and possibly give them the chance to re-edit their changes. You can just give an error message and make them start again.

Limit the SELECT FOR UPDATE and comparison or merge to as small of a code set as possible to limit the duration of the locks. Under no circumstances hold the lock while waiting for the user to decide what to do.

Contributors: Rick Schumeyer rschumeyer at ieee.org, Tom Lane tgl at sss.pgh.pa.us, Richard Huxton dev at archonet.com