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

On Thu, 2026-08-06 at 14:36 +0200, Alberto Piai wrote:
> PFA v7, implementing this version of the command:
>
> ... ALTER col ADD GENERATED USING CONSTRAINT constr_name STORED

Great! I think this is pretty much good to go.

> In particular, all the error messages now follow the guidelines for
> error reporting. I tried to use a consistent error message everywhere,
> adding details and hints where appropriate.

Much better!

I was wondering about this hint:

Use this command on the root table/partition without ONLY.

You can never turn a column of a partition into a generated column, right?
How about

Use the command on the partition root without specifying ONLY.

This detail message is longer than 80 characters:

Converting a column to a stored generated column can only be done on the whole hierarchy at once.

How about

Converting only part of a partitioning/inheritance hierarchy is not supported.

> A note about this one:
>
> > The following error message is not very helpful:
> >
> >
> > CREATE TABLE tab (
> > a integer DEFAULT 2,
> > b integer
> > CONSTRAINT con CHECK (b IS NOT DISTINCT FROM 2 + random())
> > );
> >
> >
> > ALTER TABLE tab ALTER b ADD GENERATED ALWAYS STORED USING CONSTRAINT con;
> > ERROR: cannot convert a column into a stored generated column without a constraint to prove that the values are consistent
> > DETAIL: could not find a valid constraint "con" CHECK ("b" IS NOT DISTINCT FROM (expr))
>
> This was interesting. What's going on here is that since random()
> returns a float, the whole expression returns a float. The column b is
> an int, so since there is an implicit cast from int to float, the
> resulting expression for the CHECK constraint is
>
> b::float IS NOT DISTINCT FROM 2 + random()
>
> I think in cases like this there's not much I can do: the constraint
> isn't an equality to b anymore, but an equality to f(b) where f is
> the function defined for the cast. The constraint is simply not usable
> for our purpose.
>
> For this reason, I think it doesn't make too much sense in this case to
> look at the other operand, hunt down the random() and complain about the
> function being volatile: the core problem here is the return type, and
> the same situation can happen with an immutable function.
>
> I tried detecting implicit casts though, because I think this is a
> mistake that's quite easy to make, so it's worth trying to give a hint
> to the user about what's going on and what to do.
>
> This is now reported in this way (from the regress test suite):
>
> alter table tgen.t1 add constraint chk_gen check (b is not distinct from (a + random()));
> -- the hint should inform about the type cast
> alter table tgen.t1 alter column b add generated using constraint chk_gen stored;
> ERROR: cannot convert column "b" to generated
> DETAIL: Could not find a valid constraint "chk_gen" CHECK ("b" IS NOT DISTINCT FROM expr).
> HINT: Ensure that the type of the expression matches the type of the column.
>
> In this situation, \d would show the constraint being
>
> CHECK (b::double precision = (.... expr with random())
>
> instead of b = ...expr, which makes me think the hint is clear enough.
> But I'm curious to hear what you think about it.
>
> Independently from the problem with casts, the immutability of the
> generation expression is of course also checked:
>
> alter table tgen.t2 add constraint chk_gen check (b is not distinct from (a + random()::int));
> alter table tgen.t2 alter column b
> add generated using constraint chk_gen stored;
> ERROR: generation expression is not immutable

I agree with your assessment; thanks for the additional hint!

There is one sentence in the documentation that sounds wrong to me:

+ <para>
+ After this command is run, <literal>column_name</literal> will be a stored
+ generated column with <literal>expr</literal> as its generation
+ expression.
+ </para>

Shouldn't it be "after this command has been run"? Or perhaps "has completed"?

Yours,
Laurenz Albe