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

Commitefd9366

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 parente6c3ba7 commitefd9366

File tree

4 files changed

+66
-8
lines changed

4 files changed

+66
-8
lines changed

‎src/backend/catalog/pg_constraint.c

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -783,6 +783,12 @@ ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
783783
constrForm= (Form_pg_constraint)GETSTRUCT(newtup);
784784
if (OidIsValid(parentConstrId))
785785
{
786+
/* don't allow setting parent for a constraint that already has one */
787+
Assert(constrForm->coninhcount==0);
788+
if (constrForm->conparentid!=InvalidOid)
789+
elog(ERROR,"constraint %u already has a parent constraint",
790+
childConstrId);
791+
786792
constrForm->conislocal= false;
787793
constrForm->coninhcount++;
788794
constrForm->conparentid=parentConstrId;
@@ -797,10 +803,12 @@ ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
797803
else
798804
{
799805
constrForm->coninhcount--;
800-
if (constrForm->coninhcount <=0)
801-
constrForm->conislocal= true;
806+
constrForm->conislocal= true;
802807
constrForm->conparentid=InvalidOid;
803808

809+
/* Make sure there's no further inheritance. */
810+
Assert(constrForm->coninhcount==0);
811+
804812
deleteDependencyRecordsForClass(ConstraintRelationId,childConstrId,
805813
ConstraintRelationId,
806814
DEPENDENCY_INTERNAL_AUTO);

‎src/backend/commands/tablecmds.c

Lines changed: 23 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7243,6 +7243,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
72437243
Oidpfeqoperators[INDEX_MAX_KEYS];
72447244
Oidppeqoperators[INDEX_MAX_KEYS];
72457245
Oidffeqoperators[INDEX_MAX_KEYS];
7246+
boolconnoinherit;
72467247
inti;
72477248
intnumfks,
72487249
numpks;
@@ -7586,6 +7587,12 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
75867587
ffeqoperators[i]=ffeqop;
75877588
}
75887589

7590+
/*
7591+
* FKs always inherit for partitioned tables, and never for legacy
7592+
* inheritance.
7593+
*/
7594+
connoinherit=rel->rd_rel->relkind!=RELKIND_PARTITIONED_TABLE;
7595+
75897596
/*
75907597
* Record the FK constraint in pg_constraint.
75917598
*/
@@ -7616,7 +7623,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
76167623
NULL,
76177624
true,/* islocal */
76187625
0,/* inhcount */
7619-
true,/*isnoinherit */
7626+
connoinherit,/*conNoInherit */
76207627
false);/* is_internal */
76217628
ObjectAddressSet(address,ConstraintRelationId,constrOid);
76227629

@@ -7937,7 +7944,7 @@ CloneFkReferencing(Relation pg_constraint, Relation parentRel,
79377944
}
79387945

79397946
systable_endscan(scan);
7940-
heap_close(trigrel,RowExclusiveLock);
7947+
table_close(trigrel,RowExclusiveLock);
79417948

79427949
ConstraintSetParentConstraint(fk->conoid,parentConstrOid);
79437950
CommandCounterIncrement();
@@ -9131,6 +9138,7 @@ ATExecDropConstraint(Relation rel, const char *constrName,
91319138
HeapTupletuple;
91329139
boolfound= false;
91339140
boolis_no_inherit_constraint= false;
9141+
charcontype;
91349142

91359143
/* At top level, permission check was done in ATPrepCmd, else do it */
91369144
if (recursing)
@@ -9171,6 +9179,7 @@ ATExecDropConstraint(Relation rel, const char *constrName,
91719179
constrName,RelationGetRelationName(rel))));
91729180

91739181
is_no_inherit_constraint=con->connoinherit;
9182+
contype=con->contype;
91749183

91759184
/*
91769185
* If it's a foreign-key constraint, we'd better lock the referenced
@@ -9179,7 +9188,7 @@ ATExecDropConstraint(Relation rel, const char *constrName,
91799188
* that has unfired events). But we can/must skip that in the
91809189
* self-referential case.
91819190
*/
9182-
if (con->contype==CONSTRAINT_FOREIGN&&
9191+
if (contype==CONSTRAINT_FOREIGN&&
91839192
con->confrelid!=RelationGetRelid(rel))
91849193
{
91859194
Relationfrel;
@@ -9223,6 +9232,17 @@ ATExecDropConstraint(Relation rel, const char *constrName,
92239232
}
92249233
}
92259234

9235+
/*
9236+
* For partitioned tables, non-CHECK inherited constraints are dropped via
9237+
* the dependency mechanism, so we're done here.
9238+
*/
9239+
if (contype!=CONSTRAINT_CHECK&&
9240+
rel->rd_rel->relkind==RELKIND_PARTITIONED_TABLE)
9241+
{
9242+
table_close(conrel,RowExclusiveLock);
9243+
return;
9244+
}
9245+
92269246
/*
92279247
* Propagate to children as appropriate. Unlike most other ALTER
92289248
* 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
@@ -1945,7 +1945,23 @@ DETAIL: Key (a)=(2) is not present in table "pkey".
19451945
delete from fkpart1.pkey where a = 1;
19461946
ERROR: update or delete on table "pkey" violates foreign key constraint "fk_part_a_fkey" on table "fk_part_1"
19471947
DETAIL: Key (a)=(1) is still referenced from table "fk_part_1".
1948+
-- verify that attaching and detaching partitions manipulates the inheritance
1949+
-- properties of their FK constraints correctly
1950+
create schema fkpart2
1951+
create table pkey (a int primary key)
1952+
create table fk_part (a int, constraint fkey foreign key (a) references fkpart2.pkey) partition by list (a)
1953+
create table fk_part_1 partition of fkpart2.fk_part for values in (1) partition by list (a)
1954+
create table fk_part_1_1 (a int, constraint my_fkey foreign key (a) references fkpart2.pkey);
1955+
alter table fkpart2.fk_part_1 attach partition fkpart2.fk_part_1_1 for values in (1);
1956+
alter table fkpart2.fk_part_1 drop constraint fkey;-- should fail
1957+
ERROR: cannot drop inherited constraint "fkey" of relation "fk_part_1"
1958+
alter table fkpart2.fk_part_1_1 drop constraint my_fkey;-- should fail
1959+
ERROR: cannot drop inherited constraint "my_fkey" of relation "fk_part_1_1"
1960+
alter table fkpart2.fk_part detach partition fkpart2.fk_part_1;
1961+
alter table fkpart2.fk_part_1 drop constraint fkey;-- ok
1962+
alter table fkpart2.fk_part_1_1 drop constraint my_fkey;-- doesn't exist
1963+
ERROR: constraint "my_fkey" of relation "fk_part_1_1" does not exist
19481964
\set VERBOSITY terse\\ -- suppress cascade details
1949-
drop schema fkpart0, fkpart1 cascade;
1950-
NOTICE: drop cascades to5 other objects
1965+
drop schema fkpart0, fkpart1, fkpart2 cascade;
1966+
NOTICE: drop cascades to8 other objects
19511967
\set VERBOSITY default

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

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

1395+
-- verify that attaching and detaching partitions manipulates the inheritance
1396+
-- properties of their FK constraints correctly
1397+
createschemafkpart2
1398+
createtablepkey (aintprimary key)
1399+
createtablefk_part (aint,constraint fkeyforeign key (a)referencesfkpart2.pkey) partition by list (a)
1400+
createtablefk_part_1 partition offkpart2.fk_part forvaluesin (1) partition by list (a)
1401+
createtablefk_part_1_1 (aint,constraint my_fkeyforeign key (a)referencesfkpart2.pkey);
1402+
altertablefkpart2.fk_part_1 attach partitionfkpart2.fk_part_1_1 forvaluesin (1);
1403+
altertablefkpart2.fk_part_1 dropconstraint fkey;-- should fail
1404+
altertablefkpart2.fk_part_1_1 dropconstraint my_fkey;-- should fail
1405+
altertablefkpart2.fk_part detach partitionfkpart2.fk_part_1;
1406+
altertablefkpart2.fk_part_1 dropconstraint fkey;-- ok
1407+
altertablefkpart2.fk_part_1_1 dropconstraint my_fkey;-- doesn't exist
1408+
13951409
\set VERBOSITY terse\\-- suppress cascade details
1396-
dropschema fkpart0, fkpart1 cascade;
1410+
dropschema fkpart0, fkpart1, fkpart2 cascade;
13971411
\set VERBOSITY default

0 commit comments

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