Обсуждение: Information schema sql_identifier

Per version 12 release notes:

"Treat object-name columns in the information_schema views as being of 
type name, not varchar (Tom Lane)

Per the SQL standard, object-name columns in the information_schema 
views are declared as being of domain type sql_identifier. In 
PostgreSQL, the underlying catalog columns are really of type name. This 
change makes sql_identifier be a domain over name, rather than varchar 
as before. ..."

This came up in this SO question:

https://stackoverflow.com/questions/65416748/postgres-12-4-gives-function-does-not-exists-error

Where the query is:

SELECT (TABLE_SCHEMA || '"."' || TABLE_NAME) as table_name,
        pg_size_pretty(pg_table_size(table_name)) as table_size,
        pg_size_pretty(pg_indexes_size(table_name)) AS indexes_size,
        pg_size_pretty(pg_total_relation_size(table_name)) as total_size
from information_schema.TABLES nowait
where TABLE_SCHEMA='myschema'
order by pg_total_relation_size(table_name) desc;

And the error is:

"ERROR:  function pg_table_size(information_schema.sql_identifier) does 
not exist
LINE 1: ..."."' || TABLE_NAME) as table_name, pg_size_pretty(pg_table_s..."

My attempts:

SELECT pg_table_size(table_name)  from information_schema.tables;
ERROR:  function pg_table_size(information_schema.sql_identifier) does 
not exist
LINE 1: SELECT pg_table_size(table_name)  from information_schema.ta...

SELECT pg_table_size(table_name::text)  from information_schema.tables;
ERROR:  invalid name syntax

SELECT pg_table_size(table_name::regclass)  from information_schema.tables;
ERROR:  invalid name syntax

SELECT table_name::text::regclass  from information_schema.tables;
ERROR:  invalid name syntax

So how does one go about using a table name from 
information_schema.tables in pg_table_size()?


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> So how does one go about using a table name from 
> information_schema.tables in pg_table_size()?

You want something like

select pg_table_size(quote_ident(table_schema)||'.'||quote_ident(table_name))
  from information_schema.tables;

I imagine that the failures you got are a consequence of having
some table names that aren't valid unless quoted (ie contain
spaces, funny characters, etc).  In a general-purpose query,
you can't ignore the schema name either.

I might be more excited about v12's failure to provide an implicit
cast to regclass if there were any prospect of queries like this
working in a bulletproof way without accounting for schema names
and funny characters.  But there isn't, so the query shown in SO
is a house of cards to start with.  When you do it right, with
quote_ident() or format(), no special casting is needed.

            regards, tom lane

On 12/22/20 4:33 PM, David G. Johnston wrote:
> On Tue, Dec 22, 2020 at 5:08 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     SELECT pg_table_size(table_name::regclass)  from
>     information_schema.tables;
>     ERROR:  invalid name syntax
> 
>     So how does one go about using a table name from
>     information_schema.tables in pg_table_size()?
> 
> 
> Find that the function signature in the documentation requires an input 
> of "regclass" and ignore attempts to pass anything but that to the function.
> 
> pg_table_size ( regclass ) → bigint
> 
> I observe in v13 that the expected syntax works just fine:
> 
> select pg_table_size((table_schema || '.' || table_name)::regclass) from 
> information_schema.tables;

Yeah I tried that, didn't include in my previous post:

select pg_table_size((table_schema || '.' || table_name)::regclass) from 
information_schema.tables;
ERROR:  invalid name syntax

> 
> David J.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 12/22/20 4:39 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> So how does one go about using a table name from
>> information_schema.tables in pg_table_size()?
> 
> You want something like
> 
> select pg_table_size(quote_ident(table_schema)||'.'||quote_ident(table_name))
>    from information_schema.tables;
> 
> I imagine that the failures you got are a consequence of having
> some table names that aren't valid unless quoted (ie contain
> spaces, funny characters, etc).  In a general-purpose query,
> you can't ignore the schema name either.
> 
> I might be more excited about v12's failure to provide an implicit
> cast to regclass if there were any prospect of queries like this
> working in a bulletproof way without accounting for schema names
> and funny characters.  But there isn't, so the query shown in SO
> is a house of cards to start with.  When you do it right, with
> quote_ident() or format(), no special casting is needed.

Thanks, that pushed me in right direction.

I see now the previous query worked because the alias table_name and the 
column table_name where the same and the column previously was a 
varchar. This meant the pg_table_size() was actually working on the 
column value not the concatenated value.

So the query can be simplified to:

SELECT
     pg_size_pretty(pg_table_size(quote_ident(table_name))),
     pg_size_pretty(pg_indexes_size(quote_ident(table_name))) AS 
indexes_size,
     pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) AS 
total_size
FROM
     information_schema.tables
WHERE
     table_schema = 'public'
;


> 
>             regards, tom lane
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, 2020-12-22 at 16:07 -0800, Adrian Klaver wrote:
> This came up in this SO question:
> 
> https://stackoverflow.com/questions/65416748/postgres-12-4-gives-function-does-not-exists-error
> 
> Where the query is:
> 
> SELECT (TABLE_SCHEMA || '"."' || TABLE_NAME) as table_name,
>         pg_size_pretty(pg_table_size(table_name)) as table_size,
>         pg_size_pretty(pg_indexes_size(table_name)) AS indexes_size,
>         pg_size_pretty(pg_total_relation_size(table_name)) as total_size
> from information_schema.TABLES nowait
> where TABLE_SCHEMA='myschema'
> order by pg_total_relation_size(table_name) desc;
> 
> And the error is:
> 
> "ERROR:  function pg_table_size(information_schema.sql_identifier) does not exist
> LINE 1: ..."."' || TABLE_NAME) as table_name, pg_size_pretty(pg_table_s..."

I don't see the problem.

Cast "table_name" and "table_schema" to "text" wherever it occurs.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On 12/22/20 11:21 PM, Laurenz Albe wrote:
> On Tue, 2020-12-22 at 16:07 -0800, Adrian Klaver wrote:
>> This came up in this SO question:
>>
>> https://stackoverflow.com/questions/65416748/postgres-12-4-gives-function-does-not-exists-error
>>
>> Where the query is:
>>
>> SELECT (TABLE_SCHEMA || '"."' || TABLE_NAME) as table_name,
>>          pg_size_pretty(pg_table_size(table_name)) as table_size,
>>          pg_size_pretty(pg_indexes_size(table_name)) AS indexes_size,
>>          pg_size_pretty(pg_total_relation_size(table_name)) as total_size
>> from information_schema.TABLES nowait
>> where TABLE_SCHEMA='myschema'
>> order by pg_total_relation_size(table_name) desc;
>>
>> And the error is:
>>
>> "ERROR:  function pg_table_size(information_schema.sql_identifier) does not exist
>> LINE 1: ..."."' || TABLE_NAME) as table_name, pg_size_pretty(pg_table_s..."
> 
> I don't see the problem.
> 
> Cast "table_name" and "table_schema" to "text" wherever it occurs.

SELECT pg_table_size(table_name::text)  from information_schema.tables 
where table_schema = 'public';
ERROR:  invalid name syntax

Per Tom's post this does not cover special cases of identifiers. The 
above was run on my test database that has all manner of weird things it.

So:

SELECT table_name  from information_schema.tables where table_schema = 
'public' and table_name ilike 'space%';
  table_name
-------------
  space table


SELECT pg_table_size('space table') ;
ERROR:  invalid name syntax

SELECT pg_table_size(quote_ident('space table')) ;
  pg_table_size
---------------
           8192


> 
> Yours,
> Laurenz Albe
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com