diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 56 | ||||
| -rw-r--r-- | src/test/regress/sql/foreign_key.sql | 59 |
2 files changed, 115 insertions, 0 deletions
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 3c2ede0aaa3..c20db7dbcab 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -1022,3 +1022,59 @@ 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; +-- 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' +NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) +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: $2 referential integrity violation - key referenced from rule_and_refint_t3 not found in rule_and_refint_t2 +insert into rule_and_refint_t3 values (1, 13, 11, 'row6'); +ERROR: $1 referential integrity violation - key referenced from rule_and_refint_t3 not found in 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: $2 referential integrity violation - key referenced from rule_and_refint_t3 not found in rule_and_refint_t2 +insert into rule_and_refint_t3 values (1, 13, 11, 'row8'); +ERROR: $1 referential integrity violation - key referenced from rule_and_refint_t3 not found in rule_and_refint_t1 diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index f314b5f1993..351136a1361 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -674,3 +674,62 @@ INSERT INTO pktable VALUES (2000, 3); -- too late COMMIT; DROP TABLE fktable, 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) +); + +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'); |