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

14-Feb-2005 Issue: 97

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

Josh's 8.0 Annotated postgresql.conf
Tuning for 8.0 12-Feb-2005

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, elein at

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';
If only we had a "kind_of_like" function. Substrings, LIKE and regular expressions will not necessarily fit the bill here. They are awkward and will never match all of the free-form ways people may have stored the phone numbers. It sounds like a call for a perl function!

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';
This function also has the advantage that it can be used as an index. If the telephone column in this case is used often enough to require an index and the query is usually on just_digits(telephone), you can create a functional index on that column.
	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
Your installation must have been built --with-perl in order to create plperl functions.

Contributors: fiona fbissett at, Jeff Davis jdavis-pgsql at, Scott Marlowe smarlowe at, Tom Lane tgl at, elein at

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
Of course, you can use the various options to pg_dump that can create the database on the target installation, restore OIDS, etc. In the case of creating a database, you would not necessarily name the database since it would not exist yet.

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

[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, Tom Lane tgl at, Richard Huxton dev 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