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

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.

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

ALTER TABLE t_repro_1 ADD CHECK
(numeric_send(b) IS NOT DISTINCT FROM numeric_send(a));

That would exclude data types that don't have a send function
(which is probably no big loss), and I am not certain if the send
function is required to represent the binary data exactly (it does
for the system data types, as far as I know).

Yours,
Laurenz Albe