pg_stat_monitor: see placeholders instead of actual query

Question

I have started using pg_stat_monitor extension from : https://www.percona.com/doc/percona-monitoring-and-management/2.x/manage/conf-postgres.html with my postgresql database.I have a problem here. I though of using this so that I can see my query with actual parameters instead of placeholders. However, still I see my query with placeholder. Even though , I have set pg_stat_monitor.pgsm_normalized_query =0 and by default it's 0 only. Which means it has to show me query with actual parameters.

You can refer attached screenshot:

Answer 1

While the docs do say that the default is 0, the default is clearly not 0. Looks like a bug. After you set it to really be 0, did you execute the reset so you wouldn't be looking at old data?

But the entire pgsm_normalized_query=0 feature seems misbegotten. With it, the query is still normalized, it just that the text reported is the parameterized text of an arbitrary (the first?) execution of that normalized query in that bucket, but all the stats seem to have been aggregated across the normalized query even though the text reported is not normalized.

Also, it seems pretty flaky even within those constraints:

select bucket, calls, query  from pg_stat_monitor where query like 'UPDATE pgbench_accounts%' order by total_time desc;
 bucket | calls  |                                   query                                    
--------+--------+----------------------------------------------------------------------------
      3 | 95,109 | UPDATE pgbench_accounts SET abalance = abalance + 3008 WHERE aid = 
      4 | 92,216 | UPDATE pgbench_accounts SET abalance = abalance + 3186 WHERE aid = 421795
      8 | 93,381 | UPDATE pgbench_accounts SET abalance = abalance + 1998 WHERE aid = 
      5 | 90,735 | UPDATE pgbench_accounts SET abalance = abalance + -2438 WHERE aid = 574540
      9 | 81,484 | UPDATE pgbench_accounts SET abalance = abalance + 3594 WHERE aid = 
      6 | 86,158 | UPDATE pgbench_accounts SET abalance = abalance + -41 WHERE aid = 1
      0 | 90,503 | UPDATE pgbench_accounts SET abalance = abalance + -2664 WHERE aid =
      7 | 93,446 | UPDATE pgbench_accounts SET abalance = abalance + -3997 WHERE aid =
      1 | 55,960 | UPDATE pgbench_accounts SET abalance = abalance + 4804 WHERE aid = 

Why is the final parameter sometimes there and sometimes not?

Overall, I would take the caution on that page "pg_stat_monitor is beta software and currently unsupported." to heart.

Answer 2

Update On This:: I have checked further with Percona Forums - and I got to know:

It's not a bug. It's working as expected. Queries where user provide the values directly, in that case pg_stat_statment (A PostgreSQL tool) is replacing these values with placeholders, but in pg_stat_monitor (Percona Tool) they provide a way to select whether you want to see actual values or placeholders. if user use bind variable and bind the parameters, then system will not able to detect actual values.

For more information you can refer the discussion: https://forums.percona.com/discussion/comment/57277#Comment_57277