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

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(at)kurilemu(dot)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