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

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. We can't start ignoring and tossing
significant zeroes in numeric types, because they have meaning in
scientific calculations, and have different outputs for various
operations, including casts to text, but also functions like scale.

Generated columns should (must) respect that, and produce the same
result between SELECT (gen_col_expr) and SELECT gen_col.

> Does anybody want to have a generated numeric column that's identical to
> the base column except it has more zeroes in the decimal part? Or to
> generate a text column that's not binary identical to another text
> column but compares equal when viewed through an nondeterministic
> collation?

I don't think anyone wants that specifically, but if the database
state contains that numeric column that's identical to the first, but
with more zeroes, or a text column with different encoding, then that
must be detected before we go ahead and fully consider the target
column to be a generated version of the source column; the outputs
must be identical even if the default = operator thinks they have the
same value.

Note that this issue extends to at least jsonb, which uses textual and
numeric comparators internally for the sorting operations; it's
certainly possible to remove or add a few zeroes here and there in
projection steps if you're not careful when you populate the
not-yet-GENERATED column's data.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)