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

On Tue, 22 Sept 2026 at 15:02, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>
> On Mon, 2026-09-21 at 23:48 +0200, Alberto Piai wrote:
> > in an attempt to avoid wasting committer time, I decided to use an
> > LLM-based tool to analyze this patch and try to come up with
> > counterexamples to break my usage of IS NOT DISTINCT FROM.
> >
> > It produced an example showing how IS NOT DISTINCT FROM isn't good
> > enough either for my purpose.
> >
> > The problem is types where some values are evaluated as equal (according
> > to =), but don't have the same representation. In conjuction with a
> > unique index, they could be used to put a database in an invalid state
> > where rewriting operations (update ... set a = a or pg_dump/pg_restore)
> > fail.
> >
> > Repro:
> >
> > create table tgen.t_repro_1 (a numeric, b numeric);
> > insert into tgen.t_repro_1 values ('1.0', '1.00'), ('1.0', '1.0');
> > create unique index on tgen.t_repro_1 ((b::text));
> > alter table tgen.t_repro_1
> > add constraint chk_gen check (b is not distinct from a);
> >
> > alter table tgen.t_repro_1
> > alter b add generated using constraint chk_gen stored;
> >
> > update tgen.t_repro_1 set a = a;
> > ERROR: duplicate key value violates unique constraint "t_repro_1_b_idx"
> > DETAIL: Key ((b::text))=(1.0) already exists.
> >
> >
> > I will have to re-think this quite a bit.
>
> Ho, hum. Case insensitive collations would be another example.
>
> There is no way to write "is binary identical to" in SQL, as far as
> I can tell.

None yet: [0] is a patch that adds a pg_datum_image_equal(a, b)
function that should allow users to detect binary differences in all
types of values. It won't detect differences in the way data is
stored (it only checks detoasted datum differences, so toast ID or
compression types are not tested), but I think the function added in
that patch is sufficient for this use case, too.

> Perhaps a solution would be to force the use of the data type's send
> function in the check constraint:

Going through sendfn can be rather expensive (even compared to
detoasting), which is why I started the work on [0]: At databricks we
had a comparable use case of needing to detect exactly equal input
data, as cheap as possible.

Note that "is not distinct from" will work correctly when the
underlying = operator is part of a btree opfamily whose equalimage()
support function returns true; this is something that you can use to
make sure the selected constraint in ADD GENERATED can safely be used
even if some other types won't work. The equalimage() support
function's result indicates whether the type has a strict total
ordering (when true), or whether there are values which have different
representations but are considered equal in this opclass' sort
ordering (when false). This same support function also indicates
whether deduplication can be used for the indexed data (when true) or
not (when false).

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

[0] https://commitfest.postgresql.org/patch/6309/