Details
-
Bug
-
Status: Open
-
Medium
-
Resolution: Unresolved
-
2.16.0
-
None
-
Observability
Description
When looking at PG load in QAN, if tables query touches are in a schema other than public, you can't see table structure. All you get is "Table not found: sql: no rows in result set". See the following picture for how it looks:
The same doesn't happen when tables are in public schema:
Expected result: table structure should be output regardless of table schema.
A bit more elaborate explanation: PMM expects that a database user it uses can see the inspected table in its search path, which is not true, unless pmm user's search path lists every schema in every database simultaneously.
To reproduce, run two pgbench processes. One against public schema, and one against any other schema. Providing some steps:
postgres=# create user pgbench_usr;
postgres=# create database pgbench_public owner pgbench_usr;
postgres=# create database pgbench_nopublic owner pgbench_usr;
postgres=# \c pgbench_nopublic
pgbench_nopublic=# create schema pgbench authorization pgbench_usr;
pgbench_nopublic=# drop schema public;
pgbench_nopublic=# alter user pgbench_usr SET search_path = "$user", pgbench, public;
pgbench -U pgbench_usr -d pgbench_public -i
pgbench -U pgbench_usr -d pgbench_nopublic -i
pgbench_public=> \d
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+-------------
public | pgbench_accounts | table | pgbench_usr
public | pgbench_branches | table | pgbench_usr
public | pgbench_history | table | pgbench_usr
public | pgbench_tellers | table | pgbench_usr
(4 rows)
pgbench_nopublic=> \d
List of relations
Schema | Name | Type | Owner
---------+------------------+-------+-------------
pgbench | pgbench_accounts | table | pgbench_usr
pgbench | pgbench_branches | table | pgbench_usr
pgbench | pgbench_history | table | pgbench_usr
pgbench | pgbench_tellers | table | pgbench_usr
(4 rows)
pgbench -U pgbench_usr -j1 -c1 -P5 -T7200 pgbench_public
pgbench -U pgbench_usr -j1 -c1 -P5 -T7200 pgbench_nopublic
Once PMM picks up some data, you should be able to see in QAN that when inspecting queries running against pgbench_public database you can view table structure, and see "Table not found: sql: no rows in result set" for queries over pgbench_nopublic.
Note that using public schema for user objects is discouraged and a recommended practice is to remove public schema from the search path: https://www.postgresql.org/docs/13/ddl-schemas.html#DDL-SCHEMAS-PATTERNS
To make describe table work in my reproduction scenario, run alter user pmm set search_path = "$user", public, pgbench;.
Attachments
Issue Links
- relates to
-
-
- Done
-