The deferred trigger queue pushing to disk patch pointed out that the regression tests for foreign keys didn't seem to test a deferred constraint that was not satisified by a later statement and was not made immediate by set constraints, so here's a simple added test with a single invalid insert and a commit.
Index: src/test/regress/sql/foreign_key.sql =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/foreign_key.sql,v retrieving revision 1.8 diff -c -r1.8 foreign_key.sql *** src/test/regress/sql/foreign_key.sql 17 Aug 2002 12:15:49 -0000 1.8 --- src/test/regress/sql/foreign_key.sql 4 Jul 2003 22:33:19 -0000 *************** *** 674,676 **** --- 674,696 ---- COMMIT; DROP TABLE fktable, pktable; + + -- deferrable, initially deferred + CREATE TABLE pktable ( + id INT4 PRIMARY KEY, + other INT4 + ); + + CREATE TABLE fktable ( + id INT4 PRIMARY KEY, + fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED + ); + + BEGIN; + + -- no error here + INSERT INTO fktable VALUES (100, 200); + + -- error here on commit + COMMIT; + Index: src/test/regress/expected/foreign_key.out =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/foreign_key.out,v retrieving revision 1.21 diff -c -r1.21 foreign_key.out *** src/test/regress/expected/foreign_key.out 7 Apr 2003 20:30:38 -0000 1.21 --- src/test/regress/expected/foreign_key.out 4 Jul 2003 22:33:32 -0000 *************** *** 1022,1024 **** --- 1022,1042 ---- ERROR: current transaction is aborted, queries ignored until end of transaction block COMMIT; DROP TABLE fktable, pktable; + -- deferrable, initially deferred + CREATE TABLE pktable ( + id INT4 PRIMARY KEY, + other INT4 + ); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' + CREATE TABLE fktable ( + id INT4 PRIMARY KEY, + fk INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED + ); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'fktable_pkey' for table 'fktable' + NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + BEGIN; + -- no error here + INSERT INTO fktable VALUES (100, 200); + -- error here on commit + COMMIT; + ERROR: $1 referential integrity violation - key (fk)=(200) referenced from fktable not found in pktable
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
