Thread: 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