ALTER COLUMN SET DATA TYPE does not change the generation expression's collation - Mailing list pgsql-hackers

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 ]}