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

29-Sep-2003 Issue: 45

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

Not that trivial PostgreSQL trivia
Oh, no! It's a Test! 26-Sept-2003

These are some specific questions about PostgreSQL functionality. The answers are all available in various issues of General Bits as well as the current 7.3 documentation.

This test is for fun and learning only. Give it a try. Let me know if it was easy or hard. But have fun.

  • 1. What does SELECT coalesce( x, y ); return when:
    • a. x is NULL, y is 16
    • b. x is 16, y is NULL
    • c. x is NULL, y is NULL
    • d. x is 16, y is 17

  • 2. Will these WHERE clauses use an index if one exists on the column?
    • a. WHERE timestamp_value = '12/31/59'
    • b. WHERE bigint_value = 3
    • c. WHERE text_value LIKE '% the %'
    • d. WHERE text_value in ( 'val_one', 'val_two', 'val_three')
    • e. which one of these will give you an unexpected answer?

  • 3. What would the ORDER BY clause be when you wanted to sort NULLs in a column to the top, but keep the remaining columns in regular sort order?
    • a. ORDER BY NULL
    • b. ORDER BY column WITH NULL, column
    • c. ORDER BY column IS NOT NULL ASC
    • d. ORDER BY column IS NOT NULL DESC, column
    • e. ORDER BY column

  • 4. In postgresql.conf, max_fsm_relations should be set to:
    • a. 10
    • b. The maximum number of connections allowed
    • c. The number of all of the tables in all of the databases.
    • d. The amount of free cache space on your hard drive

  • 5. Timestamps with timezones are stored with the:
    • a. UTC time zone
    • b. Server time zone
    • c. Client time zone
    • d. Specified time zone

  • 6. Which of the following words should not be used as identifiers in PostgreSQL:

  • 7.1 RULES should be used for functionality applicable to:
  • 7.2 TRIGGERS should be used for functionality applicable to:
    • a. a query
    • b. each row
    • c. views
    • d. functions

  • 8. A plpgsql function is executed
    • a. in the server
    • b. in the client application
    • c. in psql
    • d. in either the client or the server

  • 9. A function created with IMMUTABLE means that:
    • a. The function will never change
    • b. It will always return the same value for the same input
    • c. The function is expected to never raise an error
    • d. The function returns null for a null argument

  • 10. A function created with STRICT means that:
    • a. It should not be cached
    • b. It should raise an error on any failure
    • c. Will not be run if an argument is null
    • d. It will always return a non null value

The answers to these questions will be published in the next edition of General Bits.

Editor: elein at
Double Storage
[GENERAL] update field using substrings of another field 22-Sep-2003

A table containing a column with an unformatted phone number and another column with a formatted phone number is wanted. The question is how to set one from the other to ensure they are always "the same." Using elaborate substring functions, it was suggested that the plain number can be converted into the formatted number. This in turn can be stored in the second column with a trigger.

This problem illustrates the primary reason why it is not a good idea to store the same information twice. To ensure that the values stay in sync and are always equal often elaborate checks, triggers and formatting are required. Unless the checks are made completely fault-proof, somehow, somewhere, one will be updated with a value and the other will not.

The actual requirement here is to provide the one phone number in both formatted and unformatted form. This is easily done using various formatting functions alone. The first query here will get you the unformatted phone number. The second will give you a formatted version and the third will get you both. It is assumed that the phone number is numeric. If it is not, cast it to numeric.

	select fname, lname, phone from addresses;
	select fname, lname, to_char( phone, 'FM(999)999-9999') from addresses;
	select fname, lname, phone, to_char( phone, 'FM(999)999-9999') from addresses;
	select fname, lname, phone from addresses;

A view can be created to retrieve the formatted version easily if it needs to be done often. The base datum, the phone number, need be stored only once eliminating verification and the possibility of having the number and the formatted number be different. This also shows the distinction between the data and the formatted output.

Contributors: Dave [Hawk-Systems] dave at, Doug McNaught doug at, Tom Lane tgl at, Andreas Fromm Andreas.Fromm at
A Successful Recovery
[GENERAL] Database Recovery Procedures 16-Sep-2003

Everyone likes a good tale with a happy ending. But they start bad... For the first time in six years, a table or tables were corrupted due to space filling up on a server. Ideally the data should be recovered instead of resorting to a restore.

First a "SELECT * ..." revealed that the server could not come up. These were the error messages:

	LOG:  ReadRecord: unexpected pageaddr 5/27498000 in log file 5, segment 45,
	offset 4816896
	LOG:  redo is not required
	PANIC:  XLogWrite: write request 5/2D498000 is past end of log 5/2D498000
	DEBUG:  reaping dead processes
	LOG:  startup process (pid 17031) was terminated by signal 6
	LOG:  aborting startup due to startup process failure

Our hero, Tom, suggests that the startup failure is a bug in 7.3.3 and that an update to 7.3.4 is appropriate. This upgrade does not require a dump and restore (which would be impossible!) and it just so happened that the 7.3.4 release was readily available. The installation was quickly updated and so the server could restart and further analysis was possible.

A "SELECT * ... LIMIT 5" from the table known to be corrupt now gives this error:

	PANIC:  read of clog file 5, offset 16384 failed: Success
	server closed the connection unexpectedly
	        This probably means the server terminated abnormally
	        before or while processing the request.
	The connection to the server was lost. Attempting reset: Failed.
The prognosis does not look good at this point. However, following up on the error message, the request was made for the names and sizes of files in the $PGDATA/pg_clog directory. This showed:
	-rw-------    1 postgres users      262144 Jul 20 15:53 0000
	-rw-------    1 postgres users      262144 Jul 31 12:57 0001
	-rw-------    1 postgres users      262144 Aug 12 17:32 0002
	-rw-------    1 postgres users      262144 Aug 26 00:15 0003
	-rw-------    1 postgres users      262144 Sep  9 23:44 0004
	-rw-------    1 postgres users       16384 Sep 10 21:21 0005
Obviously there is something wrong with file 0005. Note that the offset that caused the panic is the same as the size of the file. The server is trying to read off the end of the file for a transaction the pg_clog did not record. pg_clog tracks transactions. It was deduced that it was likely that pg_clog file 0005 was short because of the original space issue.

The next suggestion was that the server be shutdown and the file be artificially increased by adding an 8K page of 0s at the end. This would at least alleviate the problem of running off the end of the file. Adding more than one page might be necessary and if data were recovered a good data consistency check is imperative.

To add the zeros to the file the following statement was used. /dev/zero is a source for 0 data. dd, which copies binary data is told that the output should be 8K is size and that the input from /dev/zero. The output should be appended to the problem pg_clog file.

	dd bs=8k count=1 < /dev/zero >> $PGDATA/pg_clog/0005

After this operation, it was discovered that all of the data from the corrupt tables was indeed present. And everyone breathed a sigh of relief.

This is not a technique that should be tried haphazardly. This particular set of error messages led to this particular solution. When in doubt, ask for help!

Contributors: Keith C. Perry netadmin at, Tom Lane tgl at
Self referential Foreign Key
[SQL] weird(to me) request 25-Sep-2003

This is another twist on the standard example of the people table which contains parents and kids. The table contains all people individually, but the kid have a reference to one of their parents. This is usually an example of inheritance, however, this example will not use inheritance. Instead it will use simple referential integrity.

The actual case in question is a table of accounts. Accounts may have master accounts. If they do have a master account, that account must be a row in the account table.

	account   master_account   details
	1         NULL            ...
	2         NULL            ...
	4         2               ...
	5         2               ...
	6         3               ... <-- This is the case to prevent
To ensure that all master accounts, if present are legitimate accounts in their own right define a foreign key constraint on the master_account referencing account.
	my_self_fk FOREIGN KEY (master_account) REFERENCES accounts (account);
This constraint allows master_account to be NULL, but when it is not NULL, the value will be a proper account number.

Contributors: Larry Rosenman ler at, Adam Wieckowski adam-wieckowski 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