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:

  ARE	BEGIN	CURRENT_ROLE	DEFAULT
  IS	MOVE	NOT	OFFSET
  OPERATOR	PRIMARY	SOME	UNDER

  
  
• 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 varlena.com

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 hawk-systems.com, Doug McNaught doug at mcnaught.org, Tom Lane tgl at sss.pgh.pa.us, Andreas Fromm Andreas.Fromm at physik.uni-erlangen.de

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.
	!#

	-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

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 vcsn.com, Tom Lane tgl at sss.pgh.pa.us

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

	ALTER TABLE accounts ADD CONSTRAINT 
	my_self_fk FOREIGN KEY (master_account) REFERENCES accounts (account);

Contributors: Larry Rosenman ler at lerctr.org, Adam Wieckowski adam-wieckowski at tlen.pl, Richard Huxton dev at archonet.com