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

On Wed, Mar 26, 2025 at 1:01 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> hi.
>
> 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.
>
> In ATExecAlterColumnType, we also need to set the generation
> expression collation?
>
> We can do this by adding exprSetCollation:
>
> --- a/src/backend/commands/tablecmds.c
> +++ b/src/backend/commands/tablecmds.c
> @@ -14115,6 +14115,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab,
> Relation rel,
> errmsg("default for
> column \"%s\" cannot be cast automatically to type %s",
>
> colName, format_type_be(targettype))));
> }
> + exprSetCollation(defaultexpr, targetcollid);
>
>
> ---------------------
> CREATE TABLE x1(a int,
> b int GENERATED ALWAYS AS (a * 2) stored,
> c text GENERATED ALWAYS AS ('1') stored );
> ALTER TABLE x1 alter column b set data type text collate "C";
> ALTER TABLE x1 alter column c set data type text collate "C";
>
> SELECT pg_get_expr(d.adbin, d.adrelid) AS default_value, d.adbin
> FROM pg_catalog.pg_attribute a
> JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum)
> AND a.attrelid = 'x1'::regclass
> AND a.attname in ('b', 'c');
> by adding exprSetCollation, the output is
>
> default_value | (a * 2)
> adbin | {COERCEVIAIO :arg {OPEXPR :opno 514 :opfuncid 141
> :opresulttype 23 :opretset false :opcollid 0 :inputcollid 0 :args
> ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0
> :varnullingrels (b) :varlevelsup 0 :varreturningtype 0 :varnosyn 1
> :varattnosyn 1 :location -1} {CONST :consttype 23 :consttypmod -1
> :constcollid 0 :constlen 4 :constbyval true :constisnull false
> :location -1 :constvalue 4 [ 2 0 0 0 0 0 0 0 ]}) :location -1}
> :resulttype 25 :resultcollid 950 :coerceformat 2 :location -1}
> -[ RECORD 2 ]-+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> default_value | '1'::text COLLATE "C"
> adbin | {CONST :consttype 25 :consttypmod -1 :constcollid 950
> :constlen -1 :constbyval false :constisnull false :location -1
> :constvalue 5 [ 20 0 0 0 49 ]}
>
>
> master behavior:
>
> default_value | (a * 2)
> adbin | {COERCEVIAIO :arg {OPEXPR :opno 514 :opfuncid 141
> :opresulttype 23 :opretset false :opcollid 0 :inputcollid 0 :args
> ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0
> :varnullingrels (b) :varlevelsup 0 :varreturningtype 0 :varnosyn 1
> :varattnosyn 1 :location -1} {CONST :consttype 23 :consttypmod -1
> :constcollid 0 :constlen 4 :constbyval true :constisnull false
> :location -1 :constvalue 4 [ 2 0 0 0 0 0 0 0 ]}) :location -1}
> :resulttype 25 :resultcollid 0 :coerceformat 2 :location -1}
> -[ RECORD 2 ]-+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> default_value | '1'::text
> adbin | {CONST :consttype 25 :consttypmod -1 :constcollid 100
> :constlen -1 :constbyval false :constisnull false :location -1
> :constvalue 5 [ 20 0 0 0 49 ]}

I still think this is a bug, so i put it on the

https://wiki.postgresql.org/wiki/PostgreSQL_18_Open_Items#Older_bugs_affecting_stable_branches