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

Commit5562272

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 parent24f62e9 commit5562272

File tree

3 files changed

+72
-11
lines changed

3 files changed

+72
-11
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 45 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -354,6 +354,7 @@ static void ATSimplePermissions(Relation rel, int allowed_targets);
354354
static void ATWrongRelkindError(Relation rel, int allowed_targets);
355355
static void ATSimpleRecursion(List **wqueue, Relation rel,
356356
AlterTableCmd *cmd, bool recurse, LOCKMODE lockmode);
357+
static void ATCheckPartitionsNotInUse(Relation rel, LOCKMODE lockmode);
357358
static void ATTypedTableRecursion(List **wqueue, Relation rel, AlterTableCmd *cmd,
358359
LOCKMODE lockmode);
359360
static List *find_typed_table_dependencies(Oid typeOid, const char *typeName,
@@ -3421,8 +3422,7 @@ CheckTableNotInUse(Relation rel, const char *stmt)
34213422
ereport(ERROR,
34223423
(errcode(ERRCODE_OBJECT_IN_USE),
34233424
/* translator: first %s is a SQL command, eg ALTER TABLE */
3424-
errmsg("cannot %s \"%s\" because "
3425-
"it is being used by active queries in this session",
3425+
errmsg("cannot %s \"%s\" because it is being used by active queries in this session",
34263426
stmt, RelationGetRelationName(rel))));
34273427

34283428
if (rel->rd_rel->relkind != RELKIND_INDEX &&
@@ -3431,8 +3431,7 @@ CheckTableNotInUse(Relation rel, const char *stmt)
34313431
ereport(ERROR,
34323432
(errcode(ERRCODE_OBJECT_IN_USE),
34333433
/* translator: first %s is a SQL command, eg ALTER TABLE */
3434-
errmsg("cannot %s \"%s\" because "
3435-
"it has pending trigger events",
3434+
errmsg("cannot %s \"%s\" because it has pending trigger events",
34363435
stmt, RelationGetRelationName(rel))));
34373436
}
34383437

@@ -3910,16 +3909,19 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
39103909
break;
39113910
case AT_AddIdentity:
39123911
ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
3912+
/* This command never recurses */
39133913
pass = AT_PASS_ADD_CONSTR;
39143914
break;
3915-
case AT_DropIdentity:
3916-
ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
3917-
pass = AT_PASS_DROP;
3918-
break;
39193915
case AT_SetIdentity:
39203916
ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
3917+
/* This command never recurses */
39213918
pass = AT_PASS_COL_ATTRS;
39223919
break;
3920+
case AT_DropIdentity:
3921+
ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
3922+
/* This command never recurses */
3923+
pass = AT_PASS_DROP;
3924+
break;
39233925
case AT_DropNotNull:/* ALTER COLUMN DROP NOT NULL */
39243926
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
39253927
ATPrepDropNotNull(rel, recurse, recursing);
@@ -3985,7 +3987,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
39853987
break;
39863988
case AT_DropConstraint: /* DROP CONSTRAINT */
39873989
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
3988-
/* Recursion occurs during execution phase */
3990+
ATCheckPartitionsNotInUse(rel, lockmode);
3991+
/* Other recursion occurs during execution phase */
39893992
/* No command-specific prep needed except saving recurse flag */
39903993
if (recurse)
39913994
cmd->subtype = AT_DropConstraintRecurse;
@@ -5224,8 +5227,9 @@ ATSimpleRecursion(List **wqueue, Relation rel,
52245227
AlterTableCmd *cmd, bool recurse, LOCKMODE lockmode)
52255228
{
52265229
/*
5227-
* Propagate to children if desired. Only plain tables and foreign tables
5228-
* have children, so no need to search for other relkinds.
5230+
* Propagate to children if desired. Only plain tables, foreign tables
5231+
* and partitioned tables have children, so no need to search for other
5232+
* relkinds.
52295233
*/
52305234
if (recurse &&
52315235
(rel->rd_rel->relkind == RELKIND_RELATION ||
@@ -5259,6 +5263,36 @@ ATSimpleRecursion(List **wqueue, Relation rel,
52595263
}
52605264
}
52615265

5266+
/*
5267+
* Obtain list of partitions of the given table, locking them all at the given
5268+
* lockmode and ensuring that they all pass CheckTableNotInUse.
5269+
*
5270+
* This function is a no-op if the given relation is not a partitioned table;
5271+
* in particular, nothing is done if it's a legacy inheritance parent.
5272+
*/
5273+
static void
5274+
ATCheckPartitionsNotInUse(Relation rel, LOCKMODE lockmode)
5275+
{
5276+
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
5277+
{
5278+
List *inh;
5279+
ListCell *cell;
5280+
5281+
inh = find_all_inheritors(RelationGetRelid(rel), lockmode, NULL);
5282+
/* first element is the parent rel; must ignore it */
5283+
for_each_cell(cell, inh, list_second_cell(inh))
5284+
{
5285+
Relationchildrel;
5286+
5287+
/* find_all_inheritors already got lock */
5288+
childrel = table_open(lfirst_oid(cell), NoLock);
5289+
CheckTableNotInUse(childrel, "ALTER TABLE");
5290+
table_close(childrel, NoLock);
5291+
}
5292+
list_free(inh);
5293+
}
5294+
}
5295+
52625296
/*
52635297
* ATTypedTableRecursion
52645298
*

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2379,3 +2379,18 @@ DROP SCHEMA fkpart7 CASCADE;
23792379
NOTICE: drop cascades to 2 other objects
23802380
DETAIL: drop cascades to table fkpart7.pkpart
23812381
drop cascades to table fkpart7.fk
2382+
-- ensure we check partitions are "not used" when dropping constraints
2383+
CREATE SCHEMA fkpart8
2384+
CREATE TABLE tbl1(f1 int PRIMARY KEY)
2385+
CREATE TABLE tbl2(f1 int REFERENCES tbl1 DEFERRABLE INITIALLY DEFERRED) PARTITION BY RANGE(f1)
2386+
CREATE TABLE tbl2_p1 PARTITION OF tbl2 FOR VALUES FROM (minvalue) TO (maxvalue);
2387+
INSERT INTO fkpart8.tbl1 VALUES(1);
2388+
BEGIN;
2389+
INSERT INTO fkpart8.tbl2 VALUES(1);
2390+
ALTER TABLE fkpart8.tbl2 DROP CONSTRAINT tbl2_f1_fkey;
2391+
ERROR: cannot ALTER TABLE "tbl2_p1" because it has pending trigger events
2392+
COMMIT;
2393+
DROP SCHEMA fkpart8 CASCADE;
2394+
NOTICE: drop cascades to 2 other objects
2395+
DETAIL: drop cascades to table fkpart8.tbl1
2396+
drop cascades to table fkpart8.tbl2

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1667,3 +1667,15 @@ ALTER TABLE fkpart7.pkpart1 ADD PRIMARY KEY (a);
16671667
ALTERTABLEfkpart7.pkpart ADDPRIMARY KEY (a);
16681668
CREATETABLEfkpart7.fk (aintREFERENCESfkpart7.pkpart);
16691669
DROPSCHEMA fkpart7 CASCADE;
1670+
1671+
-- ensure we check partitions are "not used" when dropping constraints
1672+
CREATESCHEMAfkpart8
1673+
CREATETABLEtbl1(f1intPRIMARY KEY)
1674+
CREATETABLEtbl2(f1intREFERENCES tbl1 DEFERRABLE INITIALLY DEFERRED) PARTITION BY RANGE(f1)
1675+
CREATETABLEtbl2_p1 PARTITION OF tbl2 FORVALUESFROM (minvalue) TO (maxvalue);
1676+
INSERT INTOfkpart8.tbl1VALUES(1);
1677+
BEGIN;
1678+
INSERT INTOfkpart8.tbl2VALUES(1);
1679+
ALTERTABLEfkpart8.tbl2 DROPCONSTRAINT tbl2_f1_fkey;
1680+
COMMIT;
1681+
DROPSCHEMA fkpart8 CASCADE;

0 commit comments

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