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

On Fri Oct 2, 2026 at 1:30 AM CEST, Alberto Piai wrote:
> On Thu Sep 24, 2026 at 12:47 PM CEST, Laurenz Albe wrote:
>> On Thu, 2026-09-24 at 00:19 +0200, Alberto Piai wrote:
>>> I find Matthias' proposal of exposing a function to check image equality
>>> very compelling for the purpose of this patch: besides fixing this
>>> problem, it would also make the command usable for data types which
>>> don't define = (json), as well as those which don't (can't?) define
>>> equalimage()... jsonb, numeric but also tsvector and PostGIS geometry.
>>
>> True, "tsvector" is limiting; I can see people wanting that for
>> generated columns.

I propose to move forward with pg_datum_image_equal() as the contract
for the check constraint. It is simpler for the user. We can always
reconsider the ROW()::record indirection in case the datum image
equality patch cannot be committed for some reason.

So here's v12, rebased on top of master + Matthias'
pg_datum_image_equal() patch from [0]. It changes the implementation to
require the new constraint shape, and updates all the documentation,
tests and error messages.

Besides that, I made a few more changes since v11:

- bail if the column is part of a FK with on delete/update actions:
adding the column directly in CREATE TABLE and ALTER TABLE ADD COLUMN
also rejects that.

- clear atthasmissing and attmissingval for consistency with CREATE
TABLE: a column that was GENERATED since the beginning can't have
those set, so let's be consistent.

- remove support for foreign tables: I don't see the point of supporting
this, since adding a stored generated column on a foreign table does
not involve a rewrite nor a scan. The actual data migration would have
to be done on the remote anyway.

- fix wrong type for a pg_depend.refobjsubid lookup.

- release AccessShareLock after scanning pg_depend: it's inconsequential
since the target rel is already under AccessExclusiveLock, so no
concurrent DDL involving it would happen. But it makes the function
more self contained.

- fix detection of trivial constraint col1=col2 (two Vars, but in
swapped order).

- add a tsvector test, also for dump/restore.

- a few minor fixes, such as an incorrect use of errmsg instead of
errmsg_internal and an incorrect comment.

Looking forward to your thoughts on this!

Regards,

Alberto

[0] https://postgr.es/m/CAEze2WhW41mVDMKRcpLaj_NMLVp4jtQLZjtG3M8RXNNbhZmCcw@mail.gmail.com

--
Alberto Piai
Sensational AG
Zürich, Switzerland