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

Commita482043

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 parentf707b53 commita482043

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
@@ -6603,12 +6603,12 @@ static ObjectAddress
66036603
ATExecAlterConstraint(Relationrel,AlterTableCmd*cmd,
66046604
boolrecurse,boolrecursing,LOCKMODElockmode)
66056605
{
6606+
Constraint*cmdcon;
66066607
Relationconrel;
66076608
SysScanDescscan;
66086609
ScanKeyDatakey;
66096610
HeapTuplecontuple;
66106611
Form_pg_constraintcurrcon=NULL;
6611-
Constraint*cmdcon=NULL;
66126612
boolfound= false;
66136613
ObjectAddressaddress;
66146614

@@ -6655,10 +6655,11 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
66556655
HeapTuplecopyTuple;
66566656
HeapTupletgtuple;
66576657
Form_pg_constraintcopy_con;
6658-
Form_pg_triggercopy_tg;
6658+
List*otherrelids=NIL;
66596659
ScanKeyDatatgkey;
66606660
SysScanDesctgscan;
66616661
Relationtgrel;
6662+
ListCell*lc;
66626663

66636664
/*
66646665
* Now update the catalog, while we have the door open.
@@ -6691,8 +6692,16 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
66916692

66926693
while (HeapTupleIsValid(tgtuple=systable_getnext(tgscan)))
66936694
{
6695+
Form_pg_triggercopy_tg;
6696+
66946697
copyTuple=heap_copytuple(tgtuple);
66956698
copy_tg= (Form_pg_trigger)GETSTRUCT(copyTuple);
6699+
6700+
/* Remember OIDs of other relation(s) involved in FK constraint */
6701+
if (copy_tg->tgrelid!=RelationGetRelid(rel))
6702+
otherrelids=list_append_unique_oid(otherrelids,
6703+
copy_tg->tgrelid);
6704+
66966705
copy_tg->tgdeferrable=cmdcon->deferrable;
66976706
copy_tg->tginitdeferred=cmdcon->initdeferred;
66986707
simple_heap_update(tgrel,&copyTuple->t_self,copyTuple);
@@ -6709,9 +6718,16 @@ ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
67096718
heap_close(tgrel,RowExclusiveLock);
67106719

67116720
/*
6712-
* Invalidate relcache so that others see the new attributes.
6721+
* Invalidate relcache so that others see the new attributes. We must
6722+
* inval both the named rel and any others having relevant triggers.
6723+
* (At present there should always be exactly one other rel, but
6724+
* there's no need to hard-wire such an assumption here.)
67136725
*/
67146726
CacheInvalidateRelcache(rel);
6727+
foreach(lc,otherrelids)
6728+
{
6729+
CacheInvalidateRelcacheByRelid(lfirst_oid(lc));
6730+
}
67156731

67166732
ObjectAddressSet(address,ConstraintRelationId,
67176733
HeapTupleGetOid(contuple));

‎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