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

Reply via email to