Re: Adding a stored generated column without long-lived locks - Mailing list pgsql-hackers

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 spent some time thinking about the situation, especially about whether
the incoherence between the backfilled value and the result of
thegenerator expression actually matters in practice.

I am acutely aware that the example I reported earlier is somewhat...
artificial. However, the fact that any rewrite operation which causes
the expression to be recomputed would silently change the stored value
(update a = a) really makes me want to treat this like a soundness issue
in my patch. (Not to mention the broken pg_restore).

So I see only a few ways forward:

- drop this patch, which would be too bad because it does address an
  operational pain point

- continue with IS NOT DISTINCT FROM, but restrict it to work with types
  which implement BTEQUALIMAGE_PROC. This would exclude useful types
  like jsonb, tsvector and geometry.

  (Aside regarding geometry: I never had the chance to work with
  PostGIS, but at a quick glance it seems to store a lot of information
  in typmod. It would definitely be wrong to implement equalimage. But
  there seems to be so much in there that I wonder whether it would not
  be very difficult for a user trying to backfill the column to do so in
  a way that it would satisfy a stricter image-equality constraint. I
  might very well be misreading all of this, though.)

- work on exposing image equality as proposed by Matthias'
  pg_datum_image_equal(), and require that for the constraint (we could
  optionally allow IS NOT DISTINCT FROM for types which implement
  equalimage, but I find the function as simple to use. Since this is an
  extremely ad-hoc constraint created just for the purpose of a
  migration, I'd go for function-only)

The notion of image equality already is somewhat exposed to the user
through *= (record_image_eq). I wonder what could be the downsides of
also exposing it for a single Datum.

For the purpose of this alter table command, one concern could be that
it's "too difficult" to produce values satisfying the stricter
constraint when backfilling (see concerns about gemoetry above). But for
the use cases I would use it for, I'd write the backfilling code to
derive the value from the row, using the exact same expression I just
used for the constraint. And if I failed to do so, then I'd get a pretty
clear constraint failure.

I'll add my review and this use case to the other thread, but in the
meantime I thought I could already post this, even if it contains more
open thoughts and questions than a concrete proposal.



Regards,

Alberto


--
Alberto Piai
Sensational AG
Zürich, Switzerland