The deferred trigger queue pushing to disk patch pointed out

bmomjian · bmomjian · commit 1e5a16cc9ccf · 2003-07-27T03:13:17.000Z
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.

Stephan Szabo
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
@@ -1053,3 +1053,22 @@ INSERT INTO pktable VALUES (2000, 3); -- too late
 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:  insert or update on "fktable" violates foreign key constraint "$1"
+DETAIL:  Key (fk)=(200) is not present in "pktable".
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
@@ -674,3 +674,23 @@ INSERT INTO pktable VALUES (2000, 3); -- too late
 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;
+
