Message-ID: <3af338791e8ab27c4a7efbc4217c97162de76bc8.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
Cc: =?ISO-8859-1?Q?=C1lvaro?= Herrera <alvherre@kurilemu.de>
Date: Mon, 15 Jun 2026 20:38:55 +0200
In-Reply-To: <DITN90YX3328.2A0I3LCV164BA@gmail.com>
References: <abkrpUwlGngF4e-d@phidippus.sen.work>
	 <DHMSK551GIM8.1B1CN2JN8BK50@gmail.com>
	 <DI19FP3X4VI0.21FO6V1HGJK5P@gmail.com>
	 <DIIRFCWJIA80.2Q5DYIX6C7KZ5@gmail.com>
	 <DITN90YX3328.2A0I3LCV164BA@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/3af338791e8ab27c4a7efbc4217c97162de76bc8.camel%40cybertec.at>
Precedence: bulk

On Wed, 2026-05-27 at 19:43 +0200, Alberto Piai wrote:
> I'd like to take his point, together with the question from Tom and the
> suggestion by =C3=81lvaro, and make a new proposal for the design of this
> command.
>=20
> Design iteration 2
> ------------------
>=20
> Syntax:
>=20
>   ALTER TABLE t ALTER COLUMN c
>     ADD GENERATED ALWAYS AS (expr) STORED USING CONSTRAINT check_name
>=20
> check_name must be a valid constraint of the form
>=20
>   CHECK (c IS NOT DISTINCT FROM (expr))
>=20
> This fails if:
>=20
> - a check constraint named check_name is not found for table c
> - the constraint is not valid
> - the constraint does not match exactly the expr the user intends to use
>   as a stored default expression
>=20
> On success, the table c is now a stored generated column with the given
> default expression, and the check_name constraint has been removed.
>
> This addresses Tom's remark, we can now fail instead of just rewriting
> the column.

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.

> It improves slightly upon the issue of a potential conflict with a
> future edition of the SQL standard, by being more specific. I don't see
> a way to be completely sure we won't have conflicts. We could improve
> more by making the syntax more "alien" and very unlinkely to be picked
> up by the standard, but at a usability cost for Postgres. I'm open to
> suggestions.

I don't think that the new proposal makes it less likely to get in
conflict with later additions to the standard.  But I don't think that
inventing unlikely syntax to avoid such conflicts is a good idea.
First, the standard committee itself seems (or seemed) to have a strong
predilection for alien, verbose syntax.
Second, if we end up with weird, unwieldy syntax, that would be bad.

No, the syntax you are proposing sounds reasonable to me.

> It improves upon another question raised by =C3=81lvaro: does the user ha=
ve
> to clean up the constraint? In v1 I felt it was better to have the user
> remove it after the migration. Since here it's explicitly mentioned as
> the constraint to use to migrate the column, I think it's OK to remove
> it. We are conceptually moving it from being a constraint to being the
> new default expression.
>=20
> The implementation should also be simpler, since there will never be a
> table rewrite.
>=20
> 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.

Yours,
Laurenz Albe