pg_stat_monitor: see placeholders instead of actual query

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:

1 - Can you please see the value using

show pg_stat_monitor.pgsm_normalized_query

2 - Secondly, all the queries logged after setting pg_stat_monitor.pgsm_normalized_query will show you the actual values.

@Ibrar - it’s on.

image.png

Still it’s not showing actual parameters

Please set it off and see new queries. The pg_stat_monitor.pgsm_normalized_query = on means show placeholders instead of actual values.

@Ibrar - I have set it to false and restarted db services. Also, after this - I executed pg_stat_monitor_reset(); and then again started looking for ongoing queries. Still it’s showing me parameters :

image.png

If you set that to false and restarted db services it will won’t work. if you want to restart the db; set that off in postgresql.conf file which is in $PGDATA directory. In that way it will set permanently and wont reset on restart.

@Ibrar - please tell me if I’m doing anything wrong. This time : I modified postgresql.conf file like below:

Then, restarted db services. Then, executed pg_stat_monitor_reset(). And, again started looking for query parameters. Still I see placeholders only. If you need screenshots or any additional information - I’m happy to share.

Please send me output of these queries.

0 - postgres=# select pg_stat_monitor_version();

1 - postgres=# show pg_stat_monitor.pgsm_normalized_query;

2 - postgres=# select * from pg_stat_monitor_reset();

3 - postgres=# select 1;

4 - postgres=# select query from pg_stat_monitor;

output.txt

@lbrar The output has been attached to this file. Please let me know if need any more info.

Thanks @Shiwangini_31,

I figured out the issue, the system is working as expected. I can see actual values in your sql file in some places. There are some queries which contains placeholders.

There are two types of queries,

1 - 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) we provide a way to select whether you want to see actual values or placeholders. here is the example of that.

postgres=# set pg_stat_monitor.pgsm_normalized_query = off;

postgres=# select 1,2; – test query

?column? | ?column?

----------±---------

1 |    2

(1 row)

postgres=# select query from pg_stat_monitor;

       query            

select * from pg_stat_monitor_reset()

select query from pg_stat_monitor

select $1,$2 <<<--------------------------------------------------------- 1,2 replace with placeholder

postgres=# set pg_stat_monitor.pgsm_normalized_query = off;

postgres=# select 1,2; – test query

?column? | ?column?

----------±---------

1 |    2

(1 row)

postgres=# select query from pg_stat_monitor;

       query            

select query from pg_stat_monitor

select 1,2; <<<< -------------------------------------------------- actual values.

set pg_stat_monitor.pgsm_normalized_query = off

select * from pg_stat_monitor_reset()

2 - In second case if user use bind variable and bind the parameters, then system will not able to detect actual values. You can open that Jira cases for future enhancements.

Here is the example (http://zetcode.com/db/postgresqlc/).

[code from the link]

char str[LEN];

snprintf(str, LEN, “%d”, rowId);

paramValues[0] = str;

PGconn *conn = PQconnectdb(“host=127.0.0.1 user=vagrant dbname=postgres”);

if (PQstatus(conn) == CONNECTION_BAD) {

fprintf(stderr, "Connection to database failed: %s\n",

  PQerrorMessage(conn));

do_exit(conn);

}

char *stm = “SELECT * FROM Cars WHERE Id=$1”; <<-------------------------- User explicitly used placeholder and will provide value using bind.

PGresult *res = PQexecParams(conn, stm, 1, NULL, paramValues,

NULL, NULL, 0);   

if (PQresultStatus(res) != PGRES_TUPLES_OK) {

printf("No data retrieved\n");     

PQclear(res);

do_exit(conn);

}

Now try to see the queries.

postgres=# select query from pg_stat_monitor;

       query            

select query from pg_stat_monitor

select 1,2;

set pg_stat_monitor.pgsm_normalized_query = off

SELECT * FROM Cars WHERE Id=$1 <<<---------------------------- Bind variable are used.

select * from pg_stat_monitor_reset()

select query from pg_stat_monitor

(6 rows)

Hope its clear now?

ok. Thanks!