Re: Adding a stored generated column without long-lived locks

On Mon Jun 15, 2026 at 8:38 PM CEST, Laurenz Albe wrote:
> On Wed, 2026-05-27 at 19:43 +0200, Alberto Piai wrote:
>> Design iteration 2
>> ------------------
>
> I like this proposal. It avoids the question "to rewrite or not to
> rewrite" by just outright failing if there is no suitable constraint.
>
> The idea to avoid the problem with NULL by forcing IS NOT DISTINCT FROM
> in the constraint is a good solution. Perhaps you could also allow
> the equality operator if the column in question is defined NOT NULL.

In the attached v5 patch I've implemented this design, and went one step
further (let me know what you think). While discussing this with my
colleagues at work, the question came up (thanks, Philip!): now that we
mention the constraint explicitly, what's the point of repeating the
expression too? The constraint already defines an equality to an
expression. I think this is a very good point, and it removes one
further way in which the operation could fail, so I went ahead and
changed the command to not mention the expression. It takes the
expression defined in the constraint and uses _that_ as the generator
expression of the column.

Design iteration 3
------------------

Syntax:

ALTER TABLE t ALTER COLUMN c
ADD GENERATED ALWAYS STORED USING CONSTRAINT check_name

check_name must be a valid constraint of a specific shape. If the
column is nullable:

CHECK (c IS NOT DISTINCT FROM expr)

If the column is NOT NULL, either of the following is acceptable:

CHECK (c = expr)
CHECK (c IS NOT DISTINCT FROM expr)

The column is then changed to be a stored generated column, with the
"expr" from the constraint as its generator expression.

>> Any thoughts about this?
>
> Yes. I think that you should not drop the constraint. That's what I'd
> expect, similar to how we don't drop the check constraint that allows
> to skip the table scan in ALTER TABLE ... ALTER COLUMN ... SET NOT
> NULL or ALTER TABLE ... ATTACK PARTITION.
>
> I feel that automatically dropping the constraint is a bit too much
> black magic, but it is more a feeling than a conviction.

I don't have a strong opinion on whether to cleanup or not, I'll gladly
take your input. This version of the patch does not drop the constraint
anymore.

This version addresses your inputs from the last review:

- I added documentation for the new alter table form to alter_table.sgml
- Tab completion for psql is there now
- The missing error conditions in case of an identity column or
sequences are now handled, more about this in the next section.

Failure conditions
------------------

There's quite a few invalid states that cannot be reached via CREATE
TABLE and should not be reachable via ALTER TABLE either.

The following are detected and fail the operation:

- the column is already a generated column

- the column is an identity column

- the column is referenced by a sequence (it is most likely a serial
column)

- the column is referenced by another column's default expression

- the column references another generated column

- the column is referenced in a partition key, either directly or
through a whole row expression

- the new default expression is not immutable

Additionally, we of course bail if the constraint is not found, not
valid, not enforced or doesn't match the specific structure we need.

Another case I considered is the column being referenced in the body of
a pre-parsed function (BEGIN ATOMIC SQL functions). In this case though,
it seems to me that we don't need to fail here: we are not altering the
type of the column, and when reading a stored generated column there's
no expression replacement happening (as it does when reading virtual
columns).

Partitioning/inheritance is supported only on the whole hierarchy at
once (see 8bf6ec3ba3a44448817af47a080587f3b71bee08). Trying to change
the column at only one level will fail, as well as any of the subtrees.

I also added a test to explictily check that we're not accidentally
enqueuing a table scan for verification in phase 3, as avoiding this
kind of work is the whole point of the command.

Logical replication
-------------------

The interaction with logical replication is tricky, since a publication
can have the option to publish generated columns or not (which is the
default).

When not publishing stored generated columns, inserts or updates would
be replicated while backfilling, and would then suddenly stop when the
column is turned into a stored generated column.

One way to avoid this is to set up triggers on the subscriber too,
before altering the column on the publisher. This way updates and
inserts would not lose the column's value on the subscriber, which can
then be migrated by using the new alter table command.

When publishing stored generated columns instead, it is not possible to
have the same column be stored generated on both the publisher and the
subscriber (see Table 29.2 in section 29.6. Generated Column
Replication). The only supported configuration has a regular column on
the side of the subscriber. (Note that this is not specific to this new
command.)

This makes this scenario a lot easier: the column is migrated on the
publisher only, and the subscriber won't lose any value.

To test these two scenarios, I wrote TAP tests for the subscription
suite. However, I'm inclined to not add them to the test suite. I have
attached them to this email separately.

Other changes since v4
----------------------

I have changed phase 2 to be ran at AT_PASS_ADD_OTHERCONSTR, before it
was at AT_PASS_SET_EXPRESSION. The reason to do it there was to reuse
the cleanup steps in ATPostAlterTypeCleanup when a table rewrite did
happen. But since now never rewrite, this is not necessary anymore.

Looking forward to your thoughts on this!

Alberto

--
Alberto Piai
Sensational AG
Zürich, Switzerland