diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 59 | ||||
| -rw-r--r-- | src/test/regress/sql/foreign_key.sql | 58 |
2 files changed, 117 insertions, 0 deletions
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index bfb90979f68..89dbfcb22a8 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -1029,3 +1029,62 @@ INSERT INTO fktable VALUES (100, 200); COMMIT; ERROR: insert or update on table "fktable" violates foreign key constraint "$1" DETAIL: Key (fk)=(200) is not present in table "pktable". +-- Check that rewrite rules splitting one INSERT into multiple +-- conditional statements does not disable FK checking. +create table rule_and_refint_t1 ( + id1a integer, + id1b integer, + + primary key (id1a, id1b) +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t1_pkey" for table "rule_and_refint_t1" +create table rule_and_refint_t2 ( + id2a integer, + id2c integer, + + primary key (id2a, id2c) +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t2_pkey" for table "rule_and_refint_t2" +create table rule_and_refint_t3 ( + id3a integer, + id3b integer, + id3c integer, + data text, + primary key (id3a, id3b, id3c), + foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b), + foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c) +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t3_pkey" for table "rule_and_refint_t3" +insert into rule_and_refint_t1 values (1, 11); +insert into rule_and_refint_t1 values (1, 12); +insert into rule_and_refint_t1 values (2, 21); +insert into rule_and_refint_t1 values (2, 22); +insert into rule_and_refint_t2 values (1, 11); +insert into rule_and_refint_t2 values (1, 12); +insert into rule_and_refint_t2 values (2, 21); +insert into rule_and_refint_t2 values (2, 22); +insert into rule_and_refint_t3 values (1, 11, 11, 'row1'); +insert into rule_and_refint_t3 values (1, 11, 12, 'row2'); +insert into rule_and_refint_t3 values (1, 12, 11, 'row3'); +insert into rule_and_refint_t3 values (1, 12, 12, 'row4'); +insert into rule_and_refint_t3 values (1, 11, 13, 'row5'); +ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$2" +DETAIL: Key (id3a,id3c)=(1,13) is not present in table "rule_and_refint_t2". +insert into rule_and_refint_t3 values (1, 13, 11, 'row6'); +ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$1" +DETAIL: Key (id3a,id3b)=(1,13) is not present in table "rule_and_refint_t1". +create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3 + where (exists (select 1 from rule_and_refint_t3 + where (((rule_and_refint_t3.id3a = new.id3a) + and (rule_and_refint_t3.id3b = new.id3b)) + and (rule_and_refint_t3.id3c = new.id3c)))) + do instead update rule_and_refint_t3 set data = new.data + where (((rule_and_refint_t3.id3a = new.id3a) + and (rule_and_refint_t3.id3b = new.id3b)) + and (rule_and_refint_t3.id3c = new.id3c)); +insert into rule_and_refint_t3 values (1, 11, 13, 'row7'); +ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$2" +DETAIL: Key (id3a,id3c)=(1,13) is not present in table "rule_and_refint_t2". +insert into rule_and_refint_t3 values (1, 13, 11, 'row8'); +ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$1" +DETAIL: Key (id3a,id3b)=(1,13) is not present in table "rule_and_refint_t1". diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index 609be4d98e7..0d54b77f303 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -694,3 +694,61 @@ INSERT INTO fktable VALUES (100, 200); -- error here on commit COMMIT; +-- Check that rewrite rules splitting one INSERT into multiple +-- conditional statements does not disable FK checking. +create table rule_and_refint_t1 ( + id1a integer, + id1b integer, + + primary key (id1a, id1b) +); + +create table rule_and_refint_t2 ( + id2a integer, + id2c integer, + + primary key (id2a, id2c) +); + +create table rule_and_refint_t3 ( + id3a integer, + id3b integer, + id3c integer, + data text, + + primary key (id3a, id3b, id3c), + + foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b), + foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c) +); + + +insert into rule_and_refint_t1 values (1, 11); +insert into rule_and_refint_t1 values (1, 12); +insert into rule_and_refint_t1 values (2, 21); +insert into rule_and_refint_t1 values (2, 22); + +insert into rule_and_refint_t2 values (1, 11); +insert into rule_and_refint_t2 values (1, 12); +insert into rule_and_refint_t2 values (2, 21); +insert into rule_and_refint_t2 values (2, 22); + +insert into rule_and_refint_t3 values (1, 11, 11, 'row1'); +insert into rule_and_refint_t3 values (1, 11, 12, 'row2'); +insert into rule_and_refint_t3 values (1, 12, 11, 'row3'); +insert into rule_and_refint_t3 values (1, 12, 12, 'row4'); +insert into rule_and_refint_t3 values (1, 11, 13, 'row5'); +insert into rule_and_refint_t3 values (1, 13, 11, 'row6'); + +create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3 + where (exists (select 1 from rule_and_refint_t3 + where (((rule_and_refint_t3.id3a = new.id3a) + and (rule_and_refint_t3.id3b = new.id3b)) + and (rule_and_refint_t3.id3c = new.id3c)))) + do instead update rule_and_refint_t3 set data = new.data + where (((rule_and_refint_t3.id3a = new.id3a) + and (rule_and_refint_t3.id3b = new.id3b)) + and (rule_and_refint_t3.id3c = new.id3c)); + +insert into rule_and_refint_t3 values (1, 11, 13, 'row7'); +insert into rule_and_refint_t3 values (1, 13, 11, 'row8'); |