On Wed, Jan 21, 2026 at 2:48 AM Matheus Alcantara <[email protected]> wrote: >
> Thanks for the new version. Just a minor comment > > + Replacing the expression of a virtual generated columns do not require > a > + table rewrite, however if the column is part of a > <literal>CHECK</literal> > + constraint expression, the constraint will be rebuilt, requiring a > table > + scan to ensure that existing rows meet the constraint. > > I think that it sounds better with: "Replacing the expression of a > virtual generated column does not require a table rewrite..." > > -- > Matheus Alcantara > EDB: https://www.enterprisedb.com > sure. please check the attached. -- jian https://www.enterprisedb.com
From e75e2d5c2aed11f209d8b000f174c550d4d4cced Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Wed, 21 Jan 2026 13:06:46 +0800 Subject: [PATCH v5 1/1] Allow ALTER COLUMN SET EXPRESSION on virtual columns with CHECK constraints Previously, changing the generation expression of a virtual column was prohibited if the column was referenced by a CHECK constraint. This lifts that restriction. RememberAllDependentForRebuilding within ATExecSetExpression will rebuild all the dependent constraints, later ATPostAlterTypeCleanup queues the required AlterTableStmt operations for ALTER TABLE Phase 3 execution. Overall, ALTER COLUMN SET EXPRESSION on virtual columns may require scanning the table to re-verify any associated CHECK constraints, but it does not require a table rewrite in ALTER TABLE Phase 3. Author: jian he <[email protected]> Reviewed-by: Matheus Alcantara <[email protected]> discussion: https://postgr.es/m/cacjufxh3vetr7orf5rw29gndk3n1wwboe3wdkhyd3ipgrq9...@mail.gmail.com commitfest: https://commitfest.postgresql.org/patch/5645 --- doc/src/sgml/ref/alter_table.sgml | 4 ++ src/backend/commands/tablecmds.c | 25 +++--------- .../regress/expected/generated_virtual.out | 39 ++++++++++++++++--- src/test/regress/sql/generated_virtual.sql | 16 +++++++- 4 files changed, 57 insertions(+), 27 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 1bd479c917a..17ab7b69d79 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -278,6 +278,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM This form replaces the expression of a generated column. Existing data in a stored generated column is rewritten and all the future changes will apply the new generation expression. + Replacing the expression of a virtual generated column do not require a + table rewrite, however if the column is part of a <literal>CHECK</literal> + constraint expression, the constraint will be rebuilt, requiring a table + scan to ensure that existing rows meet the constraint. </para> <para> diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index f976c0e5c7e..08888196280 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -8665,18 +8665,6 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, errmsg("column \"%s\" of relation \"%s\" is not a generated column", colName, RelationGetRelationName(rel)))); - /* - * TODO: This could be done, just need to recheck any constraints - * afterwards. - */ - if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL && - rel->rd_att->constr && rel->rd_att->constr->num_check > 0) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables with check constraints"), - errdetail("Column \"%s\" of relation \"%s\" is a virtual generated column.", - colName, RelationGetRelationName(rel)))); - if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL && attTup->attnotnull) tab->verify_new_notnull = true; @@ -8709,15 +8697,14 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, /* make sure we don't conflict with later attribute modifications */ CommandCounterIncrement(); - - /* - * Find everything that depends on the column (constraints, indexes, - * etc), and record enough information to let us recreate the objects - * after rewrite. - */ - RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName); } + /* + * Find everything that depends on the column (constraints, indexes, etc), + * and record enough information to let us recreate the objects. + */ + RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName); + /* * Drop the dependency records of the GENERATED expression, in particular * its INTERNAL dependency on the column, which would otherwise cause diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index 249e68be654..ea32dd8d164 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -639,12 +639,30 @@ INSERT INTO gtest20 (a) VALUES (10); -- ok INSERT INTO gtest20 (a) VALUES (30); -- violates constraint ERROR: new row for relation "gtest20" violates check constraint "gtest20_b_check" DETAIL: Failing row contains (30, virtual). -ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint (currently not supported) -ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables with check constraints -DETAIL: Column "b" of relation "gtest20" is a virtual generated column. -ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok (currently not supported) -ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables with check constraints -DETAIL: Column "b" of relation "gtest20" is a virtual generated column. +ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint +ERROR: check constraint "gtest20_b_check" of relation "gtest20" is violated by some row +ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok +--table rewrite should not happen +SELECT pg_relation_filenode('gtest20') AS gtest20_filenode \gset +ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 4), ADD COLUMN c INT DEFAULT 11; +SELECT pg_relation_filenode('gtest20') = :gtest20_filenode AS is_same_file; + is_same_file +-------------- + t +(1 row) + +\d gtest20 + Table "generated_virtual_tests.gtest20" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+----------------------------- + a | integer | | not null | + b | integer | | | generated always as (a * 4) + c | integer | | | 11 +Indexes: + "gtest20_pkey" PRIMARY KEY, btree (a) +Check constraints: + "gtest20_b_check" CHECK (b < 50) + CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); INSERT INTO gtest20a (a) VALUES (10); INSERT INTO gtest20a (a) VALUES (30); @@ -988,6 +1006,15 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; gtest_child3 | 09-13-2016 | 1 | 4 (3 rows) +--check constraint was validated based on each partitions's generation expression +ALTER TABLE gtest_parent ADD CONSTRAINT cc1 CHECK (f3 < 19); --error +ERROR: check constraint "cc1" of relation "gtest_child" is violated by some row +ALTER TABLE gtest_parent ADD CONSTRAINT cc1 CHECK (f3 < 66); --error +ERROR: check constraint "cc1" of relation "gtest_child2" is violated by some row +ALTER TABLE gtest_parent ADD CONSTRAINT cc1 CHECK (f3 <> 33); --error +ERROR: check constraint "cc1" of relation "gtest_child3" is violated by some row +ALTER TABLE gtest_parent ADD CONSTRAINT cc CHECK (f3 < 67); --ok +ALTER TABLE gtest_parent DROP CONSTRAINT cc; -- alter generation expression of parent and all its children altogether ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2); \d gtest_parent diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index 81152b39a79..3e82e105d25 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -317,8 +317,13 @@ CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTU INSERT INTO gtest20 (a) VALUES (10); -- ok INSERT INTO gtest20 (a) VALUES (30); -- violates constraint -ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint (currently not supported) -ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok (currently not supported) +ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint +ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok +--table rewrite should not happen +SELECT pg_relation_filenode('gtest20') AS gtest20_filenode \gset +ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 4), ADD COLUMN c INT DEFAULT 11; +SELECT pg_relation_filenode('gtest20') = :gtest20_filenode AS is_same_file; +\d gtest20 CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); INSERT INTO gtest20a (a) VALUES (10); @@ -536,6 +541,13 @@ ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10); \d gtest_child3 SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3; +--check constraint was validated based on each partitions's generation expression +ALTER TABLE gtest_parent ADD CONSTRAINT cc1 CHECK (f3 < 19); --error +ALTER TABLE gtest_parent ADD CONSTRAINT cc1 CHECK (f3 < 66); --error +ALTER TABLE gtest_parent ADD CONSTRAINT cc1 CHECK (f3 <> 33); --error +ALTER TABLE gtest_parent ADD CONSTRAINT cc CHECK (f3 < 67); --ok +ALTER TABLE gtest_parent DROP CONSTRAINT cc; + -- alter generation expression of parent and all its children altogether ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2); \d gtest_parent -- 2.34.1
