postgres.git / summary / log / commit / refs

commit    1e5a16cc9ccf030df8a1659ff64639426f2bc3aa
Author:   Bruce Momjian <bruce@momjian.us>
Date:     Sun Jul 27 03:13:17 2003 +0000
First released in: 7.4

    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.
    
    Stephan Szabo


 src/test/regress/expected/foreign_key.out | 19 +++++++++++++++++++
 src/test/regress/sql/foreign_key.sql      | 20 ++++++++++++++++++++
 2 files changed, 39 insertions(+)

diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index cb6fc35eb6d..cde52f46e00 100644
--- 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
index f314b5f1993..609be4d98e7 100644
--- 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;
+


[parent: 1be17f1f2611]