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

Commit1f897ae

Browse files
committed
Change FK trigger creation order to better support self-referential FKs.
When a foreign-key constraint references another column of the same table,row updates will queue both the PK's ON UPDATE action and the FK's CHECKaction in the same event. The ON UPDATE action must execute first, elsethe CHECK will check a non-final state of the row and possibly throw aninappropriate error, as seen in bug #6268 from Roman Lytovchenko.Now, the firing order of multiple triggers for the same event is determinedby the sort order of their pg_trigger.tgnames, and the auto-generated nameswe use for FK triggers are "RI_ConstraintTrigger_NNNN" where NNNN is thetrigger OID. So most of the time the firing order is the same as creationorder, and so rearranging the creation order fixes it.This patch will fail to fix the problem if the OID counter wraps around oradds a decimal digit (eg, from 99999 to 100000) while we are creating thetriggers for an FK constraint. Given the small odds of that, and the lowusage of self-referential FKs, we'll live with that solution in the backbranches. A better fix is to change the auto-generated names for FKtriggers, but it seems unwise to do that in stable branches because theremay be client code that depends on the naming convention. We'll fix itthat way in HEAD in a separate patch.Back-patch to all supported branches, since this bug has existed for a longtime.
1 parent9a743ff commit1f897ae

File tree

3 files changed

+75
-7
lines changed

3 files changed

+75
-7
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 21 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -4735,13 +4735,6 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
47354735
/* Make changes-so-far visible */
47364736
CommandCounterIncrement();
47374737

4738-
/*
4739-
* Build and execute a CREATE CONSTRAINT TRIGGER statement for the CHECK
4740-
* action for both INSERTs and UPDATEs on the referencing table.
4741-
*/
4742-
CreateFKCheckTrigger(myRel,fkconstraint,&constrobj,&trigobj, true);
4743-
CreateFKCheckTrigger(myRel,fkconstraint,&constrobj,&trigobj, false);
4744-
47454738
/*
47464739
* Build and execute a CREATE CONSTRAINT TRIGGER statement for the ON
47474740
* DELETE action on the referenced table.
@@ -4879,6 +4872,27 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
48794872

48804873
/* Register dependency from trigger to constraint */
48814874
recordDependencyOn(&trigobj,&constrobj,DEPENDENCY_INTERNAL);
4875+
4876+
/* Make changes-so-far visible */
4877+
CommandCounterIncrement();
4878+
4879+
/*
4880+
* Build and execute CREATE CONSTRAINT TRIGGER statements for the CHECK
4881+
* action for both INSERTs and UPDATEs on the referencing table.
4882+
*
4883+
* Note: for a self-referential FK (referencing and referenced tables are
4884+
* the same), it is important that the ON UPDATE action fires before the
4885+
* CHECK action, since both triggers will fire on the same row during an
4886+
* UPDATE event; otherwise the CHECK trigger will be checking a non-final
4887+
* state of the row. Because triggers fire in name order, we are
4888+
* effectively relying on the OIDs of the triggers to sort correctly as
4889+
* text. This will work except when the OID counter wraps around or adds
4890+
* a digit, eg "99999" sorts after "100000". That is infrequent enough,
4891+
* and the use of self-referential FKs is rare enough, that we live with
4892+
* it for now. There will be a real fix in PG 9.2.
4893+
*/
4894+
CreateFKCheckTrigger(myRel,fkconstraint,&constrobj,&trigobj, true);
4895+
CreateFKCheckTrigger(myRel,fkconstraint,&constrobj,&trigobj, false);
48824896
}
48834897

48844898
/*

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

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1276,3 +1276,35 @@ SELECT * FROM tasks;
12761276
(3 rows)
12771277

12781278
COMMIT;
1279+
--
1280+
-- Test self-referential FK with CASCADE (bug #6268)
1281+
--
1282+
create temp table selfref (
1283+
a int primary key,
1284+
b int,
1285+
foreign key (b) references selfref (a)
1286+
on update cascade on delete cascade
1287+
);
1288+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "selfref_pkey" for table "selfref"
1289+
insert into selfref (a, b)
1290+
values
1291+
(0, 0),
1292+
(1, 1);
1293+
begin;
1294+
update selfref set a = 123 where a = 0;
1295+
select a, b from selfref;
1296+
a | b
1297+
-----+-----
1298+
1 | 1
1299+
123 | 123
1300+
(2 rows)
1301+
1302+
update selfref set a = 456 where a = 123;
1303+
select a, b from selfref;
1304+
a | b
1305+
-----+-----
1306+
1 | 1
1307+
456 | 456
1308+
(2 rows)
1309+
1310+
commit;

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -899,3 +899,25 @@ SELECT * FROM tasks;
899899
DELETEFROM usersWHERE id=2;
900900
SELECT*FROM tasks;
901901
COMMIT;
902+
903+
--
904+
-- Test self-referential FK with CASCADE (bug #6268)
905+
--
906+
create temp table selfref (
907+
aintprimary key,
908+
bint,
909+
foreign key (b)references selfref (a)
910+
onupdate cascadeon delete cascade
911+
);
912+
913+
insert into selfref (a, b)
914+
values
915+
(0,0),
916+
(1,1);
917+
918+
begin;
919+
update selfrefset a=123where a=0;
920+
select a, bfrom selfref;
921+
update selfrefset a=456where a=123;
922+
select a, bfrom selfref;
923+
commit;

0 commit comments

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