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
