Обсуждение: ERROR: relation "sql_features" does not exist
Hi
I tried to write a query that does lateral join between information_schema.tables and pgstattuple function.
select * from information_schema.tables, lateral(select * from pgstattuple(table_name::name)) s where table_type = 'BASE TABLE';
The query finished by strange error
postgres=# select * from information_schema.tables, lateral(select * from pgstattuple(table_name::name)) s where table_type = 'BASE TABLE';
ERROR: relation "sql_features" does not exist
ERROR: relation "sql_features" does not exist
When I set search_path to information_schema, then the query is running. But there is not any reason why it should be necessary.
I found this issue on pg 11.11, but the same behavior is on master branch.
Regards
Pavel
Pavel Stehule <pavel.stehule@gmail.com> writes: > I tried to write a query that does lateral join between > information_schema.tables and pgstattuple function. > select * from information_schema.tables, lateral(select * from > pgstattuple(table_name::name)) s where table_type = 'BASE TABLE'; > The query finished by strange error > postgres=# select * from information_schema.tables, lateral(select * from > pgstattuple(table_name::name)) s where table_type = 'BASE TABLE'; > ERROR: relation "sql_features" does not exist > When I set search_path to information_schema, then the query is running. > But there is not any reason why it should be necessary. Nope, this is classic user error, nothing else. "table_name::name" is entirely inadequate as a way to reference a table that isn't visible in your search path. You have to incorporate the schema name as well. Ideally you'd just pass the table OID to the OID-accepting version of pgstattuple(), but of course the information_schema schema views don't expose OIDs. So basically you need something like pgstattuple((quote_ident(table_schema)||'.'||quote_ident(table_name))::regclass) although perhaps format() could help a little here. regards, tom lane
po 26. 4. 2021 v 19:10 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> I tried to write a query that does lateral join between
> information_schema.tables and pgstattuple function.
> select * from information_schema.tables, lateral(select * from
> pgstattuple(table_name::name)) s where table_type = 'BASE TABLE';
> The query finished by strange error
> postgres=# select * from information_schema.tables, lateral(select * from
> pgstattuple(table_name::name)) s where table_type = 'BASE TABLE';
> ERROR: relation "sql_features" does not exist
> When I set search_path to information_schema, then the query is running.
> But there is not any reason why it should be necessary.
Nope, this is classic user error, nothing else. "table_name::name"
is entirely inadequate as a way to reference a table that isn't
visible in your search path. You have to incorporate the schema
name as well.
Ideally you'd just pass the table OID to the OID-accepting version of
pgstattuple(), but of course the information_schema schema views
don't expose OIDs. So basically you need something like
pgstattuple((quote_ident(table_schema)||'.'||quote_ident(table_name))::regclass)
although perhaps format() could help a little here.
I understand now. Thank you for explanation
select * from information_schema.tables, lateral(select * from pgstattuple(format('%I.%I', table_schema, table_name))) s where table_type = 'BASE TABLE';
This is working
Regards
Pavel
regards, tom lane