Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitcc4baf4

Browse files
Jan WieckJan Wieck
Jan Wieck
authored and
Jan Wieck
committed
Fix for possible referential integrity violation when a qualified ON INSERT
rule split the query into one INSERT and one UPDATE where the UPDATEthen hit's the just created row without modifying the key fields again.In this special case, the new key slipped in totally unchecked.Jan
1 parente181001 commitcc4baf4

File tree

3 files changed

+121
-2
lines changed

3 files changed

+121
-2
lines changed

‎src/backend/utils/adt/ri_triggers.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,7 @@
1717
*
1818
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
1919
*
20-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.62 2003/10/06 16:38:28 tgl Exp $
20+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.63 2003/10/31 03:58:20 wieck Exp $
2121
*
2222
* ----------
2323
*/
@@ -379,7 +379,9 @@ RI_FKey_check(PG_FUNCTION_ARGS)
379379
*/
380380
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
381381
{
382-
if (ri_KeysEqual(fk_rel,old_row,new_row,&qkey,
382+
if (HeapTupleHeaderGetXmin(old_row->t_data)!=
383+
GetCurrentTransactionId()&&
384+
ri_KeysEqual(fk_rel,old_row,new_row,&qkey,
383385
RI_KEYPAIR_FK_IDX))
384386
{
385387
heap_close(pk_rel,RowShareLock);

‎src/test/regress/expected/foreign_key.out

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1029,3 +1029,62 @@ INSERT INTO fktable VALUES (100, 200);
10291029
COMMIT;
10301030
ERROR: insert or update on table "fktable" violates foreign key constraint "$1"
10311031
DETAIL: Key (fk)=(200) is not present in table "pktable".
1032+
-- Check that rewrite rules splitting one INSERT into multiple
1033+
-- conditional statements does not disable FK checking.
1034+
create table rule_and_refint_t1 (
1035+
id1a integer,
1036+
id1b integer,
1037+
1038+
primary key (id1a, id1b)
1039+
);
1040+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t1_pkey" for table "rule_and_refint_t1"
1041+
create table rule_and_refint_t2 (
1042+
id2a integer,
1043+
id2c integer,
1044+
1045+
primary key (id2a, id2c)
1046+
);
1047+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t2_pkey" for table "rule_and_refint_t2"
1048+
create table rule_and_refint_t3 (
1049+
id3a integer,
1050+
id3b integer,
1051+
id3c integer,
1052+
data text,
1053+
primary key (id3a, id3b, id3c),
1054+
foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b),
1055+
foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c)
1056+
);
1057+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rule_and_refint_t3_pkey" for table "rule_and_refint_t3"
1058+
insert into rule_and_refint_t1 values (1, 11);
1059+
insert into rule_and_refint_t1 values (1, 12);
1060+
insert into rule_and_refint_t1 values (2, 21);
1061+
insert into rule_and_refint_t1 values (2, 22);
1062+
insert into rule_and_refint_t2 values (1, 11);
1063+
insert into rule_and_refint_t2 values (1, 12);
1064+
insert into rule_and_refint_t2 values (2, 21);
1065+
insert into rule_and_refint_t2 values (2, 22);
1066+
insert into rule_and_refint_t3 values (1, 11, 11, 'row1');
1067+
insert into rule_and_refint_t3 values (1, 11, 12, 'row2');
1068+
insert into rule_and_refint_t3 values (1, 12, 11, 'row3');
1069+
insert into rule_and_refint_t3 values (1, 12, 12, 'row4');
1070+
insert into rule_and_refint_t3 values (1, 11, 13, 'row5');
1071+
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$2"
1072+
DETAIL: Key (id3a,id3c)=(1,13) is not present in table "rule_and_refint_t2".
1073+
insert into rule_and_refint_t3 values (1, 13, 11, 'row6');
1074+
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$1"
1075+
DETAIL: Key (id3a,id3b)=(1,13) is not present in table "rule_and_refint_t1".
1076+
create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3
1077+
where (exists (select 1 from rule_and_refint_t3
1078+
where (((rule_and_refint_t3.id3a = new.id3a)
1079+
and (rule_and_refint_t3.id3b = new.id3b))
1080+
and (rule_and_refint_t3.id3c = new.id3c))))
1081+
do instead update rule_and_refint_t3 set data = new.data
1082+
where (((rule_and_refint_t3.id3a = new.id3a)
1083+
and (rule_and_refint_t3.id3b = new.id3b))
1084+
and (rule_and_refint_t3.id3c = new.id3c));
1085+
insert into rule_and_refint_t3 values (1, 11, 13, 'row7');
1086+
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$2"
1087+
DETAIL: Key (id3a,id3c)=(1,13) is not present in table "rule_and_refint_t2".
1088+
insert into rule_and_refint_t3 values (1, 13, 11, 'row8');
1089+
ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "$1"
1090+
DETAIL: Key (id3a,id3b)=(1,13) is not present in table "rule_and_refint_t1".

‎src/test/regress/sql/foreign_key.sql

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -694,3 +694,61 @@ INSERT INTO fktable VALUES (100, 200);
694694
-- error here on commit
695695
COMMIT;
696696

697+
-- Check that rewrite rules splitting one INSERT into multiple
698+
-- conditional statements does not disable FK checking.
699+
createtablerule_and_refint_t1 (
700+
id1ainteger,
701+
id1binteger,
702+
703+
primary key (id1a, id1b)
704+
);
705+
706+
createtablerule_and_refint_t2 (
707+
id2ainteger,
708+
id2cinteger,
709+
710+
primary key (id2a, id2c)
711+
);
712+
713+
createtablerule_and_refint_t3 (
714+
id3ainteger,
715+
id3binteger,
716+
id3cinteger,
717+
datatext,
718+
719+
primary key (id3a, id3b, id3c),
720+
721+
foreign key (id3a, id3b)references rule_and_refint_t1 (id1a, id1b),
722+
foreign key (id3a, id3c)references rule_and_refint_t2 (id2a, id2c)
723+
);
724+
725+
726+
insert into rule_and_refint_t1values (1,11);
727+
insert into rule_and_refint_t1values (1,12);
728+
insert into rule_and_refint_t1values (2,21);
729+
insert into rule_and_refint_t1values (2,22);
730+
731+
insert into rule_and_refint_t2values (1,11);
732+
insert into rule_and_refint_t2values (1,12);
733+
insert into rule_and_refint_t2values (2,21);
734+
insert into rule_and_refint_t2values (2,22);
735+
736+
insert into rule_and_refint_t3values (1,11,11,'row1');
737+
insert into rule_and_refint_t3values (1,11,12,'row2');
738+
insert into rule_and_refint_t3values (1,12,11,'row3');
739+
insert into rule_and_refint_t3values (1,12,12,'row4');
740+
insert into rule_and_refint_t3values (1,11,13,'row5');
741+
insert into rule_and_refint_t3values (1,13,11,'row6');
742+
743+
createrulerule_and_refint_t3_insason insert to rule_and_refint_t3
744+
where (exists (select1from rule_and_refint_t3
745+
where (((rule_and_refint_t3.id3a=new.id3a)
746+
and (rule_and_refint_t3.id3b=new.id3b))
747+
and (rule_and_refint_t3.id3c=new.id3c))))
748+
do insteadupdate rule_and_refint_t3set data=new.data
749+
where (((rule_and_refint_t3.id3a=new.id3a)
750+
and (rule_and_refint_t3.id3b=new.id3b))
751+
and (rule_and_refint_t3.id3c=new.id3c));
752+
753+
insert into rule_and_refint_t3values (1,11,13,'row7');
754+
insert into rule_and_refint_t3values (1,13,11,'row8');

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp