pq: column s.total_plan_time does not exist: Switch pg_stat_statements query composer from DB server version to extension version

[b3zn0gim]

Hey! I'm having issue with pg-agent on AWS RDS PostgreSQL 13.3. pg-agent logs reports:

W1130 17:51:06.706240       1 collector.go:141] pq: column s.total_plan_time does not exist
W1130 17:51:21.625628       1 collector.go:141] pq: column s.total_plan_time does not exist
W1130 17:51:34.362643       1 collector.go:235] no summaries
W1130 17:51:36.627009       1 collector.go:141] pq: column s.total_plan_time does not exist
W1130 17:51:49.361532       1 collector.go:235] no summaries
W1130 17:51:51.625913       1 collector.go:141] pq: column s.total_plan_time does not exist
W1130 17:52:04.362273       1 collector.go:235] no summaries

pg-agent checks PostgreSQL server version to compose query to pg_stat_statements. If version is =>13.0.0 then it assumes that pg_stat_statements view has column total_plan_time, however:

My PostgreSQL server version:

postgres => SELECT version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
(1 row)

postgres =>SELECT setting FROM pg_settings WHERE name='server_version';
 setting
---------
 13.3
(1 row)

But I have older version of pg_stat_statements extension.

So, I think that pg-agent actually should check extension version (pg_extension.extversion) instead of server version :

postgres => SELECT * FROM pg_extension where extname = 'pg_stat_statements';
  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 17016 | pg_stat_statements |       10 |        17014 | t              | 1.4        |           |
(1 row)

Version 1.4 (at least) has old version of pg_stat_statements view:

postgres => \d pg_stat_statements
                    View "public.pg_stat_statements"
       Column        |       Type       | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
 userid              | oid              |           |          |
 dbid                | oid              |           |          |
 queryid             | bigint           |           |          |
 query               | text             |           |          |
 calls               | bigint           |           |          |
 total_time          | double precision |           |          |
 min_time            | double precision |           |          |
 max_time            | double precision |           |          |
 mean_time           | double precision |           |          |
 stddev_time         | double precision |           |          |
 rows                | bigint           |           |          |
 shared_blks_hit     | bigint           |           |          |
 shared_blks_read    | bigint           |           |          |
 shared_blks_dirtied | bigint           |           |          |
 shared_blks_written | bigint           |           |          |
 local_blks_hit      | bigint           |           |          |
 local_blks_read     | bigint           |           |          |
 local_blks_dirtied  | bigint           |           |          |
 local_blks_written  | bigint           |           |          |
 temp_blks_read      | bigint           |           |          |
 temp_blks_written   | bigint           |           |          |
 blk_read_time       | double precision |           |          |
 blk_write_time      | double precision |           |          |

I fixed the issue re-installing extension to update it to version 1.8:

postgres => drop extension pg_stat_statements;
DROP EXTENSION
postgres => create extension pg_stat_statements;
CREATE EXTENSION
postgres => SELECT * FROM pg_extension where extname = 'pg_stat_statements';
   oid    |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
----------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 35151774 | pg_stat_statements |       10 |        17014 | t              | 1.8        |           |
(1 row)

[houserx-jmcc]

Agree, just had to troubleshoot an issue where different databases had different versions of the extension set, which was quite annoying before I realized this was the root cause. I'd be open to putting up a PR for a fix if you're open to contributions here.

[def]

@houserx-jmcc sure, we're open to contributions