Information schema sql_identifier

On Tue, Dec 22, 2020 at 5:08 PM Adrian Klaver <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;

David J.

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