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

Hi all,

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. In general it's clear that I
need to use an operator which proves that the value is identical bit by
bit to what evaluating the expression would produce. The question will
be how to make this accessible and usable enough, and at which cost in
complexity.

I won't have time to work on this for at least a few days, so in the
meantime I will remove the "ready for committer" tag to avoid luring
anyone into looking at this (at least with the committer hat on). Any
feedback | thought | idea would of course be very welcome! :)

Kind regards,

Alberto

--
Alberto Piai
Sensational AG
Zürich, Switzerland