Uploaded image for project: 'Percona Monitoring and Management'
  1. Percona Monitoring and Management
  2. PMM-7992

QAN with PMM shows "Table not found: sql: no rows in result set" instead of table info when table is not in public schema



      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;.


        Issue Links



              Unassigned Unassigned
              sergey.kuzmichev Sergey Kuzmichev (Inactive)
              0 Vote for this issue
              2 Start watching this issue



                Smart Checklist