diff options
| -rw-r--r-- | src/backend/utils/adt/ri_triggers.c | 6 | ||||
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 59 | ||||
| -rw-r--r-- | src/test/regress/sql/foreign_key.sql | 58 |
3 files changed, 121 insertions, 2 deletions
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index 306dda0aa4e..d0e3e9c0817 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -17,7 +17,7 @@ * * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * - * $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.62 2003/10/06 16:38:28 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.63 2003/10/31 03:58:20 wieck Exp $ * * ---------- */ @@ -379,7 +379,9 @@ RI_FKey_check(PG_FUNCTION_ARGS) */ if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) { - if (ri_KeysEqual(fk_rel, old_row, new_row, &qkey, + if (HeapTupleHeaderGetXmin(old_row->t_data) != + GetCurrentTransactionId() && + ri_KeysEqual(fk_rel, old_row, new_row, &qkey, RI_KEYPAIR_FK_IDX)) { heap_close(pk_rel, RowShareLock); 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'); |