Обсуждение: support ALTER COLUMN SET EXPRESSION over virtual generated column with check constraint

hi.

in RememberAllDependentForRebuilding
while (HeapTupleIsValid(depTup = systable_getnext(scan)))
{
        if(subtype == AT_SetExpression)
            elog(INFO, "foundObject.classId:%d", foundObject.classId);
}
Then do the regress test on generated_stored.sql
I found out only constraints and indexes will be rebuilt
while we are doing ALTER TABLE ALTER COLUMN SET EXPRESSION.

we can also see RememberAllDependentForRebuilding handling of:
case RelationRelationId:
case AttrDefaultRelationId:
case ConstraintRelationId:

RememberAllDependentForRebuilding record
AlteredTableInfo->changedConstraintOids, AlteredTableInfo->changedIndexOids.
ATPostAlterTypeCleanup will construct steps to rebuild these
constraints over the generated column.
and if these constraints are successfully installed,
AlteredTableInfo->constraints will be populated.
then in phase3 ATRewriteTable will do the scan or rewrite.


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


we didn't support virtual generated columns over domain with check constraint.
we also didn't support index over virtual generated columns.
to support change generation expressions for virtual generated columns
over check constraints,
the code seems not hard.

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,

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