I have made a clearer example of the bug I reported to -hackers yesterday: http://archives.postgresql.org/pgsql-hackers/2006-10/msg01252.php
The following example shows a simple case that fails on 8.0+ (including CVS HEAD), but works fine on 7.4. There are two almost identical situations, and one causes an ERROR and the other a PANIC. The only difference is the column type: INT versus TEXT, respectively. I am on FreeBSD. An OOM condition must be caused to see this bug. In 7.4, an OOM condition is not even caused for the query, so perhaps it has a the same bug, but handles foreign keys differently. Incidently, foreign keys are all AFTER triggers, even in 7.4, but I don't understand why 7.4 doesn't exhaust itself of memory collecting the trigger events, as is described in the following mailing list post: http://archives.postgresql.org/pgsql-bugs/2006-05/msg00036.php Also, and this is pure conjecture, this bug may be related to the following change in the 8.0 release notes: "Nondeferred AFTER triggers are now fired immediately after completion of the triggering query, rather than upon finishing the current interactive command. This makes a difference when the triggering query occurred within a function: the trigger is invoked before the function proceeds to its next operation. For example, if a function inserts a new row into a table, any nondeferred foreign key checks occur before proceeding with the function." Regards, Jeff Davis Step 1: Create 4 tables ----------------------------------------- CREATE TABLE r1( i INT PRIMARY KEY ); INSERT INTO r1 VALUES(1); CREATE TABLE r2( i INT PRIMARY KEY ); INSERT INTO r2 VALUES(1); CREATE TABLE r3( i INT PRIMARY KEY ); INSERT INTO r3 VALUES(1); CREATE TABLE r4( i INT PRIMARY KEY ); INSERT INTO r4 VALUES(1); Step 2: Cause an out of memory condition. The result is an ERROR, as expected. ----------------------------------------- BEGIN; CREATE TABLE crashme ( attr1 INT REFERENCES r1(i), attr2 INT REFERENCES r2(i), attr3 INT REFERENCES r3(i), attr4 INT REFERENCES r4(i), attr5 TEXT ); INSERT INTO crashme(attr1,attr2,attr3,attr4,attr5) SELECT 1,1,1,1,'t' FROM generate_series(1,5000000); Step 3: Do almost exacly the same thing, except attr5 is INT and not TEXT type. This causes a PANIC instead of an ERROR. The bug is that this should be only an ERROR, since everything is the same except the column type for attr5. --------------------------------------------------- BEGIN; CREATE TABLE crashme ( attr1 INT REFERENCES r1(i), attr2 INT REFERENCES r2(i), attr3 INT REFERENCES r3(i), attr4 INT REFERENCES r4(i), attr5 INT ); INSERT INTO crashme(attr1,attr2,attr3,attr4,attr5) SELECT 1,1,1,1,1 FROM generate_series(1,5000000); ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster