Обсуждение: deviation of 12 from 10 or 11

Hi, all.

Have upgraded 10 to 12, but have a some trouble with ::name(?) to ::text conversion:

in 10 and 11 this works fine:

create schema kpi_test;
create table kpi_test.ttest0 ( a int, b text, c text);

with
cols as (
    select column_name, udt_name from information_schema.columns where table_schema = 'kpi_test' and table_name = 'ttest0' order by ordinal_position
),
cols_sel as (
    select
    string_agg(
        case when column_name = 'a' then repeat('x', 256)
        else column_name
        end,
    ', ') x0
    from cols
)
select length(x0)from cols_sel
;


with
cols as (
    select column_name::text, udt_name from information_schema.columns where table_schema = 'kpi_test' and table_name = 'ttest0' order by ordinal_position
),
cols_sel as (
    select
    string_agg(
        case when column_name = 'a' then repeat('x', 256)
        else column_name::text
        end,
    ', ') x0
    from cols
)
select length(x0)from cols_sel
;

The results are expected: 262 and 262. Ok.

But in 12 the results are 69 and 262. I'm think the ::name is more "efficient" and ::text was converted to ::name ?

 length
════════
     69
(1 row)

 length
════════
    262
(1 row)

Please help.

bx <holybolt@rambler.ru> writes:
> Have upgraded 10 to 12, but have a some trouble with ::name(?) to ::text 
> conversion:
> ...
> But in 12 the results are 69 and 262. I'm think the ::name is more 
> "efficient" and ::text was converted to ::name ?

The column_name column of information_schema.columns is declared
as being of the domain type information_schema.sql_identifier.
Prior to v12 that was a domain over varchar:

/*
 * 5.5
 * SQL_IDENTIFIER domain
 */

CREATE DOMAIN sql_identifier AS character varying;

That was both inefficient and wrong, though, so now it's a domain
over name:

CREATE DOMAIN sql_identifier AS name;

It was inefficient because the underlying catalog columns exposed
through sql_identifier columns are generally of type name; forcibly
coercing them to varchar costs cycles and defeats query optimizations.
It was wrong because the SQL spec says that sql_identifier should
enforce the implementation's limits on identifier length, which
obviously plain varchar would not.

The applicability to your issue is that now the result of that CASE
construct is resolved as type name not type varchar, so it's constrained
to be no more than 63 bytes.  (Yeah, the ELSE result has priority
for determining the CASE's output type.  It's historical.)

            regards, tom lane