Of course you have saw this announcement already, right? You are subscribed to pgsql-announce aren't you? If not, go to PostgreSQL Mailing lists to sign up. The traffic is low and the information is very valuable.

The 7.3.2 release addresses several problems found with v7.3.1. This release addresses several overrun and memory leak issues that were found in recent weeks, so it is highly recommended that those running the v7.3.1 branch upgrade at their earliest convenience. This release is backwards compatibile to the previous v7.3.x releases, and does not require a dump/restore to upgrade.

This release also addresses a couple of issues previously addressed by General Bits. It will restore the creation of the OID column in CREATE TABLE AS / SELECT INTO statements. And it will increase default value of max_fsm_relations.

Contributors: Marc G. Fournier scrappy at hub.org elein at varlena.com

Casting for bigint Queries [GENERAL] 335 times faster (!) 03-Feb-2003

We have a report from Mikael Carneholm that a plain query on a bigint primary key took 6 seconds where a query on a functional index on another column took 19ms. The cause of this surprising speed was not, as speculated, that the functional index on text was faster than the primary key.

The cause of this was that the primary key query qualification value was not the same data type as the column. It was not quoted or cast into a bigint so the parser originally saw the number as an integer early and by default converted it to int4 (integer). Because the data type of the column was not an integer but rather a bigint, the index was not used.

When the value is quoted its conversion is delayed until more information is available and then cast to a bigint and so in this case the index is used. If the value is cast explicitly to bigint then the index is also used.

In general it is beneficial to try to match the data types of your queries by explicitly casting elements to their expected values. PostgreSQL does an excellent job of guessing the proper type most of the time, but there are some anomalies like this one with indexes that occasionally occur. This item is on the TODO list for 7.4, however, there is no promise on exactly when it will be corrected.

Contributors: Mikael Carneholm carniz at spray.se, Stephan Szabo sszabo at megazone23.bigpanda.com, Bruno Wolff III bruno at wolff.to, Nigel J. Andrews nandrews at investsystems.co.uk, Dennis Gearon gearond at cvc.net, Jan Wieck JanWieck at Yahoo.com, Patric Bechtel bechtel at ipcon.de, Neil Conway neilc at samurai.com

Keywords Reminder [GENERAL] user column name 6-Feb-2003

This is a brief reminder that you shouldn't use reserved keywords as identifier names if you can help it. If you must use them, then you also must enclose them in double quotes each time you refer to them. An identifier is the name of an object, a table name, a column name, etc.

The full list of keywords is in the documentation which notes that some keywords are absolutely forbidden and some are only forbidden in certain contexts. The documentation shows which are which, but in general it is best to avoid them all. It is also recommended that you avoid keywords in SQL 92 and SQL 99 specifications. PostgreSQL tries to adhere to that standard.

Contributors: Peter Choe choepete at mindspring.com, Stephan Szabo sszabo at megazone23.bigpanda.com, Dennis Gearon gearond at cvc.net, Robert Treat xzilla at users.sourceforge.net

Truncation, Delete and Contraints [GENERAL] Constraints/On Delete... 4-Jan-2003

This is a combination of two separate threads which have pretty much the same answer. Suppose you have a FOREIGN KEY constraint on table_two that references column fkey in table_one.

When you delete a row in table_one that is referenced by a row in table_two you will get the following error message.

 ERROR: $1 referential integrity violation
      - key in first still referenced from second

When you truncate table_one you will get this error:

	truncate table_one;
	ERROR:  TRUNCATE cannot be used as table table_two references
	        this one via foreign key constraint $1

In the truncate case, if the DELETE is prohibitively slow, which is the reason for using TRUNCATE in the first place, you may want to alter the other table(s) and drop the constraints before you truncate the first table. Then you will want to re-add the constraint.

You must also think at this time, what if a row in table_two refers to a row that will be deleted from table_one? What do you want to happen? If there are rows in table_two referencing missing rows in table_one you will get the Referential Integrity error message when adding back the constraint. You will have to remove the rows in table_two by hand, skip adding back the constraint or perhaps you should have used DELETE CASCADE in the first place. DELETE CASCADE can be invoked either as the SQL command or via the FOREIGN KEY constraint in. TRUNCATE is really not a good option for tables using referential integrity.

So we are back to the question of how to add the DELETE CASCADE to the FOREIGN KEY constraint on table_two. In the case in question, the tables were already created and so altering the constraints was required. First the original FOREIGN KEY constraint must be dropped and then it must be replaced with the new one. You can find the name of the original constraint using the \d command in psql. Unnamed constraints are named "$1", "$2", etc.

	ALTER TABLE table_two DROP CONSTRAINT "$1";
	ALTER TABLE table_two ADD CONSTRAINT "secondfk"
		FOREIGN KEY (fkey) REFERENCES table_one(one_key)

To set the default database for a user to something other that $USER, you must set the PGDATABASE variable in their environment. Environments for users can get tricky, especially when installations are in non-standard locations and ports use non-standard numbers and when there are multiple installations available. But there is a straight forward way of handling all this in the shell. In this example, we'll use bash.

The environment variables may be set in start up scripts for the shell. /etc/profile is executed for every user. ~/.bash_profile is executed for each user. Sometimes you may want to set things system wide, and in that case use /etc/profile. In user specific cases or to override system defaults for users, use ~/.bash_profile.

For a system wide setting to a production database, I would embed in /etc/profile the following lines. Note that PG_INST is not a PostgreSQL variable. It is my own to facilitate locating various installations.

	PG_INST=/local/pgsql73
	PGDATA=$PG_INST/data
	PGPORT=5433
	PGLIB=$PG_INST/lib
	PATH=$PG_INST/bin:$PATH
	PGHOST=cookie
	PGDATABASE=production
	export PG_INST PGDATA PGHOST PGPORT PATH PGLIB PGDATABASE

To create a user specific environment, one could embed the lines above in ~/.bash_profile, customizing the values as appropriate.

In my own development environment, I switch back and forth between installations. In this case I manually invoke a script I call pgenv with

	. pgenv

Localizing and standardizing your PG environment helps cut down the clutter involved in checking to see whether you have the environment when running in development environments. And it can prevent mishaps for users running in production environments.

Contributors: philip johnson philip.johnson at atempo.com, Martijn van Oosterhout kleptog at svana.org, Medi Montaseri medi.montaseri at intransa.com, elein at varlena.com

Calendar Wrangling [GENERAL] [SQL] Start and End Day of a Week 7-Feb-2003

Answering his own question, Luis Magaņa, supplies us with two nice functions to give us the day of month for the start and end days of each week in a given year.

   CREATE OR REPLACE FUNCTION weekstart(int4,int4)
   RETURNS TIMESTAMP
   AS'
   DECLARE
      year ALIAS FOR $1;
      week ALIAS FOR $2;
      startsin TIMESTAMP;
   BEGIN

      SELECT INTO startsin 
         (SELECT  CAST(year || ''-01-01'' AS TIMESTAMP) - 
         (date_part(''dow'',CAST(year || ''-01-01'' AS TIMESTAMP))||'' days'')::INTERVAL) + 
         ((week*7)||'' days'')::INTERVAL - ''7 days''::INTERVAL;
      RETURN startsin;

   END;
   ' LANGUAGE 'plpgsql';

   CREATE OR REPLACE FUNCTION weekend(int4,int4)
   RETURNS TIMESTAMP
   AS'
   DECLARE
      year ALIAS FOR $1;
      week ALIAS FOR $2;
      endsin TIMESTAMP;
   BEGIN

      SELECT INTO endsin
         (SELECT  CAST(year || ''-01-01'' AS TIMESTAMP) - 
         (date_part(''dow'',CAST(year || ''-01-01'' AS TIMESTAMP))||'' days'')::INTERVAL) + 
         ((week*7)||'' days'')::INTERVAL - ''1 days''::INTERVAL;

      RETURN endsin;
   
   END;
   ' LANGUAGE 'plpgsql';

Contributors: Luis Magaņa joe666 at gnovus.com