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

On Wed, 2026-05-27 at 19:44 +0200, Alberto Piai wrote:
> On Tue May 26, 2026 at 5:23 PM CEST, Laurenz Albe wrote:
>
>
> > 2) We currently have ALTER TABLE ... ALTER ... SET EXPRESSION AS (...) to
> > change the generation expression of a column. This command always
> > rewrites the table, according to the documentation.
> > I think that if the present patch adds support to skip rewriting the table
> > when a generation expression is added and the expression matches a check
> > constraint, changing the generation expression should also be possible
> > without a rewrite. If not, I would consider that a violation of the
> > principle of least astonishment.
> > Would it be difficult to extend the patch to support that?
>
> Yes, I don't see a way to make that work. Since we're talking only about
> stored values, a rewrite will always be necessary. However, using this
> new command, a user could add a column with the new expression, then
> atomically drop the old one and rename. All without holding onto an
> AccessExclusiveLock for a long time :)

With your new proposal to never rewrite the table, but fail instead if
there is no constraint, my objection loses its point, so I withdraw it.

> > 3) We already have a couple of tricks to avoid blocking for a long time:
> >
> > - ALTER TABLE ... ALTER ... SET NOT NULL can skip the table scan if there
> > is a check constraint that makes sure that the column is NOT NULL
> >
> > - ALTER TABLE ... ATTACH PARTITION can skip the scan of the new partition
> > if there is a check constraint matching the partition constraint
> >
> > It would be great to document these little tricks in the documentation,
> > probably on the ALTER TABLE page. This is not necessarily the job of
> > this patch, but it would also not be off-topic for the patch.
>
> The SET NOT NULL one and the ATTACH PARTITION one are documented in the
> section specific to the command. However
>
> or, if an equivalent index already exists, it will be attached to the
> target table's index, as if ALTER INDEX ATTACH PARTITION had been
> executed
>
> is not very explicit about the advantages this has for online
> migrations.
>
> In the NOTES section of the ALTER TABLE page, there is a paragraph about
> NOT VALID / VALIDATE, which is another operation in the same spirit as
> this.
>
> Maybe we could group them all in a new section dedicated to online
> schema migrations?

You are right, the existing shortcuts are documented. Your new proposal
makes the proposed feature different from these existing cases, so I don't
think lumping them together is a good idea now.

> Agreed, will fix all these in the next version of the patch.

Great; I'm looking forward to it.

Yours,
Laurenz Albe