Re: Adding a stored generated column without long-lived locks - Mailing list pgsql-hackers

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.

Rather than just documenting this problem, I'd say that your idea with
the equalimage() support function has appeal.  We can simply reject the
operation if the function returns FALSE.

Yours,
Laurenz Albe