Horton

Thread

Commits

  1. Allow ALTER COLUMN SET EXPRESSION on virtual columns with CHECK constraints

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

    jian he <jian.universality@gmail.com> — 2025-03-11T04:17:36Z

    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.

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

    jian he <jian.universality@gmail.com> — 2025-04-24T02:42:31Z

    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.

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

    jian he <jian.universality@gmail.com> — 2025-07-07T02:24:13Z

    hi.
rebased only.

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

    Matheus Alcantara <matheusssilv97@gmail.com> — 2026-01-14T21:01:58Z

    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

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

    jian he <jian.universality@gmail.com> — 2026-01-15T04:31:07Z

    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/

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

    Matheus Alcantara <matheusssilv97@gmail.com> — 2026-01-15T15:29:28Z

    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

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

    jian he <jian.universality@gmail.com> — 2026-01-16T11:31:35Z

    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>

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

    Matheus Alcantara <matheusssilv97@gmail.com> — 2026-01-16T16:07:25Z

    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

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

    jian he <jian.universality@gmail.com> — 2026-01-19T16:11:48Z

    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

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

    Matheus Alcantara <matheusssilv97@gmail.com> — 2026-01-20T18:48:11Z

    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

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

    jian he <jian.universality@gmail.com> — 2026-01-21T05:10:20Z

    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

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

    Matheus Alcantara <matheusssilv97@gmail.com> — 2026-01-21T19:26:14Z

    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

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

    Peter Eisentraut <peter@eisentraut.org> — 2026-02-24T09:44:21Z

    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.