Thread: ALTER COLUMN SET DATA TYPE does not change the generation expression's collation
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 ]}
Re: ALTER COLUMN SET DATA TYPE does not change the generation expression's collation
From
jian he
Date:
On Wed, Mar 26, 2025 at 1:01 PM jian he <jian.universality@gmail.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
Re: ALTER COLUMN SET DATA TYPE does not change the generation expression's collation
From
Tom Lane
Date:
jian he <jian.universality@gmail.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