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
