public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: jian he <[email protected]>
Cc: [email protected]
Subject: Re: ALTER COLUMN SET DATA TYPE does not change the generation expression's collation
Date: Fri, 18 Apr 2025 13:12:01 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACJufxFYsg2UKnpnMcJmLhbQei4EVxb0+t69s1BPZwiDDh5UVw@mail.gmail.com>
References: <CACJufxGKWZ3WWC7m_F4v-1X7NP2=-eU8hjo37P8RfEcdcVzUTg@mail.gmail.com>
	<CACJufxFYsg2UKnpnMcJmLhbQei4EVxb0+t69s1BPZwiDDh5UVw@mail.gmail.com>

jian he <[email protected]> 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

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: ALTER COLUMN SET DATA TYPE does not change the generation expression's collation
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox