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

Commitec7f9a7

Browse files
committed
Check that partitions are not in use when dropping constraints
If the user creates a deferred constraint in a partition, and in atransaction they cause the constraint's trigger execution to be deferreduntil commit time *and* drop the constraint, then when commit time comesthe queued trigger will fail to run because the trigger object will havebeen dropped.This is explained because when a constraint gets dropped in apartitioned table, the recursion to drop the ones in partitions is doneby the dependency mechanism, not by ALTER TABLE traversing the recursiontree as in all other cases. In the non-partitioned case, this problemis avoided by checking that the table is not "in use" by alter-table;other alter-table subcommands that recurse to partitions do that checkfor each partition. But the dependency mechanism doesn't have a way todo that. Fix the problem by applying the same check to all partitionsduring ALTER TABLE's "prep" phase, which correctly raises the necessaryerror.Reported-by: Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>Discussion:https://postgr.es/m/CAKcux6nZiO9-eEpr1ZD84bT1mBoVmeZkfont8iSpcmYrjhGWgA@mail.gmail.com
1 parent8d21512 commitec7f9a7

File tree

3 files changed

+70
-14
lines changed

3 files changed

+70
-14
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 45 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -353,6 +353,7 @@ static void ATSimplePermissions(Relation rel, int allowed_targets);
353353
staticvoidATWrongRelkindError(Relationrel,intallowed_targets);
354354
staticvoidATSimpleRecursion(List**wqueue,Relationrel,
355355
AlterTableCmd*cmd,boolrecurse,LOCKMODElockmode);
356+
staticvoidATCheckPartitionsNotInUse(Relationrel,LOCKMODElockmode);
356357
staticvoidATTypedTableRecursion(List**wqueue,Relationrel,AlterTableCmd*cmd,
357358
LOCKMODElockmode);
358359
staticList*find_typed_table_dependencies(OidtypeOid,constchar*typeName,
@@ -3269,8 +3270,7 @@ CheckTableNotInUse(Relation rel, const char *stmt)
32693270
ereport(ERROR,
32703271
(errcode(ERRCODE_OBJECT_IN_USE),
32713272
/* translator: first %s is a SQL command, eg ALTER TABLE */
3272-
errmsg("cannot %s \"%s\" because "
3273-
"it is being used by active queries in this session",
3273+
errmsg("cannot %s \"%s\" because it is being used by active queries in this session",
32743274
stmt,RelationGetRelationName(rel))));
32753275

32763276
if (rel->rd_rel->relkind!=RELKIND_INDEX&&
@@ -3279,8 +3279,7 @@ CheckTableNotInUse(Relation rel, const char *stmt)
32793279
ereport(ERROR,
32803280
(errcode(ERRCODE_OBJECT_IN_USE),
32813281
/* translator: first %s is a SQL command, eg ALTER TABLE */
3282-
errmsg("cannot %s \"%s\" because "
3283-
"it has pending trigger events",
3282+
errmsg("cannot %s \"%s\" because it has pending trigger events",
32843283
stmt,RelationGetRelationName(rel))));
32853284
}
32863285

@@ -3746,16 +3745,19 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
37463745
break;
37473746
caseAT_AddIdentity:
37483747
ATSimplePermissions(rel,ATT_TABLE |ATT_VIEW |ATT_FOREIGN_TABLE);
3748+
/* This command never recurses */
37493749
pass=AT_PASS_ADD_CONSTR;
37503750
break;
3751-
caseAT_DropIdentity:
3752-
ATSimplePermissions(rel,ATT_TABLE |ATT_VIEW |ATT_FOREIGN_TABLE);
3753-
pass=AT_PASS_DROP;
3754-
break;
37553751
caseAT_SetIdentity:
37563752
ATSimplePermissions(rel,ATT_TABLE |ATT_VIEW |ATT_FOREIGN_TABLE);
3753+
/* This command never recurses */
37573754
pass=AT_PASS_COL_ATTRS;
37583755
break;
3756+
caseAT_DropIdentity:
3757+
ATSimplePermissions(rel,ATT_TABLE |ATT_VIEW |ATT_FOREIGN_TABLE);
3758+
/* This command never recurses */
3759+
pass=AT_PASS_DROP;
3760+
break;
37593761
caseAT_DropNotNull:/* ALTER COLUMN DROP NOT NULL */
37603762
ATSimplePermissions(rel,ATT_TABLE |ATT_FOREIGN_TABLE);
37613763
ATPrepDropNotNull(rel,recurse,recursing);
@@ -3817,7 +3819,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
38173819
break;
38183820
caseAT_DropConstraint:/* DROP CONSTRAINT */
38193821
ATSimplePermissions(rel,ATT_TABLE |ATT_FOREIGN_TABLE);
3820-
/* Recursion occurs during execution phase */
3822+
ATCheckPartitionsNotInUse(rel,lockmode);
3823+
/* Other recursion occurs during execution phase */
38213824
/* No command-specific prep needed except saving recurse flag */
38223825
if (recurse)
38233826
cmd->subtype=AT_DropConstraintRecurse;
@@ -5085,8 +5088,9 @@ ATSimpleRecursion(List **wqueue, Relation rel,
50855088
AlterTableCmd*cmd,boolrecurse,LOCKMODElockmode)
50865089
{
50875090
/*
5088-
* Propagate to children if desired. Only plain tables and foreign tables
5089-
* have children, so no need to search for other relkinds.
5091+
* Propagate to children if desired. Only plain tables, foreign tables
5092+
* and partitioned tables have children, so no need to search for other
5093+
* relkinds.
50905094
*/
50915095
if (recurse&&
50925096
(rel->rd_rel->relkind==RELKIND_RELATION||
@@ -5120,6 +5124,36 @@ ATSimpleRecursion(List **wqueue, Relation rel,
51205124
}
51215125
}
51225126

5127+
/*
5128+
* Obtain list of partitions of the given table, locking them all at the given
5129+
* lockmode and ensuring that they all pass CheckTableNotInUse.
5130+
*
5131+
* This function is a no-op if the given relation is not a partitioned table;
5132+
* in particular, nothing is done if it's a legacy inheritance parent.
5133+
*/
5134+
staticvoid
5135+
ATCheckPartitionsNotInUse(Relationrel,LOCKMODElockmode)
5136+
{
5137+
if (rel->rd_rel->relkind==RELKIND_PARTITIONED_TABLE)
5138+
{
5139+
List*inh;
5140+
ListCell*cell;
5141+
5142+
inh=find_all_inheritors(RelationGetRelid(rel),lockmode,NULL);
5143+
/* first element is the parent rel; must ignore it */
5144+
for_each_cell(cell,lnext(list_head(inh)))
5145+
{
5146+
Relationchildrel;
5147+
5148+
/* find_all_inheritors already got lock */
5149+
childrel=heap_open(lfirst_oid(cell),NoLock);
5150+
CheckTableNotInUse(childrel,"ALTER TABLE");
5151+
heap_close(childrel,NoLock);
5152+
}
5153+
list_free(inh);
5154+
}
5155+
}
5156+
51235157
/*
51245158
* ATTypedTableRecursion
51255159
*

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

Lines changed: 13 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1926,7 +1926,18 @@ alter table fkpart2.fk_part detach partition fkpart2.fk_part_1;
19261926
alter table fkpart2.fk_part_1 drop constraint fkey;-- ok
19271927
alter table fkpart2.fk_part_1_1 drop constraint my_fkey;-- doesn't exist
19281928
ERROR: constraint "my_fkey" of relation "fk_part_1_1" does not exist
1929+
-- ensure we check partitions are "not used" when dropping constraints
1930+
CREATE SCHEMA fkpart8
1931+
CREATE TABLE tbl1(f1 int PRIMARY KEY)
1932+
CREATE TABLE tbl2(f1 int REFERENCES tbl1 DEFERRABLE INITIALLY DEFERRED) PARTITION BY RANGE(f1)
1933+
CREATE TABLE tbl2_p1 PARTITION OF tbl2 FOR VALUES FROM (minvalue) TO (maxvalue);
1934+
INSERT INTO fkpart8.tbl1 VALUES(1);
1935+
BEGIN;
1936+
INSERT INTO fkpart8.tbl2 VALUES(1);
1937+
ALTER TABLE fkpart8.tbl2 DROP CONSTRAINT tbl2_f1_fkey;
1938+
ERROR: cannot ALTER TABLE "tbl2_p1" because it has pending trigger events
1939+
COMMIT;
19291940
\set VERBOSITY terse\\ -- suppress cascade details
1930-
drop schema fkpart0, fkpart1, fkpart2 cascade;
1931-
NOTICE: drop cascades to8 other objects
1941+
drop schema fkpart0, fkpart1, fkpart2, fkpart8 cascade;
1942+
NOTICE: drop cascades to10 other objects
19321943
\set VERBOSITY default

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

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1380,6 +1380,17 @@ alter table fkpart2.fk_part detach partition fkpart2.fk_part_1;
13801380
altertablefkpart2.fk_part_1 dropconstraint fkey;-- ok
13811381
altertablefkpart2.fk_part_1_1 dropconstraint my_fkey;-- doesn't exist
13821382

1383+
-- ensure we check partitions are "not used" when dropping constraints
1384+
CREATESCHEMAfkpart8
1385+
CREATETABLEtbl1(f1intPRIMARY KEY)
1386+
CREATETABLEtbl2(f1intREFERENCES tbl1 DEFERRABLE INITIALLY DEFERRED) PARTITION BY RANGE(f1)
1387+
CREATETABLEtbl2_p1 PARTITION OF tbl2 FORVALUESFROM (minvalue) TO (maxvalue);
1388+
INSERT INTOfkpart8.tbl1VALUES(1);
1389+
BEGIN;
1390+
INSERT INTOfkpart8.tbl2VALUES(1);
1391+
ALTERTABLEfkpart8.tbl2 DROPCONSTRAINT tbl2_f1_fkey;
1392+
COMMIT;
1393+
13831394
\set VERBOSITY terse\\-- suppress cascade details
1384-
dropschema fkpart0, fkpart1, fkpart2 cascade;
1395+
dropschema fkpart0, fkpart1, fkpart2, fkpart8 cascade;
13851396
\set VERBOSITY default

0 commit comments

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