Subject: Re: ALTER COLUMN SET DATA TYPE does not change the generation expression's collation

From: Tom Lane <[email-masked]>
Date: Fri, 18 Apr 2025 13:12:01 -0400

jian he <[email-masked]> writes:
>> ATPrepAlterColumnType forbids us to ALTER COLUMN SET DATA TYPE USING (expr)
>> for generated columns.
>> however we can still change the generated column type from non-text to text
>> or text type from one collation to another collation.

I don't really understand why we allow SET DATA TYPE on a generated
column at all.  However ...

>> In ATExecAlterColumnType, we also need to set the generation
>> expression collation?

Don't believe that would matter in the slightest.  The generation
expression is not exposed anywhere --- we don't incorporate it
in the plan tree, just evaluate it in ExecComputeStoredGenerated.

It could matter in the case of a virtual generated column,
but it appears that that works already:

regression=# CREATE TABLE x1(a int,
                b int GENERATED ALWAYS AS (a * 2) virtual,
                c text GENERATED ALWAYS AS ('1') stored );
CREATE TABLE
regression=# insert into x1 values (11);
INSERT 0 1
regression=# ALTER TABLE x1 alter column b set data type text collate "C";
ALTER TABLE
regression=#  select pg_collation_for(b) from x1;
 pg_collation_for 
------------------
 "C"
(1 row)
regression=# ALTER TABLE x1 alter column b set data type text collate "POSIX";
ALTER TABLE
regression=#  select pg_collation_for(b) from x1;
 pg_collation_for 
------------------
 "POSIX"
(1 row)

(It looks like the reason that works is that
build_generation_expression inserts the necessary coercion.)

So I don't see a bug here.  If you want to claim that this is
a bug deserving of being an open item, I think you need to
demonstrate some observable misbehavior.  If you want to say
it'd be cleaner to fix the stored expression and get rid of
the extra step in build_generation_expression, I'd probably
agree, but that seems like cleanup that could wait for v19.
It's certainly not a bug affecting any stable branches.

			regards, tom lane