pg_stat_monitor: see placeholders instead of actual query

Percona Monitoring and Management (PMM) PMM 2.x
10

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?