On 2019-Jul-17, Alvaro Herrera wrote:
> On 2019-Jul-17, Alvaro Herrera wrote:
> > I wonder if there are other AT subcommands that are similarly broken,
> > because many of them skip the CheckTableNotInUse for the partitions.
>
> I suppose the question here is where else do we need to call the new
> ATRecurseCheckNotInUse function (which needs a comment).
I decided to rename the new function to ATCheckPartitionsNotInUse, and
make it a no-op for legacy inheritance. This seems quite specific to
partitioned tables (as opposed to legacy inheritance behavior).
After looking at the code some more, I think calling the new function in
the Prep phase is correct. The attached patch is pretty much final form
for this bugfix. I decided to unwrap a couple of error messages (I did
get bitten while grepping because of this), and reordered one of the new
Identity command cases in ATPrepCmd since it appeared in inconsistent
order in that one place of four.
I looked at all the other AT subcommand cases that might require the
same treatment, and didn't find anything -- either the recursion is done
at Prep time, which checks already, or contains the proper check at Exec
time right after opening the partition rel. (I think it would be better
to do the check during the Prep phase, to avoid wasting work in case a
partition happens to be used. However, that's not critical and not for
this commit to fix IMO.)
Separately from that, there's AT_SetLogged / AT_SetUnlogged which look
pretty dubious ... I'm not sure that recursion is handled correctly
there. Maybe it's considered okay to have a partitioned table with
unlogged partitions, and vice versa?
I also noticed that AT_AlterConstraint does not handle recursion at all,
and it also has this comment:
* Currently only works for Foreign Key constraints.
* Foreign keys do not inherit, so we purposely ignore the
* recursion bit here, but we keep the API the same for when
* other constraint types are supported.
which sounds to oppose reality.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From ca7841a24c27012e351d08dc36039233ae0d80e1 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Mon, 22 Jul 2019 11:09:06 -0400
Subject: [PATCH v2] Check partitions not in use
---
src/backend/commands/tablecmds.c | 56 +++++++++++++++++++++++++-------
1 file changed, 45 insertions(+), 11 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f39f993e01..fd17aa4b5a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -354,6 +354,7 @@ static void ATSimplePermissions(Relation rel, int allowed_targets);
static void ATWrongRelkindError(Relation rel, int allowed_targets);
static void ATSimpleRecursion(List **wqueue, Relation rel,
AlterTableCmd *cmd, bool recurse, LOCKMODE lockmode);
+static void ATCheckPartitionsNotInUse(Relation rel, LOCKMODE lockmode);
static void ATTypedTableRecursion(List **wqueue, Relation rel, AlterTableCmd *cmd,
LOCKMODE lockmode);
static List *find_typed_table_dependencies(Oid typeOid, const char *typeName,
@@ -3421,8 +3422,7 @@ CheckTableNotInUse(Relation rel, const char *stmt)
ereport(ERROR,
(errcode(ERRCODE_OBJECT_IN_USE),
/* translator: first %s is a SQL command, eg ALTER TABLE */
- errmsg("cannot %s \"%s\" because "
- "it is being used by active queries in this session",
+ errmsg("cannot %s \"%s\" because it is being used by active queries in this session",
stmt, RelationGetRelationName(rel))));
if (rel->rd_rel->relkind != RELKIND_INDEX &&
@@ -3431,8 +3431,7 @@ CheckTableNotInUse(Relation rel, const char *stmt)
ereport(ERROR,
(errcode(ERRCODE_OBJECT_IN_USE),
/* translator: first %s is a SQL command, eg ALTER TABLE */
- errmsg("cannot %s \"%s\" because "
- "it has pending trigger events",
+ errmsg("cannot %s \"%s\" because it has pending trigger events",
stmt, RelationGetRelationName(rel))));
}
@@ -3910,16 +3909,19 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
break;
case AT_AddIdentity:
ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
+ /* This command never recurses */
pass = AT_PASS_ADD_CONSTR;
break;
- case AT_DropIdentity:
- ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
- pass = AT_PASS_DROP;
- break;
case AT_SetIdentity:
ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
+ /* This command never recurses */
pass = AT_PASS_COL_ATTRS;
break;
+ case AT_DropIdentity:
+ ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
+ /* This command never recurses */
+ pass = AT_PASS_DROP;
+ break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
ATPrepDropNotNull(rel, recurse, recursing);
@@ -3985,7 +3987,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
break;
case AT_DropConstraint: /* DROP CONSTRAINT */
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
- /* Recursion occurs during execution phase */
+ ATCheckPartitionsNotInUse(rel, lockmode);
+ /* Other recursion occurs during execution phase */
/* No command-specific prep needed except saving recurse flag */
if (recurse)
cmd->subtype = AT_DropConstraintRecurse;
@@ -5224,8 +5227,9 @@ ATSimpleRecursion(List **wqueue, Relation rel,
AlterTableCmd *cmd, bool recurse, LOCKMODE lockmode)
{
/*
- * Propagate to children if desired. Only plain tables and foreign tables
- * have children, so no need to search for other relkinds.
+ * Propagate to children if desired. Only plain tables, foreign tables
+ * and partitioned tables have children, so no need to search for other
+ * relkinds.
*/
if (recurse &&
(rel->rd_rel->relkind == RELKIND_RELATION ||
@@ -5259,6 +5263,36 @@ ATSimpleRecursion(List **wqueue, Relation rel,
}
}
+/*
+ * Obtain list of partitions of the given table, locking them all at the given
+ * lockmode and ensuring that they all pass CheckTableNotInUse.
+ *
+ * This function is a no-op if the given relation is not a partitioned table;
+ * in particular, nothing is done if it's a legacy inheritance parent.
+ */
+static void
+ATCheckPartitionsNotInUse(Relation rel, LOCKMODE lockmode)
+{
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ List *inh;
+ ListCell *cell;
+
+ inh = find_all_inheritors(RelationGetRelid(rel), lockmode, NULL);
+ /* first element is the parent rel; must ignore it */
+ for_each_cell(cell, inh, list_second_cell(inh))
+ {
+ Relation childrel;
+
+ /* find_all_inheritors already got lock */
+ childrel = table_open(lfirst_oid(cell), NoLock);
+ CheckTableNotInUse(childrel, "ALTER TABLE");
+ table_close(childrel, NoLock);
+ }
+ list_free(inh);
+ }
+}
+
/*
* ATTypedTableRecursion
*
--
2.17.1
