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

Commit2c535bf

Browse files
committed
Fix incorrect optimization of foreign-key checks. When an UPDATE on the
referencing table does not change the tuple's FK column(s), we don't botherto check the PK table since the constraint was presumably already valid.However, the check is still necessary if the tuple was inserted by our owntransaction, since in that case the INSERT trigger will conclude it need notmake the check (since its version of the tuple has been deleted). We got thisright for simple cases, but not when the insert and update are in differentsubtransactions of the current top-level transaction; in such cases the FKcheck would never be made at all. (Hence, problem dates back to 8.0 whensubtransactions were added --- it's actually the subtransaction version of abug fixed in 7.3.5.) Fix, and add regression test cases. Report and fix byAffan Salman.
1 parenta5ca334 commit2c535bf

File tree

3 files changed

+88
-3
lines changed

3 files changed

+88
-3
lines changed

‎src/backend/commands/trigger.c

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.215 2007/07/01 17:45:42 tgl Exp $
10+
* $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.216 2007/07/17 17:45:28 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -3396,8 +3396,7 @@ AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, bool row_trigger,
33963396
* anything, so we have to do the check for the UPDATE
33973397
* anyway.
33983398
*/
3399-
if (HeapTupleHeaderGetXmin(oldtup->t_data)!=
3400-
GetCurrentTransactionId()&&
3399+
if (!TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetXmin(oldtup->t_data))&&
34013400
RI_FKey_keyequal_upd_fk(trigger,rel,oldtup,newtup))
34023401
{
34033402
continue;

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

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1193,3 +1193,40 @@ UPDATE fktable SET id = id + 1;
11931193
COMMIT;
11941194
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
11951195
DETAIL: Key (fk)=(20) is not present in table "pktable".
1196+
-- check same case when insert is in a different subtransaction than update
1197+
BEGIN;
1198+
-- doesn't match PK, but no error yet
1199+
INSERT INTO fktable VALUES (0, 20);
1200+
-- UPDATE will be in a subxact
1201+
SAVEPOINT savept1;
1202+
-- don't change FK
1203+
UPDATE fktable SET id = id + 1;
1204+
-- should catch error from initial INSERT
1205+
COMMIT;
1206+
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1207+
DETAIL: Key (fk)=(20) is not present in table "pktable".
1208+
BEGIN;
1209+
-- INSERT will be in a subxact
1210+
SAVEPOINT savept1;
1211+
-- doesn't match PK, but no error yet
1212+
INSERT INTO fktable VALUES (0, 20);
1213+
RELEASE SAVEPOINT savept1;
1214+
-- don't change FK
1215+
UPDATE fktable SET id = id + 1;
1216+
-- should catch error from initial INSERT
1217+
COMMIT;
1218+
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1219+
DETAIL: Key (fk)=(20) is not present in table "pktable".
1220+
BEGIN;
1221+
-- doesn't match PK, but no error yet
1222+
INSERT INTO fktable VALUES (0, 20);
1223+
-- UPDATE will be in a subxact
1224+
SAVEPOINT savept1;
1225+
-- don't change FK
1226+
UPDATE fktable SET id = id + 1;
1227+
-- Roll back the UPDATE
1228+
ROLLBACK TO savept1;
1229+
-- should catch error from initial INSERT
1230+
COMMIT;
1231+
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1232+
DETAIL: Key (fk)=(20) is not present in table "pktable".

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

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -830,3 +830,52 @@ UPDATE fktable SET id = id + 1;
830830

831831
-- should catch error from initial INSERT
832832
COMMIT;
833+
834+
-- check same case when insert is in a different subtransaction than update
835+
836+
BEGIN;
837+
838+
-- doesn't match PK, but no error yet
839+
INSERT INTO fktableVALUES (0,20);
840+
841+
-- UPDATE will be in a subxact
842+
SAVEPOINT savept1;
843+
844+
-- don't change FK
845+
UPDATE fktableSET id= id+1;
846+
847+
-- should catch error from initial INSERT
848+
COMMIT;
849+
850+
BEGIN;
851+
852+
-- INSERT will be in a subxact
853+
SAVEPOINT savept1;
854+
855+
-- doesn't match PK, but no error yet
856+
INSERT INTO fktableVALUES (0,20);
857+
858+
RELEASE SAVEPOINT savept1;
859+
860+
-- don't change FK
861+
UPDATE fktableSET id= id+1;
862+
863+
-- should catch error from initial INSERT
864+
COMMIT;
865+
866+
BEGIN;
867+
868+
-- doesn't match PK, but no error yet
869+
INSERT INTO fktableVALUES (0,20);
870+
871+
-- UPDATE will be in a subxact
872+
SAVEPOINT savept1;
873+
874+
-- don't change FK
875+
UPDATE fktableSET id= id+1;
876+
877+
-- Roll back the UPDATE
878+
ROLLBACK TO savept1;
879+
880+
-- should catch error from initial INSERT
881+
COMMIT;

0 commit comments

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