Subject: Re: support ALTER COLUMN SET EXPRESSION over virtual generated column with check constraint

From: Matheus Alcantara <[email-masked]>
Date: Wed, 14 Jan 2026 18:01:58 -0300

Hi,

On 06/07/25 23:24, jian he wrote:
> hi.
> rebased only.

+--test no table rewrite happen
+ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 4), ADD 
COLUMN C int default 11;
+SELECT  pa.attnum,pa.attname,attmissingval
+FROM    pg_attribute pa
+JOIN    pg_attrdef patt ON pa.attrelid = patt.adrelid AND pa.attnum = 
patt.adnum
+WHERE   pa.attrelid = 'gtest20'::regclass

Not sure if I understand the goal of this query. Is to check if the 
table was not rewritten after an update on the virtual generated 
column? If that's the case, I've tested this query by updating the 
expression from a STORED generated column and it return the same 
results, before and after the alter table, so I'm not sure if it's the 
best way to test this, or I'm missing something?

Perhaps we could use pg_relation_filenode() and use \gset to store the 
value on a variable before the ALTER TABLE execution and check if the 
value is the same after the ALTER TABLE SET EXPRESSION is executed. 
Please see the attached diff for reference.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 24fa3ca8cc7..a04e27230c3 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -643,17 +643,13 @@ ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100);  -- violates con
 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
 --test no table rewrite 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  pa.attnum,pa.attname,attmissingval
-FROM    pg_attribute pa
-JOIN    pg_attrdef patt ON pa.attrelid = patt.adrelid AND pa.attnum = patt.adnum
-WHERE   pa.attrelid = 'gtest20'::regclass
-ORDER BY pa.attnum;
- attnum | attname | attmissingval 
---------+---------+---------------
-      2 | b       | 
-      3 | c       | {11}
-(2 rows)
+SELECT pg_relation_filenode('gtest20') = :gtest20_filenode AS is_same_file;
+ is_same_file 
+--------------
+ t
+(1 row)
 
 CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
 INSERT INTO gtest20a (a) VALUES (10);
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 10e0e65c590..765006af23c 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -320,12 +320,9 @@ INSERT INTO gtest20 (a) VALUES (30);  -- violates constraint
 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
 --test no table rewrite 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  pa.attnum,pa.attname,attmissingval
-FROM    pg_attribute pa
-JOIN    pg_attrdef patt ON pa.attrelid = patt.adrelid AND pa.attnum = patt.adnum
-WHERE   pa.attrelid = 'gtest20'::regclass
-ORDER BY pa.attnum;
+SELECT pg_relation_filenode('gtest20') = :gtest20_filenode AS is_same_file;
 
 CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
 INSERT INTO gtest20a (a) VALUES (10);

---

From: jian he <[email-masked]>
Date: Thu, 15 Jan 2026 12:31:07 +0800

> Please see the attached diff for reference.

hi.
Your patch made the test more simple.
so i added a ``\d gtest20``

I aslo polished the commit message.

--
jian
https://www.enterprisedb.com/
From a180ad0fbb7aec84d18b5da8db9e42ff020ab3b4 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Thu, 15 Jan 2026 12:22:48 +0800
Subject: [PATCH v3 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 Phase 3 execution.

We also verify that ALTER COLUMN SET EXPRESSION for virtual columns does not
trigger an unnecessary table rewrite.

discussion: https://postgr.es/m/[email-masked]
commitfest: https://commitfest.postgresql.org/patch/5645
---
 src/backend/commands/tablecmds.c              | 26 ++++---------
 .../regress/expected/generated_virtual.out    | 39 ++++++++++++++++---
 src/test/regress/sql/generated_virtual.sql    | 16 +++++++-
 3 files changed, 54 insertions(+), 27 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f976c0e5c7e..933ef1b034b 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,15 @@ 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 after
+	 * rewrite.
+	 */
+	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

---

From: "Matheus Alcantara" <[email-masked]>
Date: Thu, 15 Jan 2026 12:29:28 -0300

> I aslo polished the commit message.
Thanks for the new version. The commit message seems better. Just a few
comments:

+	/*
+	 * 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);
+
Perhaps this comments should be updated since we are now collecting
these dependencies for virtual generated columns too that it not require
a table rewrite.

---

I think that it would be good to update the SET EXPRESSION AS
documentation on doc/src/sgml/ref/alter_table.sgml to mention that for
virtual columns the table is not rewritten but a full table scan may
still be needed if the column has check constraints.

---

From: jian he <[email-masked]>
Date: Fri, 16 Jan 2026 19:31:35 +0800

> these dependencies for virtual generated columns too that it not require

I guess we can change it as:

    /*
     * 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);

> still be needed if the column has check constraints.

In doc/src/sgml/ref/alter_table.sgml, I intended to change it as follows:

   <varlistentry id="sql-altertable-desc-set-expression">
    <term><literal>SET EXPRESSION AS</literal></term>
    <listitem>
     <para>
      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.
+      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.
     </para>

---

From: Matheus Alcantara <[email-masked]>
Date: Fri, 16 Jan 2026 13:07:25 -0300

>      RememberAllDependentForRebuilding(tab, AT_SetExpression, rel,
It looks good.

> +      ensure that existing rows meet the constraint.

Sounds good.

---

From: jian he <[email-masked]>
Date: Tue, 20 Jan 2026 00:11:48 +0800

hi.

I ultimately settled on the following:
+      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.

--
jian
https://www.enterprisedb.com
From 02afedea5fb9c345a71faf519ce297092284dea4 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Tue, 20 Jan 2026 00:03:30 +0800
Subject: [PATCH v4 1/1] Allow ALTER COLUMN SET EXPRESSION on virtual columns
 with CHECK constraints

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-masked]>
Reviewed-by: Matheus Alcantara <[email-masked]>
discussion: https://postgr.es/m/[email-masked]
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..4f696439256 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 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.
      </para>
 
      <para>
index f976c0e5c7e..08888196280 100644
 
-	/*
 
@@ -8709,15 +8697,14 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,

+	/*
+	 * and record enough information to let us recreate the objects.

---

From: "Matheus Alcantara" <[email-masked]>
Date: Tue, 20 Jan 2026 15:48:11 -0300

> +      scan to ensure that existing rows meet the constraint.
Thanks for the new version. Just a minor comment

I think that it sounds better with: "Replacing the expression of a
virtual generated column does not require a table rewrite..."

---

From: jian he <[email-masked]>
Date: Wed, 21 Jan 2026 13:10:20 +0800

On Wed, Jan 21, 2026 at 2:48 AM Matheus Alcantara
<[email-masked]> wrote:

> virtual generated column does not require a table rewrite..."

sure. please check the attached.

--
jian
https://www.enterprisedb.com
From e75e2d5c2aed11f209d8b000f174c550d4d4cced Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
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

index 1bd479c917a..17ab7b69d79 100644
+      Replacing the expression of a virtual generated column do not require a
 
      <para>
 
-	/*

 
+	/*

---

From: Matheus Alcantara <[email-masked]>
Date: Wed, 21 Jan 2026 16:26:14 -0300

> sure. please check the attached.

Thanks. The patch is in a good shape for me, I don't have any other 
comments. Perhaps we could move the CF status for "Ready for Committer"?