This announcement is included verbatim from the ANNOUNCE mailing list

Its been almost 4 weeks since PostgreSQL 7.4 was released, and, as with all new releases, several bugs have been identified as administrators migrate their production databases up from older releases.

As the list of Changes since 7.4 is fairly small, they are included in this email:

• Fixed bug in "CREATE SCHEMA" parsing in ECPG (Michael)
• Fix compile error when "--enable-thread-safety" and "--with-perl" are used together (Peter)
• Fix for subqueries that used hash joins (Tom)
• Fix free space map compaction bug (Tom)
• Fix for Borland compiler build of libpq (Bruce)
• Fix netmask() and hostmask() to return the maximum-length masklen (Tom)
• Several "contrib/pg_autovacuum" fixes
• Allow compile of "contrib/cube" under Cygwin (Jason Tishler)
• Fix Solaris use of password file when no passwords are defined (Tom)
• JDBC fix for thread problems, other fixes
• Fix for bytea index lookups (Joe)
• Fix information schema for bit data types (Peter)
• Force zero_damaged_pages to be on during recovery from WAL
• Prevent some obscure cases of "variable not in subplan target lists"
• Make PQescapeBytea and byteaout consistent with each other (Joe)
• Escape bytea output for bytes > 0x7e(Joe)
• Added missing SPI_finish() calls to dblink's get_tuple_of_interest() (Joe)
• New Czech FAQ
• Fix information schema view constraint_column_usage for foreign keys (Peter)
• ECPG fixes (Michael)
• Fix bug with multiple IN subqueries and joins in the subqueries (Tom)
• Allow COUNT('x') to work (Tom)
• Install ECPG include files for Informix compatibility into separate directory (Peter)
• Fix SSL memory leak (Neil)
• Prevent "pg_service.conf" from using service name as default dbname (Bruce)
• Fix local ident authentication on FreeBSD (Tom)

http://www.postgresql.org/mirrors-www.html

http://bt.postgresql.org

Contributors: Marc G. Fournier scrappy at postgresql.org

To quote or not to quote, that is the question. [SQL] Point and function help 25-Dec-2003

plpgsql and sql functions are defined as a text string. This requires that all quotes within the function be escaped. This can add to the confusion of what to quote and when.

The sql function in question in this thread is as follows. It takes varchar arguments and uses them in a direct SQL statement. Can you see the quoting error?

	CREATE OR REPLACE FUNCTION map_point(varchar, varchar, varchar)
	RETURNS point AS
	'
	   SELECT map_loc 
	   FROM zip_code 
	   WHERE zip = \'$3\' AND
	      lower(state) = lower(\'$2\') AND
	      lower(city) = lower(\'$1\')
	' LANGUAGE 'sql' VOLATILE;

Over zealous quoting is the problem here. \'$3\' is read as '$3' a constant. Parameters to the function should not be quoted. The data type defined in the function definition ensures that the parameter is inserted into the text of the function correctly. This function is written correctly as:

	CREATE OR REPLACE FUNCTION map_point(varchar, varchar, varchar)
	RETURNS point AS
	'
	   SELECT map_loc 
	   FROM zip_code 
	   WHERE zip = $3 AND
	      lower(state) = lower($2) AND
	      lower(city) = lower($1)
	' LANGUAGE 'sql' VOLATILE;

It is always helpful to know what your own common mistakes are when dealing with coding issues. For example, I, myself am always off by one--whatever the context :-\ Also, I know I always forget to double my quotes in a function definition. Every time. You'd think I'd know by now :-) Knowing these things about yourself will help you debug smarter and quicker. You have a list of things to check first.

Contributors: Andy Lewis jumboc@comcast.net, Tom Lane tgl@sss.pgh.pa.us, elein at varlena.com

One true true [GENERAL] Constraint Problem 02-Nov-2003

In a table with foreign keys or associations you may want to designate one record as the "primary" record. For example, the table might be:

	CREATE TABLE emails (
	   person text,
	   email text,
	   primary boolean);

	elein   elein@varlena.com  t
	elein   who@where.org      f
	elein   me@here.org        f
	robin   rlmr@overthere.com t
	robin   checks@saranap.com f
	cheryl  ckms@home.net      t
	cheryl  cms@yonder.net     f

This can be implemented with a qualified UNIQUE INDEX. The index would be:

	CREATE UNIQUE INDEX primary_mail on emails(a) WHERE primary;

This issue was also discussed in Issue #33. That discussion, however, used a trigger to implement the uniqueness.

The trigger solution is better for the cases where you are updating the boolean field often--switching the primary email address often in this example. The index solution is better when you do not update the boolean field often, if ever.

To change the primary email for a record using the index above, you must use two statements. These are in a block transaction in order to prevent a window where there is no primary email for the person.

	BEGIN;
		UPDATE emails SET primary = 'f' where primary='t' and person='elein';
		UPDATE emails SET primary = 't' where email='who@where.org';
	COMMIT;

Contributors: Ron St-Pierre rstpierre at syscor.com, Tom Lane tgl at sss.pgh.pa.us, scott.marlowe scott.marlowe at ihs.com, Martin Marques martin at bugs.unl.edu.ar, Greg Stark gsstark at mit.edu, Csaba Nagy nagy at ecircle-ag.com, elein at varlena.com

When was this database created?
Creation date for Database	15-Dec-2003

People have inquired how to find the creation date of a database. The true answer is that you cannot unless you explicitly store that information when you create the database. However, you can find the approximate date that the database was created in the current PostgreSQL installation. Often this is good enough. Just note, that if the database was created on an earlier or different installation of PostgreSQL the date will be the date it was upgraded or moved to the current installation.

The creation date of the database is not stored in the system catalogs and so you cannot query the database for it. The way to find the creation date of a database is to look at the earliest modification date for a table file in the database.

To find the directory with the table files in it, you first need to know where your data directory is. It is usually in $PGDATA and it is usually read only by postgres or whoever installed your server. Then you will need to know the oid of the database. You can find this by selecting the oid from the pg_database table using psql.

The file in that directory that has the oldest modification time has the approximate date stamp of the database creation. The oldest files are usually system catalog files that are not changed over time, for example, pg_conversion. If all of your system catalog tables have been touched, however, the modification time will be less than adequate.

With the caveats discussed above, this shell script should return the approximate creation date of your database in your installation. It is set up with the assumption that postgres is the database super user and also must be run from the shell by a user with access to $PGDATA.

#!/bin/bash
#
#
dboid=`psql $1 -At -U postgres -c "select oid from pg_database where datname='$1';"`
cd $PGDATA/base/$dboid
ls -ltc | tail -1 | \
perl -n -e 'my @outline=split " "; print "$outline[5] $outline[6] $outline[7]\n";'