Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Thu, 24 Sep 2026 00:19:05 +0200
Message-Id: <DLN1MKEVTIQW.31UMH54A38IA7@gmail.com>
Cc: "Alberto Piai" <alberto.piai@gmail.com>, <pgsql-hackers@postgresql.org>
From: "Alberto Piai" <alberto.piai@gmail.com>
To: "Laurenz Albe" <laurenz.albe@cybertec.at>, "Matthias van de Meent"
 <boekewurm+postgres@gmail.com>, =?utf-8?q?=C3=81lvaro_Herrera?=
 <alvherre@kurilemu.de>
Subject: Re: Adding a stored generated column without long-lived locks
References: <DLLBPPI9MKV4.1EYFI9ZD90Z1E@gmail.com>
 <arKaxe9vLCCi_eio@alvherre.pgsql>
 <CAEze2WhTKSn8gTAneJXEHRTqxpQ9Jix0ijd5fSa3owOpiQuC3Q@mail.gmail.com>
 <d57d7a0b7e2fe1c148996e41559647adde4c2034.camel@cybertec.at>
In-Reply-To: <d57d7a0b7e2fe1c148996e41559647adde4c2034.camel@cybertec.at>
Archived-At: 
 <https://www.postgresql.org/message-id/DLN1MKEVTIQW.31UMH54A38IA7%40gmail.com>
Precedence: bulk

On Wed Sep 23, 2026 at 12:26 PM CEST, Laurenz Albe wrote:
> On Wed, 2026-09-23 at 12:07 +0200, Matthias van de Meent wrote:
>> On Tue, 22 Sept 2026 at 17:17, =C3=81lvaro Herrera <alvherre@kurilemu.de=
> wrote:
>> > On 2026-Sep-21, Alberto Piai wrote:
>> >=20
>> > > Repro:
>> > >=20
>> > >   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);
>> > >=20
>> > >   alter table tgen.t_repro_1
>> > >     alter b add generated using constraint chk_gen stored;
>> > >=20
>> > >   update tgen.t_repro_1 set a =3D a;
>> > >   ERROR:  duplicate key value violates unique constraint "t_repro_1_=
b_idx"
>> > >   DETAIL:  Key ((b::text))=3D(1.0) already exists.
>> >=20
>> > Does this _matter_?
>>=20
>> Yes, this does matter.
>
> I tend to agree.

I too think this matters. The main arugment is IMHO the catastrophic
failure mode: successful ALTER TABLE command, broken pg_restore... I
would hate to put anyone in that situation.

I find Matthias' proposal of exposing a function to check image equality
very compelling for the purpose of this patch: besides fixing this
problem, it would also make the command usable for data types which
don't define =3D (json), as well as those which don't (can't?) define
equalimage()... jsonb, numeric but also tsvector and PostGIS geometry.
I'll take some time to study/review the patch and the discussion around
it.


Kind regards,

Alberto


--=20
Alberto Piai
Sensational AG
Z=C3=BCrich, Switzerland