agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Alberto Piai <alberto.piai@gmail.com>
To: Alberto Piai <alberto.piai@gmail.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-hackers@postgresql.org
Cc: Álvaro Herrera <alvherre@kurilemu.de>
Subject: Re: Adding a stored generated column without long-lived locks
Date: Mon, 21 Sep 2026 23:48:03 +0200
Message-ID: <DLLBPPI9MKV4.1EYFI9ZD90Z1E@gmail.com> (raw)
In-Reply-To: <DLGVZTSLAPS8.39J305UANRWN@gmail.com>
References: <6f5ea02f6e5205a96a9b3979190a4d7cb3c99414.camel@cybertec.at>
<DKXFEG915LK8.12HECZQW2OWQE@gmail.com>
<86a9336dc25d9a085916ad247a5791dc53d2ab13.camel@cybertec.at>
<DKY5OJ1NSSGO.3U2FKRKCHM533@gmail.com>
<57bd5c0e2b532c07a1e6cd867520e899b1007622.camel@cybertec.at>
<DL0C8TG99HCO.1XVPKG2IM0C1S@gmail.com>
<31fec16020bdf25d4a62cbbb4b8ea00af007e0d9.camel@cybertec.at>
<DL0PFQ4CM3KO.38HQUI04TKPLT@gmail.com>
<82554bb40f6822bebbda7fcbc5ec1dc6a1823c0d.camel@cybertec.at>
<DL3CJBUV86UG.3IZJ0KV63YKZ2@gmail.com>
<DLGVZTSLAPS8.39J305UANRWN@gmail.com>
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
view thread (36+ messages) latest in thread
Message-ID: <DLLBPPI9MKV4.1EYFI9ZD90Z1E@gmail.com>
Permalink: ../DLLBPPI9MKV4.1EYFI9ZD90Z1E@gmail.com/
Also on: postgresql.org/message-id/DLLBPPI9MKV4.1EYFI9ZD90Z1E@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alberto.piai@gmail.com, laurenz.albe@cybertec.at, alvherre@kurilemu.de
Subject: Re: Adding a stored generated column without long-lived locks
In-Reply-To: <DLLBPPI9MKV4.1EYFI9ZD90Z1E@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox