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

Commit1ad5210

Browse files
committed
Fix droppability of constraints upon partition detach
We were failing to set conislocal correctly for constraints inpartitions after partition detach, leading to those constraints becomingundroppable. Fix by setting the flag correctly. Existing databasesmight contain constraints with the conislocal wrongly set to false, forpartitions that were detached; this situation should be fixable byapplying an UPDATE on pg_constraint to set conislocal true. Thisproblem should otherwise be innocuous and should disappear across adump/restore or pg_upgrade.Secondarily, when constraint drop was attempted in a partitioned table,ATExecDropConstraint would try to recurse to partitions after doingperformDeletion() of the constraint in the partitioned table itself; butsince the constraint in the partitions are dropped by the initial callof performDeletion() (because of following dependencies), the recursionstep would fail since it would not find the constraint, causing thewhole operation to fail. Fix by preventing recursion.Reported-by: Amit LangoteDiagnosed-by: Amit LangoteAuthor: Amit Langote, Álvaro HerreraDiscussion:https://postgr.es/m/f2b8ead5-4131-d5a8-8016-2ea0a31250af@lab.ntt.co.jp
1 parent27d6bc6 commit1ad5210

File tree

4 files changed

+65
-7
lines changed

4 files changed

+65
-7
lines changed

‎src/backend/catalog/pg_constraint.c

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -792,6 +792,12 @@ ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
792792
constrForm= (Form_pg_constraint)GETSTRUCT(newtup);
793793
if (OidIsValid(parentConstrId))
794794
{
795+
/* don't allow setting parent for a constraint that already has one */
796+
Assert(constrForm->coninhcount==0);
797+
if (constrForm->conparentid!=InvalidOid)
798+
elog(ERROR,"constraint %u already has a parent constraint",
799+
childConstrId);
800+
795801
constrForm->conislocal= false;
796802
constrForm->coninhcount++;
797803
constrForm->conparentid=parentConstrId;
@@ -806,10 +812,12 @@ ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
806812
else
807813
{
808814
constrForm->coninhcount--;
809-
if (constrForm->coninhcount <=0)
810-
constrForm->conislocal= true;
815+
constrForm->conislocal= true;
811816
constrForm->conparentid=InvalidOid;
812817

818+
/* Make sure there's no further inheritance. */
819+
Assert(constrForm->coninhcount==0);
820+
813821
deleteDependencyRecordsForClass(ConstraintRelationId,childConstrId,
814822
ConstraintRelationId,
815823
DEPENDENCY_INTERNAL_AUTO);

‎src/backend/commands/tablecmds.c

Lines changed: 22 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7336,6 +7336,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
73367336
Oidpfeqoperators[INDEX_MAX_KEYS];
73377337
Oidppeqoperators[INDEX_MAX_KEYS];
73387338
Oidffeqoperators[INDEX_MAX_KEYS];
7339+
boolconnoinherit;
73397340
inti;
73407341
intnumfks,
73417342
numpks;
@@ -7679,6 +7680,12 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
76797680
ffeqoperators[i]=ffeqop;
76807681
}
76817682

7683+
/*
7684+
* FKs always inherit for partitioned tables, and never for legacy
7685+
* inheritance.
7686+
*/
7687+
connoinherit=rel->rd_rel->relkind!=RELKIND_PARTITIONED_TABLE;
7688+
76827689
/*
76837690
* Record the FK constraint in pg_constraint.
76847691
*/
@@ -7710,7 +7717,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
77107717
NULL,
77117718
true,/* islocal */
77127719
0,/* inhcount */
7713-
true,/*isnoinherit */
7720+
connoinherit,/*conNoInherit */
77147721
false);/* is_internal */
77157722
ObjectAddressSet(address,ConstraintRelationId,constrOid);
77167723

@@ -9230,6 +9237,7 @@ ATExecDropConstraint(Relation rel, const char *constrName,
92309237
HeapTupletuple;
92319238
boolfound= false;
92329239
boolis_no_inherit_constraint= false;
9240+
charcontype;
92339241

92349242
/* At top level, permission check was done in ATPrepCmd, else do it */
92359243
if (recursing)
@@ -9270,6 +9278,7 @@ ATExecDropConstraint(Relation rel, const char *constrName,
92709278
constrName,RelationGetRelationName(rel))));
92719279

92729280
is_no_inherit_constraint=con->connoinherit;
9281+
contype=con->contype;
92739282

92749283
/*
92759284
* If it's a foreign-key constraint, we'd better lock the referenced
@@ -9278,7 +9287,7 @@ ATExecDropConstraint(Relation rel, const char *constrName,
92789287
* that has unfired events). But we can/must skip that in the
92799288
* self-referential case.
92809289
*/
9281-
if (con->contype==CONSTRAINT_FOREIGN&&
9290+
if (contype==CONSTRAINT_FOREIGN&&
92829291
con->confrelid!=RelationGetRelid(rel))
92839292
{
92849293
Relationfrel;
@@ -9322,6 +9331,17 @@ ATExecDropConstraint(Relation rel, const char *constrName,
93229331
}
93239332
}
93249333

9334+
/*
9335+
* For partitioned tables, non-CHECK inherited constraints are dropped via
9336+
* the dependency mechanism, so we're done here.
9337+
*/
9338+
if (contype!=CONSTRAINT_CHECK&&
9339+
rel->rd_rel->relkind==RELKIND_PARTITIONED_TABLE)
9340+
{
9341+
heap_close(conrel,RowExclusiveLock);
9342+
return;
9343+
}
9344+
93259345
/*
93269346
* Propagate to children as appropriate. Unlike most other ALTER
93279347
* routines, we have to do this one level of recursion at a time; we can't

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

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1884,7 +1884,23 @@ DETAIL: Key (a)=(2) is not present in table "pkey".
18841884
delete from fkpart1.pkey where a = 1;
18851885
ERROR: update or delete on table "pkey" violates foreign key constraint "fk_part_a_fkey" on table "fk_part_1"
18861886
DETAIL: Key (a)=(1) is still referenced from table "fk_part_1".
1887+
-- verify that attaching and detaching partitions manipulates the inheritance
1888+
-- properties of their FK constraints correctly
1889+
create schema fkpart2
1890+
create table pkey (a int primary key)
1891+
create table fk_part (a int, constraint fkey foreign key (a) references fkpart2.pkey) partition by list (a)
1892+
create table fk_part_1 partition of fkpart2.fk_part for values in (1) partition by list (a)
1893+
create table fk_part_1_1 (a int, constraint my_fkey foreign key (a) references fkpart2.pkey);
1894+
alter table fkpart2.fk_part_1 attach partition fkpart2.fk_part_1_1 for values in (1);
1895+
alter table fkpart2.fk_part_1 drop constraint fkey;-- should fail
1896+
ERROR: cannot drop inherited constraint "fkey" of relation "fk_part_1"
1897+
alter table fkpart2.fk_part_1_1 drop constraint my_fkey;-- should fail
1898+
ERROR: cannot drop inherited constraint "my_fkey" of relation "fk_part_1_1"
1899+
alter table fkpart2.fk_part detach partition fkpart2.fk_part_1;
1900+
alter table fkpart2.fk_part_1 drop constraint fkey;-- ok
1901+
alter table fkpart2.fk_part_1_1 drop constraint my_fkey;-- doesn't exist
1902+
ERROR: constraint "my_fkey" of relation "fk_part_1_1" does not exist
18871903
\set VERBOSITY terse\\ -- suppress cascade details
1888-
drop schema fkpart0, fkpart1 cascade;
1889-
NOTICE: drop cascades to5 other objects
1904+
drop schema fkpart0, fkpart1, fkpart2 cascade;
1905+
NOTICE: drop cascades to8 other objects
18901906
\set VERBOSITY default

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

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1341,6 +1341,20 @@ create table fkpart1.fk_part_1_2 partition of fkpart1.fk_part_1 for values in (2
13411341
insert intofkpart1.fk_part_1values (2);-- should fail
13421342
deletefromfkpart1.pkeywhere a=1;
13431343

1344+
-- verify that attaching and detaching partitions manipulates the inheritance
1345+
-- properties of their FK constraints correctly
1346+
createschemafkpart2
1347+
createtablepkey (aintprimary key)
1348+
createtablefk_part (aint,constraint fkeyforeign key (a)referencesfkpart2.pkey) partition by list (a)
1349+
createtablefk_part_1 partition offkpart2.fk_part forvaluesin (1) partition by list (a)
1350+
createtablefk_part_1_1 (aint,constraint my_fkeyforeign key (a)referencesfkpart2.pkey);
1351+
altertablefkpart2.fk_part_1 attach partitionfkpart2.fk_part_1_1 forvaluesin (1);
1352+
altertablefkpart2.fk_part_1 dropconstraint fkey;-- should fail
1353+
altertablefkpart2.fk_part_1_1 dropconstraint my_fkey;-- should fail
1354+
altertablefkpart2.fk_part detach partitionfkpart2.fk_part_1;
1355+
altertablefkpart2.fk_part_1 dropconstraint fkey;-- ok
1356+
altertablefkpart2.fk_part_1_1 dropconstraint my_fkey;-- doesn't exist
1357+
13441358
\set VERBOSITY terse\\-- suppress cascade details
1345-
dropschema fkpart0, fkpart1 cascade;
1359+
dropschema fkpart0, fkpart1, fkpart2 cascade;
13461360
\set VERBOSITY default

0 commit comments

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