A bad trigger was created to update an id with the next value of a sequence.

	CREATE OR REPLACE FUNCTION "f_products"() RETURNS "opaque" AS '
	BEGIN
   	   NEW.ID = NEXTVAL(''products_id_seq'');
   	   RETURN NEW;
	END' LANGUAGE 'plpgsql'

	CREATE TRIGGER "t_classes" AFTER INSERT ON "products" 
	FOR EACH ROW EXECUTE PROCEDURE f_products();

This does not work because to change a value in the row, you must use a BEFORE trigger rather than an AFTER trigger.

It was also pointed out that it is much easier to use a SERIAL data type or to have a DEFAULT set to the value of nextval() to achieve the same effect. You can find more informatino in the documentation.

Contributors: Roberto de Amorim roberto at smartbit.inf.br, Einar Karttunen ekarttun at cs.helsinki.fi, Stephan Szabo sszabo at megazone23.bigpanda.com

Index Maintenance? [GENERAL] index question 13-Nov-2002

Do indexes need maintenance to stay current?

Indexes maintain themselves as you insert, update and delete from your table--mostly.

There is a known issue where some deleted index space could be recovered but isn't. This is the trade-off between being able to run vacuum while the database is live, or having to lock table access in order to vacuum.

The exact problem is that empty index pages are not collapsed. This means that index space doesn't shrink when the table and therefore the number of indexes shrink. When the range of an index constantly grows, the indexes will also constantly grow even if you prune the low end.

Re-indexing will re-construct the index from scratch, freeing any lingering unused index pages. This can be done occasionally, depending on the shrinkage of the table and the narrowing of index values.

Contributors: Williams, Travis L, NPONS tlw at att.com, Shridhar Daithankar shridhar_daithankar at persistent.co.in, scott.marlowe scott.marlowe at ihs.com, Stephan Szabo sszabo at megazone23.bigpanda.com, Tom Lane tgl at sss.pgh.pa.us

Table Inheritance Cascading Delete kills off the kids [GENERAL] Inheritance: delete parent deletes children 2-Oct-2002

There a base table, "dad" and sub tables "kid1", "kid2", etc. who are created with INHERITS( dad ).

	create table dad (...);
	create table kid1 (...) inherits (dad);
	create table kid2 (...) inherits (dad);

	delete from dad;

To delete from the base table and not the sub tables, use:

	delete from only dad;

	delete from kid1 where ....;

The postgres.conf value SQL_INHERITANCE changes the default behavior so that it conforms to the behavior prior to 7.1. Prior to 7.1 commands did not necessarily extend to the sub tables. However, it is recommended that you use ONLY in your queries instead of relying on old semantics.

Contributors: M. I. google.clp at alma.ch, Stephan Szabo sszabo at megazone23.bigpanda.com

Using Sequence Functions: how to and how not to [GENERAL] [Q] Sequences, last_value and inserts 01-Nov-2002

Sequence function currval('sequence_name') will fetch the value of a sequence as selected by the previous nextval('sequence_name') issued on the the current connection.

This is true whether you are using Perl/DBI or any other interface. And it is true if other connections are also issuing calls to nextval() and currval() for the same sequence. As long as you maintain the current connection, you can get the last value you requested with nextval('sequence_name');

It was suggested that:

	select last_value from sequence_name; 

Contributors: Roderick A. Anderson raanders at acm.org, Bruno Wolff III bruno at wolff.to, Marie G. Tuite marie.tuite at edisonaffiliates.com, Gregory Wood gregw at com-stock.com, Tom Lane tgl at sss.pgh.pa.us

Negative Queries [GENERAL] Proper Join and check 06-Oct-2002

There is a one to many relationship between "tableone" and "tablemany".

	CREATE TABLE "tableone" (
		"sym" varchar(5) NOT NULL,
		PRIMARY KEY ("sym") );
 
	CREATE TABLE "tablemany" (
		"id"  int4       NOT NULL DEFAULT nextval('res_id_seq'::text),
		"sym" varchar(5) REFERENCES tableone ON UPDATE CASCADE,
		PRIMARY KEY ("id") );

The problem is to find the records in tableone that don't have any corresponding records in tablemany.
This query will do the task:

	select a.sym from tableone a
	where a.sym not in (select b.sym from tablemany b
			    where b.sym = a.sym);

	create index tablemany_sym on tablemany(sym);

This query was meant to find bad data. However, you could prevent the problem by using triggers so that the tables could be updated together or put the initial inserts into one transaction that would be committed or rolled back together.

Contributors: Billy G. Allie bga at mug.org, Patrick Nelson pnelson at neatech.com

Setting DEFAULT column values [GENERAL] how do i insert a default value? 12-Nov-2002

In 7.3, you can now use the keyword DEFAULT in an insert statement to indicate that you want the value to be its default. This is usually used when you are not using column names in you INSERT statement.

	insert into table bubba values
		('truck','washer',DEFAULT,'gun rack',DEFAULT,36,24,36);

In 7.2 and earlier, you could only omit columns from the far right of the table. Otherwise you needed to use a column name list.

	insert into table bubba values
		(vehicle, cargo,  equip,  age, size, depth)
		('truck','washer','gun rack',36,24,36);

Contributors: Andy Kriger akriger at greaterthanone.com, scott.marlowe scott.marlowe at ihs.com, Tom Lane tgl at sss.pgh.pa.us, elein at varlena.com