On Tue, Aug 25, 2026 at 2:57 AM Alberto Piai <[email protected]> wrote: > > What do you think? > > As a user, I would find it very surprising if ALTER TABLE ONLY ... SET > EXPRESSION failed sometimes, depending on how dependencies are set up. > It would be great if we'd manage to get this working. >
The previous patch v3 is incorrect. ATExecCmd->ATExecSetExpression. In ATRewriteCatalogs, we have ``` foreach(lcmd, subcmds) ATExecCmd(wqueue, tab, lfirst_node(AlterTableCmd, lcmd), lockmode, pass, context); /* * After the ALTER TYPE or SET EXPRESSION pass, do cleanup work * (this is not done in ATExecAlterColumnType since it should be * done only once if multiple columns of a table are altered). */ if (pass == AT_PASS_ALTER_TYPE || pass == AT_PASS_SET_EXPRESSION) ATPostAlterTypeCleanup(wqueue, tab, lockmode); ````` Consider ALTER TABLE on a partitioned table with SET EXPRESSION. It will drop and recreate the dependent objects (such as indexes and constraints) of the partitioned table first, and then process each partition. If we call RememberAllDependentForRebuilding and RememberWholeRowDependentForRebuilding only in Phase 1 (ATPrepCmd) for each partition, the dependent objects of the partitions may already have been dropped as part of the cascading drop of the corresponding dependent objects on the partitioned table in ATPostAlterTypeCleanup. As a result, by the time we reach ATPostAlterTypeCleanup for the partition, AlteredTableInfo->changedConstraintOids and AlteredTableInfo->changedIndexOids may refer to dropped objects. Therefore, RememberAllDependentForRebuilding and RememberWholeRowDependentForRebuilding need to remain within ATExecSetExpression. But we want ALTER TABLE partitionX SET EXPRESSION to succeed when partitionX itself does not have any index dependencies or other dependencies. (partitionX is a partition, not a partitioned table). So, during Phase 1 (ATPrepCmd), call RememberAllDependentForRebuilding and RememberWholeRowDependentForRebuilding to detect unsupported dependencies and report error if necessary. After the check, reset the corresponding lists to NIL so that the dependencies are not actually queued for rebuilding. In v4-0001-Fix-dependency-issue-when-directly-ALTER-TABLE-SET-EXPRESSION-o.nocfbot We transform tab->changedConstraintOids to tab->constraints and delete unncessary tab->changedConstraintOids logic inside ATPostAlterTypeCleanup, seems more intuitive IMHO. In v4-0001-Disallow-directly-ALTER-TABLE-SET-EXPRESSION-on-child-table-if-de.patch Disallow directly applying ALTER TABLE SET EXPRESSION on a child table if dependencies exist; Disallow ALTER TABLE ONLY SET EXPRESSION on a table if dependencies exist; -- jian https://www.enterprisedb.com/
v4-0001-Fix-dependency-issue-when-directly-ALTER-TABLE-SET-EXPRESSION-o.nocfbot
Description: Binary data
From b8a462e61b64491c23bab424cec0cbbba43b4d75 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Tue, 25 Aug 2026 23:06:36 +0800 Subject: [PATCH v4 1/1] Disallow directly ALTER TABLE SET EXPRESSION on child table if dependency exists 1. Directly dropping and recreating an index on the child table will fail if the index is part of an index hierarchy. 2. Directly dropping and recreating a constraint on the child table will silently drop the constraint without recreating it. This is because the existing constraint is not local to the partition, so ALTER TABLE ADD CONSTRAINT command will not issued. See ATPostAlterTypeCleanup for details. ALTER TABLE ONLY ... SET EXPRESSION should fail if the generated column has dependencies, because rebuilding dependent objects would violate the semantics of ONLY. commitfest: https://commitfest.postgresql.org/patch/7117 discussion: https://postgr.es/m/CACJufxEomSz3BwUGfk8A6MeAKj=Cki5B+gYTbJO=accjyyt...@mail.gmail.com --- src/backend/commands/tablecmds.c | 69 +++++++++++++++++++ .../regress/expected/generated_stored.out | 42 +++++++++++ .../regress/expected/generated_virtual.out | 42 +++++++++++ src/test/regress/sql/generated_stored.sql | 30 ++++++++ src/test/regress/sql/generated_virtual.sql | 30 ++++++++ 5 files changed, 213 insertions(+) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 9b911310f05..cb57190129f 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -795,6 +795,9 @@ static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, static List *collectPartitionIndexExtDeps(List *partitionOids); static void applyPartitionIndexExtDeps(Oid newPartOid, List *extDepState); static void freePartitionIndexExtDeps(List *extDepState); +static void ATPrepSetExpression(List **wqueue, AlteredTableInfo *tab, Relation rel, + AlterTableCmd *cmd, bool recurse, bool recursing, + LOCKMODE lockmode); /* ---------------------------------------------------------------- * DefineRelation @@ -5134,6 +5137,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_FOREIGN_TABLE); ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); + ATPrepSetExpression(wqueue, tab, rel, cmd, recurse, recursing, lockmode); pass = AT_PASS_SET_EXPRESSION; break; case AT_DropExpression: /* ALTER COLUMN DROP EXPRESSION */ @@ -8897,6 +8901,71 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, return address; } +/* + * ALTER TABLE ALTER COLUMN SET EXPRESSION + */ +static void +ATPrepSetExpression(List **wqueue, AlteredTableInfo *tab, Relation rel, + AlterTableCmd *cmd, bool recurse, bool recursing, + LOCKMODE lockmode) +{ + char *colName = cmd->name; + Form_pg_attribute attTup; + AttrNumber attnum; + HeapTuple tuple; + + tuple = SearchSysCacheAttName(RelationGetRelid(rel), + colName); + if (!HeapTupleIsValid(tuple)) + { + /* + * If a newly added generated column has its generation expression set + * in the same command, the column has not been installed yet, so the + * attribute lookup will return false. + */ + return; + } + attTup = (Form_pg_attribute) GETSTRUCT(tuple); + attnum = attTup->attnum; + + ReleaseSysCache(tuple); + + if (recursing) + return; + + RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName); + + RememberWholeRowDependentForRebuilding(tab, AT_SetExpression, rel); + + if (!recurse && !recursing) + { + /* + * Cannot use ONLY to modify a generated column's expression when + * there are dependencies (e.g. constraints or indexes) that need to + * be rebuilt. For a parent table, the rebuild must cascade to its + * child partitions. For a child table, dropping and recreating its + * dependencies while preserving the partition constraint hierarchy is + * not trivial. + */ + if (tab->changedConstraintOids != NIL || tab->changedIndexOids != NIL) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("ALTER TABLE ONLY ... SET EXPRESSION is not supported for generated columns with dependent objects on a parent table"), + errdetail("Dependent objects, such as constraints and indexes, must be rebuilt in child tables as well, which conflicts with ONLY.")); + } + else if (!recursing && has_superclass(RelationGetRelid(rel))) + { + if (tab->changedConstraintOids != NIL || tab->changedIndexOids != NIL) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects"), + errdetail("Inherited objects, such as indexes, cannot be rebuilt independently for a child table.")); + + } + tab->changedConstraintOids = NIL; + tab->changedIndexOids = NIL; +} + /* * ALTER TABLE ALTER COLUMN DROP EXPRESSION */ diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out index fd6caf1cf2d..35e8685726f 100644 --- a/src/test/regress/expected/generated_stored.out +++ b/src/test/regress/expected/generated_stored.out @@ -1683,6 +1683,48 @@ CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED); c | integer | | | x | integer | | | generated always as (b * 2) stored +-- Test table partitioning, change child table generation expression +CREATE TABLE gtest_pk AS SELECT g::int as b FROM generate_series(1, 100) g; +ALTER TABLE gtest_pk ADD PRIMARY KEY (b); +CREATE TABLE gtest35(a int, b int GENERATED ALWAYS AS (a) STORED NOT NULL, CONSTRAINT cc check (b < 120)) PARTITION BY RANGE (a); +CREATE TABLE gtest35_1 PARTITION OF gtest35 FOR VALUES FROM (1) TO (10); +CREATE TABLE gtest35_2 PARTITION OF gtest35 FOR VALUES FROM (10) TO (20); +CREATE INDEX gtest35_b_idx ON gtest35(b); +INSERT INTO gtest35 SELECT g FROM generate_series(5, 14) g; +ALTER TABLE gtest35 ADD CONSTRAINT gtest35_fk FOREIGN KEY (b) REFERENCES gtest_pk; +ALTER TABLE ONLY gtest35 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ERROR: ALTER TABLE ONLY ... SET EXPRESSION is not supported for generated columns with dependent objects on a parent table +DETAIL: Dependent objects, such as constraints and indexes, must be rebuilt in child tables as well, which conflicts with ONLY. +ALTER TABLE ONLY gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ERROR: ALTER TABLE ONLY ... SET EXPRESSION is not supported for generated columns with dependent objects on a parent table +DETAIL: Dependent objects, such as constraints and indexes, must be rebuilt in child tables as well, which conflicts with ONLY. +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +ERROR: cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects +DETAIL: Inherited objects, such as indexes, cannot be rebuilt independently for a child table. +DROP INDEX gtest35_b_idx; +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (case when a=7 then 200 else a + 112 end); -- error +ERROR: cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects +DETAIL: Inherited objects, such as indexes, cannot be rebuilt independently for a child table. +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 200 else a + 2 end); -- error +ERROR: cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects +DETAIL: Inherited objects, such as indexes, cannot be rebuilt independently for a child table. +-- Test inheritance, change child table generation expression +CREATE TABLE gtest36(a int, b int GENERATED ALWAYS AS (a) STORED NOT NULL, CONSTRAINT cc check (b < 120)); +CREATE TABLE gtest36_1 () INHERITS (gtest36); +CREATE TABLE gtest36_12 () INHERITS (gtest36, gtest36_1); +NOTICE: merging multiple inherited definitions of column "a" +NOTICE: merging multiple inherited definitions of column "b" +INSERT INTO gtest36 VALUES (8), (9); +INSERT INTO gtest36_1 VALUES (10), (11); +INSERT INTO gtest36_12 VALUES (12), (13); +CREATE INDEX gtest36_b_idx ON gtest36(b); +ALTER TABLE gtest36 ADD CONSTRAINT gtest36_fk FOREIGN KEY (b) REFERENCES gtest_pk; +ALTER TABLE ONLY gtest36 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ERROR: ALTER TABLE ONLY ... SET EXPRESSION is not supported for generated columns with dependent objects on a parent table +DETAIL: Dependent objects, such as constraints and indexes, must be rebuilt in child tables as well, which conflicts with ONLY. +ALTER TABLE gtest36_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +ERROR: cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects +DETAIL: Inherited objects, such as indexes, cannot be rebuilt independently for a child table. -- rule actions referring to generated columns: -- NEW.b in a rule action should reflect the generated column's new value CREATE TABLE gtest_rule (a int, b int GENERATED ALWAYS AS (a * 2) STORED); diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index 6ee029796f1..be8d8b9356d 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -1577,6 +1577,48 @@ CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED); c | integer | | | x | integer | | | generated always as (b * 2) +-- Test table partitioning, change child table generation expression +CREATE TABLE gtest_pk AS SELECT g::int as b FROM generate_series(1, 100) g; +ALTER TABLE gtest_pk ADD PRIMARY KEY (b); +CREATE TABLE gtest35(a int, b int GENERATED ALWAYS AS (a) STORED NOT NULL, CONSTRAINT cc check (b < 120)) PARTITION BY RANGE (a); +CREATE TABLE gtest35_1 PARTITION OF gtest35 FOR VALUES FROM (1) TO (10); +CREATE TABLE gtest35_2 PARTITION OF gtest35 FOR VALUES FROM (10) TO (20); +-- CREATE INDEX gtest35_b_idx ON gtest35(b); +INSERT INTO gtest35 SELECT g FROM generate_series(5, 14) g; +-- ALTER TABLE gtest35 ADD CONSTRAINT gtest35_fk FOREIGN KEY (b) REFERENCES gtest_pk; +ALTER TABLE ONLY gtest35 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ERROR: ALTER TABLE ONLY ... SET EXPRESSION is not supported for generated columns with dependent objects on a parent table +DETAIL: Dependent objects, such as constraints and indexes, must be rebuilt in child tables as well, which conflicts with ONLY. +ALTER TABLE ONLY gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +ERROR: ALTER TABLE ONLY ... SET EXPRESSION is not supported for generated columns with dependent objects on a parent table +DETAIL: Dependent objects, such as constraints and indexes, must be rebuilt in child tables as well, which conflicts with ONLY. +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +ERROR: cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects +DETAIL: Inherited objects, such as indexes, cannot be rebuilt independently for a child table. +-- DROP INDEX gtest35_b_idx; +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (case when a=7 then 200 else a + 112 end); -- error +ERROR: cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects +DETAIL: Inherited objects, such as indexes, cannot be rebuilt independently for a child table. +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 200 else a + 2 end); -- error +ERROR: cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects +DETAIL: Inherited objects, such as indexes, cannot be rebuilt independently for a child table. +-- Test inheritance, change child table generation expression +CREATE TABLE gtest36(a int, b int GENERATED ALWAYS AS (a) STORED NOT NULL, CONSTRAINT cc check (b < 120)); +CREATE TABLE gtest36_1 () INHERITS (gtest36); +CREATE TABLE gtest36_12 () INHERITS (gtest36, gtest36_1); +NOTICE: merging multiple inherited definitions of column "a" +NOTICE: merging multiple inherited definitions of column "b" +INSERT INTO gtest36 VALUES (8), (9); +INSERT INTO gtest36_1 VALUES (10), (11); +INSERT INTO gtest36_12 VALUES (12), (13); +CREATE INDEX gtest36_b_idx ON gtest36(b); +ALTER TABLE gtest36 ADD CONSTRAINT gtest36_fk FOREIGN KEY (b) REFERENCES gtest_pk; +ALTER TABLE ONLY gtest36 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ERROR: ALTER TABLE ONLY ... SET EXPRESSION is not supported for generated columns with dependent objects on a parent table +DETAIL: Dependent objects, such as constraints and indexes, must be rebuilt in child tables as well, which conflicts with ONLY. +ALTER TABLE gtest36_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +ERROR: cannot use ALTER TABLE ... SET EXPRESSION on a generated column in a child table with dependent objects +DETAIL: Inherited objects, such as indexes, cannot be rebuilt independently for a child table. -- rule actions referring to generated columns: -- NEW.b in a rule action should reflect the generated column's new value CREATE TABLE gtest_rule (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql index 235bc28db81..054ce6e5c69 100644 --- a/src/test/regress/sql/generated_stored.sql +++ b/src/test/regress/sql/generated_stored.sql @@ -848,6 +848,36 @@ ALTER TABLE gtest28a DROP COLUMN a; CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED); \d gtest28* +-- Test table partitioning, change child table generation expression +CREATE TABLE gtest_pk AS SELECT g::int as b FROM generate_series(1, 100) g; +ALTER TABLE gtest_pk ADD PRIMARY KEY (b); +CREATE TABLE gtest35(a int, b int GENERATED ALWAYS AS (a) STORED NOT NULL, CONSTRAINT cc check (b < 120)) PARTITION BY RANGE (a); +CREATE TABLE gtest35_1 PARTITION OF gtest35 FOR VALUES FROM (1) TO (10); +CREATE TABLE gtest35_2 PARTITION OF gtest35 FOR VALUES FROM (10) TO (20); +CREATE INDEX gtest35_b_idx ON gtest35(b); +INSERT INTO gtest35 SELECT g FROM generate_series(5, 14) g; +ALTER TABLE gtest35 ADD CONSTRAINT gtest35_fk FOREIGN KEY (b) REFERENCES gtest_pk; + +ALTER TABLE ONLY gtest35 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ALTER TABLE ONLY gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +DROP INDEX gtest35_b_idx; + +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (case when a=7 then 200 else a + 112 end); -- error +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 200 else a + 2 end); -- error + +-- Test inheritance, change child table generation expression +CREATE TABLE gtest36(a int, b int GENERATED ALWAYS AS (a) STORED NOT NULL, CONSTRAINT cc check (b < 120)); +CREATE TABLE gtest36_1 () INHERITS (gtest36); +CREATE TABLE gtest36_12 () INHERITS (gtest36, gtest36_1); +INSERT INTO gtest36 VALUES (8), (9); +INSERT INTO gtest36_1 VALUES (10), (11); +INSERT INTO gtest36_12 VALUES (12), (13); +CREATE INDEX gtest36_b_idx ON gtest36(b); +ALTER TABLE gtest36 ADD CONSTRAINT gtest36_fk FOREIGN KEY (b) REFERENCES gtest_pk; + +ALTER TABLE ONLY gtest36 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ALTER TABLE gtest36_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error -- rule actions referring to generated columns: -- NEW.b in a rule action should reflect the generated column's new value diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index e4ea63bb3a1..2fd366df0b8 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -848,6 +848,36 @@ ALTER TABLE gtest28a DROP COLUMN a; CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED); \d gtest28* +-- Test table partitioning, change child table generation expression +CREATE TABLE gtest_pk AS SELECT g::int as b FROM generate_series(1, 100) g; +ALTER TABLE gtest_pk ADD PRIMARY KEY (b); +CREATE TABLE gtest35(a int, b int GENERATED ALWAYS AS (a) STORED NOT NULL, CONSTRAINT cc check (b < 120)) PARTITION BY RANGE (a); +CREATE TABLE gtest35_1 PARTITION OF gtest35 FOR VALUES FROM (1) TO (10); +CREATE TABLE gtest35_2 PARTITION OF gtest35 FOR VALUES FROM (10) TO (20); +-- CREATE INDEX gtest35_b_idx ON gtest35(b); +INSERT INTO gtest35 SELECT g FROM generate_series(5, 14) g; +-- ALTER TABLE gtest35 ADD CONSTRAINT gtest35_fk FOREIGN KEY (b) REFERENCES gtest_pk; + +ALTER TABLE ONLY gtest35 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ALTER TABLE ONLY gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error +-- DROP INDEX gtest35_b_idx; + +ALTER TABLE gtest35_1 ALTER COLUMN b SET EXPRESSION AS (case when a=7 then 200 else a + 112 end); -- error +ALTER TABLE gtest35_2 ALTER COLUMN b SET EXPRESSION AS (case when a=10 then 200 else a + 2 end); -- error + +-- Test inheritance, change child table generation expression +CREATE TABLE gtest36(a int, b int GENERATED ALWAYS AS (a) STORED NOT NULL, CONSTRAINT cc check (b < 120)); +CREATE TABLE gtest36_1 () INHERITS (gtest36); +CREATE TABLE gtest36_12 () INHERITS (gtest36, gtest36_1); +INSERT INTO gtest36 VALUES (8), (9); +INSERT INTO gtest36_1 VALUES (10), (11); +INSERT INTO gtest36_12 VALUES (12), (13); +CREATE INDEX gtest36_b_idx ON gtest36(b); +ALTER TABLE gtest36 ADD CONSTRAINT gtest36_fk FOREIGN KEY (b) REFERENCES gtest_pk; + +ALTER TABLE ONLY gtest36 ALTER COLUMN b SET EXPRESSION AS (a); -- error +ALTER TABLE gtest36_1 ALTER COLUMN b SET EXPRESSION AS (a+1); -- error -- rule actions referring to generated columns: -- NEW.b in a rule action should reflect the generated column's new value -- 2.34.1
