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

29-Dec-2003 Issue: 55

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 www.PostgreSQL.org.

The latest greatest
[ANNOUNCE] PostgreSQL 7.4.1 Released 22-Dec-2003

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)
This version, as with most minor versions, does not require a dump/reload to put into place. That said, there are some changes to the information schema that require a DROP/reload of that *specific* schema ... please read the HISTORY file for instruction on how to do this. As always, this release is available on all our mirrors:
http://www.postgresql.org/mirrors-www.html
And, thanks to David Fetter, is also available via BitTorrent at:
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);
where one person may have many email addresses. But you might want to designate one and only one as primary. That is, only one row per person should be TRUE.
	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
Each person has many addresses but only one address is the primary address.

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

	CREATE UNIQUE INDEX primary_mail on emails(a) WHERE primary;
This will cause a "Cannot insert duplicate key into unique index" message and prevent setting multiple primary emails.

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";'
Contributors: Juliano da Silva Ignacio jsignacio at hotmail.com, elein at varlena.com

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

Top
Google
Search General Bits & varlena.com Search WWW