From: Tom Lane <tgl@sss.pgh.pa.us>
To: jian he <jian.universality@gmail.com>
cc: pgsql-hackers@lists.postgresql.org
Subject: Re: ALTER COLUMN SET DATA TYPE does not change the generation expression's collation
In-reply-to: <CACJufxFYsg2UKnpnMcJmLhbQei4EVxb0+t69s1BPZwiDDh5UVw@mail.gmail.com>
References: <CACJufxGKWZ3WWC7m_F4v-1X7NP2=-eU8hjo37P8RfEcdcVzUTg@mail.gmail.com> <CACJufxFYsg2UKnpnMcJmLhbQei4EVxb0+t69s1BPZwiDDh5UVw@mail.gmail.com>
Comments: In-reply-to jian he <jian.universality@gmail.com>
	message dated "Fri, 18 Apr 2025 21:05:33 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2635972.1744996321.1@sss.pgh.pa.us>
Date: Fri, 18 Apr 2025 13:12:01 -0400
Message-ID: <2635973.1744996321@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2635973.1744996321%40sss.pgh.pa.us>
Precedence: bulk

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