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

On Tue, Mar 11, 2025 at 12:17 PM jian he <jian.universality@gmail.com> wrote:

hi.
in summary: ATExecSetExpression, RememberAllDependentForRebuilding
will do all the work to change the generation expression,
whether it's virtual or stored.

while working on another patch, I found out this can be further simplified.
Thus a new patch is attached.

hi.
rebased only.

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

On Thu, Jan 15, 2026 at 5:02 AM Matheus Alcantara
<matheusssilv97@gmail.com> wrote:

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/

On Thu Jan 15, 2026 at 1:31 AM -03, jian he wrote:

On Thu, Jan 15, 2026 at 5:02 AM Matheus Alcantara
<matheusssilv97@gmail.com> wrote:

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.

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.

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

On Thu, Jan 15, 2026 at 11:29 PM Matheus Alcantara
<matheusssilv97@gmail.com> wrote:

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

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.

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

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>

On 16/01/26 08:31, jian he wrote:

On Thu, Jan 15, 2026 at 11:29 PM Matheus Alcantara
<matheusssilv97@gmail.com> wrote:

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

It looks good.

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.

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>

Sounds good.

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

On Sat, Jan 17, 2026 at 12:07 AM Matheus Alcantara
<matheusssilv97@gmail.com> wrote:

<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>

Sounds good.

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

On Mon Jan 19, 2026 at 1:11 PM -03, jian he wrote:

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.

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

On Wed, Jan 21, 2026 at 2:48 AM Matheus Alcantara
<matheusssilv97@gmail.com> 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

On 21/01/26 02:10, jian he wrote:

+      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..."

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"?

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

On 21.01.26 20:26, Matheus Alcantara wrote:

On 21/01/26 02:10, jian he wrote:

+      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..."

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"?

(As a matter of process, if you, as the reviewer, feel like you have
reviewed all you could, then it is appropriate for you to change the
status to "Ready for Committer".)

I have committed this.

I changed the wording in the documentation a bit, mentioning just
"constraints" in general, since this also applies to the existing
support for not-null constraints, and it would presumably apply to other
kinds of constraints as well, if we want to support those.