Re: Fix ALTER TABLE DROP EXPRESSION with inheritance hierarchy - Mailing list pgsql-hackers

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