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

Commit79edb29

Browse files
committed
Fix overlooked relcache invalidation in ALTER TABLE ... ALTER CONSTRAINT.
When altering the deferredness state of a foreign key constraint, wecorrectly updated the catalogs and then invalidated the relcache state forthe target relation ... but that's not the only relation with relevanttriggers. Must invalidate the other table as well, or the state changefails to take effect promptly for operations triggered on the other table.Per bug #13224 from Christian Ullrich.In passing, reorganize regression test case for this feature so that itisn't randomly injected into the middle of an unrelated test sequence.Oversight in commitf177cbf. Back-patchto 9.4 where the faulty code was added.
1 parent70fac48 commit79edb29

File tree

3 files changed

+70
-42
lines changed

3 files changed

+70
-42
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 19 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -6308,12 +6308,12 @@ static void
63086308
ATExecAlterConstraint(Relationrel,AlterTableCmd*cmd,
63096309
boolrecurse,boolrecursing,LOCKMODElockmode)
63106310
{
6311+
Constraint*cmdcon;
63116312
Relationconrel;
63126313
SysScanDescscan;
63136314
ScanKeyDatakey;
63146315
HeapTuplecontuple;
63156316
Form_pg_constraintcurrcon=NULL;
6316-
Constraint*cmdcon=NULL;
63176317
boolfound= false;
63186318

63196319
Assert(IsA(cmd->def,Constraint));
@@ -6359,10 +6359,11 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
63596359
HeapTuplecopyTuple;
63606360
HeapTupletgtuple;
63616361
Form_pg_constraintcopy_con;
6362-
Form_pg_triggercopy_tg;
6362+
List*otherrelids=NIL;
63636363
ScanKeyDatatgkey;
63646364
SysScanDesctgscan;
63656365
Relationtgrel;
6366+
ListCell*lc;
63666367

63676368
/*
63686369
* Now update the catalog, while we have the door open.
@@ -6395,8 +6396,16 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
63956396

63966397
while (HeapTupleIsValid(tgtuple=systable_getnext(tgscan)))
63976398
{
6399+
Form_pg_triggercopy_tg;
6400+
63986401
copyTuple=heap_copytuple(tgtuple);
63996402
copy_tg= (Form_pg_trigger)GETSTRUCT(copyTuple);
6403+
6404+
/* Remember OIDs of other relation(s) involved in FK constraint */
6405+
if (copy_tg->tgrelid!=RelationGetRelid(rel))
6406+
otherrelids=list_append_unique_oid(otherrelids,
6407+
copy_tg->tgrelid);
6408+
64006409
copy_tg->tgdeferrable=cmdcon->deferrable;
64016410
copy_tg->tginitdeferred=cmdcon->initdeferred;
64026411
simple_heap_update(tgrel,&copyTuple->t_self,copyTuple);
@@ -6413,9 +6422,16 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
64136422
heap_close(tgrel,RowExclusiveLock);
64146423

64156424
/*
6416-
* Invalidate relcache so that others see the new attributes.
6425+
* Invalidate relcache so that others see the new attributes. We must
6426+
* inval both the named rel and any others having relevant triggers.
6427+
* (At present there should always be exactly one other rel, but
6428+
* there's no need to hard-wire such an assumption here.)
64176429
*/
64186430
CacheInvalidateRelcache(rel);
6431+
foreach(lc,otherrelids)
6432+
{
6433+
CacheInvalidateRelcacheByRelid(lfirst_oid(lc));
6434+
}
64196435
}
64206436

64216437
systable_endscan(scan);

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

Lines changed: 24 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -1132,15 +1132,6 @@ CREATE TEMP TABLE fktable (
11321132
id int primary key,
11331133
fk int references pktable deferrable initially deferred
11341134
);
1135-
-- check ALTER CONSTRAINT
1136-
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
1137-
-- illegal option
1138-
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED;
1139-
ERROR: constraint declared INITIALLY DEFERRED must be DEFERRABLE
1140-
LINE 1: ...e ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY ...
1141-
^
1142-
-- reset
1143-
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
11441135
INSERT INTO pktable VALUES (5, 10);
11451136
BEGIN;
11461137
-- doesn't match PK, but no error yet
@@ -1151,16 +1142,6 @@ UPDATE fktable SET id = id + 1;
11511142
COMMIT;
11521143
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
11531144
DETAIL: Key (fk)=(20) is not present in table "pktable".
1154-
-- change the constraint definition and retest
1155-
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
1156-
BEGIN;
1157-
-- doesn't match PK, should throw error now
1158-
INSERT INTO fktable VALUES (0, 20);
1159-
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1160-
DETAIL: Key (fk)=(20) is not present in table "pktable".
1161-
COMMIT;
1162-
-- reset
1163-
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
11641145
-- check same case when insert is in a different subtransaction than update
11651146
BEGIN;
11661147
-- doesn't match PK, but no error yet
@@ -1198,6 +1179,30 @@ ROLLBACK TO savept1;
11981179
COMMIT;
11991180
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
12001181
DETAIL: Key (fk)=(20) is not present in table "pktable".
1182+
--
1183+
-- check ALTER CONSTRAINT
1184+
--
1185+
INSERT INTO fktable VALUES (1, 5);
1186+
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
1187+
BEGIN;
1188+
-- doesn't match FK, should throw error now
1189+
UPDATE pktable SET id = 10 WHERE id = 5;
1190+
ERROR: update or delete on table "pktable" violates foreign key constraint "fktable_fk_fkey" on table "fktable"
1191+
DETAIL: Key (id)=(5) is still referenced from table "fktable".
1192+
COMMIT;
1193+
BEGIN;
1194+
-- doesn't match PK, should throw error now
1195+
INSERT INTO fktable VALUES (0, 20);
1196+
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1197+
DETAIL: Key (fk)=(20) is not present in table "pktable".
1198+
COMMIT;
1199+
-- try additional syntax
1200+
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
1201+
-- illegal option
1202+
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED;
1203+
ERROR: constraint declared INITIALLY DEFERRED must be DEFERRABLE
1204+
LINE 1: ...e ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY ...
1205+
^
12011206
-- test order of firing of FK triggers when several RI-induced changes need to
12021207
-- be made to the same row. This was broken by subtransaction-related
12031208
-- changes in 8.0.

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

Lines changed: 27 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -818,13 +818,6 @@ CREATE TEMP TABLE fktable (
818818
fkintreferences pktable deferrable initially deferred
819819
);
820820

821-
-- check ALTER CONSTRAINT
822-
ALTERTABLE fktable ALTERCONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
823-
-- illegal option
824-
ALTERTABLE fktable ALTERCONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED;
825-
-- reset
826-
ALTERTABLE fktable ALTERCONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
827-
828821
INSERT INTO pktableVALUES (5,10);
829822

830823
BEGIN;
@@ -838,19 +831,6 @@ UPDATE fktable SET id = id + 1;
838831
-- should catch error from initial INSERT
839832
COMMIT;
840833

841-
-- change the constraint definition and retest
842-
ALTERTABLE fktable ALTERCONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
843-
844-
BEGIN;
845-
846-
-- doesn't match PK, should throw error now
847-
INSERT INTO fktableVALUES (0,20);
848-
849-
COMMIT;
850-
851-
-- reset
852-
ALTERTABLE fktable ALTERCONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
853-
854834
-- check same case when insert is in a different subtransaction than update
855835

856836
BEGIN;
@@ -900,6 +880,33 @@ ROLLBACK TO savept1;
900880
-- should catch error from initial INSERT
901881
COMMIT;
902882

883+
--
884+
-- check ALTER CONSTRAINT
885+
--
886+
887+
INSERT INTO fktableVALUES (1,5);
888+
889+
ALTERTABLE fktable ALTERCONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
890+
891+
BEGIN;
892+
893+
-- doesn't match FK, should throw error now
894+
UPDATE pktableSET id=10WHERE id=5;
895+
896+
COMMIT;
897+
898+
BEGIN;
899+
900+
-- doesn't match PK, should throw error now
901+
INSERT INTO fktableVALUES (0,20);
902+
903+
COMMIT;
904+
905+
-- try additional syntax
906+
ALTERTABLE fktable ALTERCONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
907+
-- illegal option
908+
ALTERTABLE fktable ALTERCONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED;
909+
903910
-- test order of firing of FK triggers when several RI-induced changes need to
904911
-- be made to the same row. This was broken by subtransaction-related
905912
-- changes in 8.0.

0 commit comments

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