Message-ID: <03658280d2a719fe854457b581f420865a3d1238.camel@cybertec.at>
Subject: Re: Adding a stored generated column without long-lived locks
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Alberto Piai <alberto.piai@gmail.com>, pgsql-hackers@postgresql.org
Date: Mon, 15 Jun 2026 22:41:15 +0200
In-Reply-To: <DITN9EVYH0RK.N3T595ULOV4T@gmail.com>
References: <abkrpUwlGngF4e-d@phidippus.sen.work>
	 <DHMSK551GIM8.1B1CN2JN8BK50@gmail.com>
	 <DI19FP3X4VI0.21FO6V1HGJK5P@gmail.com>
	 <DIIRFCWJIA80.2Q5DYIX6C7KZ5@gmail.com>
	 <1514ac90bc374f750df34fe465caadd77121b067.camel@cybertec.at>
	 <DITN9EVYH0RK.N3T595ULOV4T@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/03658280d2a719fe854457b581f420865a3d1238.camel%40cybertec.at>
Precedence: bulk

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:
>=20
>=20
> > 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 c=
heck
> >    constraint, changing the generation expression should also be possib=
le
> >    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?
>=20
> 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=
:
> >=20
> >    - 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
> >=20
> >    - ALTER TABLE ... ATTACH PARTITION can skip the scan of the new part=
ition
> >      if there is a check constraint matching the partition constraint
> >=20
> >    It would be great to document these little tricks in the documentati=
on,
> >    probably on the ALTER TABLE page.  This is not necessarily the job o=
f
> >    this patch, but it would also not be off-topic for the patch.
>=20
> The SET NOT NULL one and the ATTACH PARTITION one are documented in the
> section specific to the command. However
>=20
>   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
>=20
> is not very explicit about the advantages this has for online
> migrations.
>=20
> 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.
>=20
> 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