Default query error

[doublethink13]

Hello,

This query was giving me the following error:

2021-06-29 20:04:46.870 UTC [331] ERROR:  column "total_time" does not exist at character 48
2021-06-29 20:04:46.870 UTC [331] STATEMENT:  SELECT t2.rolname, t3.datname, queryid, calls, total_time / 1000 as total_time_seconds, min_time / 1000 as min_time_seconds, max_time / 1000 as max_time_seconds, mean_time / 1000 as mean_time_seconds, stddev_time / 1000 as stddev_time_seconds, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time / 1000 as blk_read_time_seconds, blk_write_time / 1000 as blk_write_time_seconds FROM pg_stat_statements t1 JOIN pg_roles t2 ON (t1.userid=t2.oid) JOIN pg_database t3 ON (t1.dbid=t3.oid) WHERE t2.rolname != 'rdsadmin'

I changed it to this: SELECT t2.rolname, t3.datname, queryid, calls, total_exec_time / 1000 as total_time_seconds, min_exec_time / 1000 as min_time_seconds, max_exec_time / 1000 as max_time_seconds, mean_exec_time / 1000 as mean_time_seconds, stddev_exec_time / 1000 as stddev_time_seconds, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time / 1000 as blk_read_time_seconds, blk_write_time / 1000 as blk_write_time_seconds FROM pg_stat_statements t1 JOIN pg_roles t2 ON (t1.userid=t2.oid) JOIN pg_database t3 ON (t1.dbid=t3.oid) WHERE t2.rolname != 'rdsadmin'.

Now it is working as expected. Do you need/want me to open a pull request?

Thank you!

[suveshmathur]

It'll be tricky as in postgres older release, the pg_stat_statements columns are according to the original sql. The only way to make it work for mixed pg version is to have condition.

from pg9.6
Column | Type | Modifiers
---------------------+------------------+-----------
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 |

[Harkishen-Singh]

@eduardoaemunoz I think we can use a coalase(total_exec_time, total_time)/1000 or something around this, in the query and that should be fine. Please make a PR with that change if possible.

[Harkishen-Singh]

I think #541 is related too.

[sysadmind]

I'm pretty sure that coalesce will error if the column doesn't exist. The exporter should know which version of postgres is running on a server, so it should be possible to swap the queries around if we know in which version the table changed.

[Harkishen-Singh]

Ah yes. coalase will not work. Off mind, sorry. We can do something like a case as said above.