Hi all,
I tried to fix a bug in PostgreSQL where ALTER TABLE ... DROP EXPRESSION
fails on multi-level inheritance hierarchies.
Bug:
When a parent table has a generated column and child/grandchild tables
inherit from it, executing:
ALTER TABLE parent ALTER COLUMN d DROP EXPRESSION;
ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too
Fix Details:
-
Updated file: src/backend/commands/tablecmds.c
-
Function modified: ATPrepDropExpression()
-
Change: Added !recursing check to ensure proper recursion across
inheritance.
if (!recurse && !recursing &&
find_inheritance_children(RelationGetRelid(rel), lockmode))
ereport(ERROR,
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("ALTER TABLE / DROP EXPRESSION must be applied to
child tables too"),
errhint("Do not specify the ONLY keyword."));
-
Test Query DROP TABLE IF EXISTS parent CASCADE;
CREATE TABLE parent (a int, d int GENERATED ALWAYS AS (11) STORED);
CREATE TABLE child () INHERITS (parent);
CREATE TABLE grandchild () INHERITS (child);
ALTER TABLE parent ALTER COLUMN d DROP EXPRESSION;
-
Output ALTER TABLE
On Thu, Aug 28, 2025 at 10:49 AM Kirill Reshke <[email protected]>
wrote:
> On Thu, 28 Aug 2025 at 08:35, jian he <[email protected]> wrote:
> >
> > That means, we don't need to change the ATPrepDropExpression function
> > argument for now?
>
> Sure. V3 with this attached, and I think we can move cf entry to RFC
>
> --
> Best regards,
> Kirill Reshke
>
From b28086bee3c7d49b3703cf164813f8cbc20d6938 Mon Sep 17 00:00:00 2001
From: Lakshmi <[email protected]>
Date: Wed, 22 Oct 2025 17:15:55 +0530
Subject: [PATCH] Fix ALTER TABLE DROP EXPRESSION for inheritance hierarchy
---
src/backend/commands/tablecmds.c | 12 ++++++------
1 file changed, 6 insertions(+), 6 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5fd8b51312c..0f0c104e238 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8732,12 +8732,12 @@ ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recurs
* tables, somewhat similar to how DROP COLUMN does it, so that the
* resulting state can be properly dumped and restored.
*/
- if (!recurse &&
- find_inheritance_children(RelationGetRelid(rel), lockmode))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("ALTER TABLE / DROP EXPRESSION must be applied to child tables too")));
-
+ if (!recurse && !recursing &&
+ find_inheritance_children(RelationGetRelid(rel), lockmode))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ALTER TABLE / DROP EXPRESSION must be applied to child tables too"),
+ errhint("Do not specify the ONLY keyword.")));
/*
* Cannot drop generation expression from inherited columns.
*/
--
2.39.5
