pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom: Alberto Piai <alberto.piai@gmail.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: Matthias van de Meent <boekewurm+postgres@gmail.com>
To: Álvaro Herrera <alvherre@kurilemu.de>
Cc: Alberto Piai <alberto.piai@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Adding a stored generated column without long-lived locks
Date: Thu, 24 Sep 2026 00:19:05 +0200
Message-ID: <DLN1MKEVTIQW.31UMH54A38IA7@gmail.com> (raw)
In-Reply-To: <d57d7a0b7e2fe1c148996e41559647adde4c2034.camel@cybertec.at>
References: <DLLBPPI9MKV4.1EYFI9ZD90Z1E@gmail.com>
<arKaxe9vLCCi_eio@alvherre.pgsql>
<CAEze2WhTKSn8gTAneJXEHRTqxpQ9Jix0ijd5fSa3owOpiQuC3Q@mail.gmail.com>
<d57d7a0b7e2fe1c148996e41559647adde4c2034.camel@cybertec.at>
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, Álvaro Herrera <alvherre@kurilemu.de> wrote:
>> > On 2026-Sep-21, Alberto Piai wrote:
>> >
>> > > 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.
>> >
>> > Does this _matter_?
>>
>> 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 = (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
--
Alberto Piai
Sensational AG
Zürich, Switzerland
view thread (41+ messages) latest in thread
Message-ID: <DLN1MKEVTIQW.31UMH54A38IA7@gmail.com>
Permalink: ../DLN1MKEVTIQW.31UMH54A38IA7@gmail.com/
Also on: postgresql.org/message-id/DLN1MKEVTIQW.31UMH54A38IA7@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, boekewurm+postgres@gmail.com, alvherre@kurilemu.de
Subject: Re: Adding a stored generated column without long-lived locks
In-Reply-To: <DLN1MKEVTIQW.31UMH54A38IA7@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox