public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Alberto Piai <[email protected]>
To: [email protected]
Subject: Re: Adding a stored generated column without long-lived locks
Date: Mon, 15 Jun 2026 22:41:15 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

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

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Adding a stored generated column without long-lived locks
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox