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

jian he <jian(dot)universality(at)gmail(dot)com> 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