# pg\_stat\_monitor: see placeholders instead of actual query

**URL:** <https://forums.percona.com/t/pg-stat-monitor-see-placeholders-instead-of-actual-query/8152>\
**Category:** PMM 2.x\
**Tags:** pmm\
**Created:** [October 18, 2020, 11:36am UTC](https://forums.percona.com/t/pg-stat-monitor-see-placeholders-instead-of-actual-query/8152 "2020-10-18T11:36:08Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Shiwangini\_31](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/shiwangini_31/32/11040_2.png) [@Shiwangini\_31](https://forums.percona.com/u/Shiwangini_31)\
**Post date:** [October 18, 2020, 11:36am UTC](https://forums.percona.com/t/pg-stat-monitor-see-placeholders-instead-of-actual-query/8152/1 "2020-10-18T11:36:08Z")

</div>

I have started using pg\_stat\_monitor extension from : [https://www.percona.com/doc/percona-monitoring-and-management/2.x/manage/conf-postgres.html](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:

 ![image.png](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/1X/d8f460582d5d05ab67f091f152afc773f1682d18.png)

---

<div class="post-metadata">

**Author:** ![Ibrar](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ibrar/32/1378_2.png) [@Ibrar](https://forums.percona.com/u/Ibrar)\
**Post date:** [October 19, 2020, 11:52am UTC](https://forums.percona.com/t/pg-stat-monitor-see-placeholders-instead-of-actual-query/8152/2 "2020-10-19T11:52:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Shiwangini\_31](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/shiwangini_31/32/11040_2.png) [@Shiwangini\_31](https://forums.percona.com/u/Shiwangini_31)\
**Post date:** [October 19, 2020, 12:29pm UTC](https://forums.percona.com/t/pg-stat-monitor-see-placeholders-instead-of-actual-query/8152/3 "2020-10-19T12:29:21Z")

</div>

@Ibrar - it’s on.

![image.png](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/1X/6abd8cbcb948a7689d31d0f7ef529df251f82add.png)

Still it’s not showing actual parameters

---

<div class="post-metadata">

**Author:** ![Ibrar](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ibrar/32/1378_2.png) [@Ibrar](https://forums.percona.com/u/Ibrar)\
**Post date:** [October 19, 2020, 12:33pm UTC](https://forums.percona.com/t/pg-stat-monitor-see-placeholders-instead-of-actual-query/8152/4 "2020-10-19T12:33:25Z")

</div>

Please set it off and see new queries. The pg\_stat\_monitor.pgsm\_normalized\_query = on means show placeholders instead of actual values.

---

<div class="post-metadata">

**Author:** ![Shiwangini\_31](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/shiwangini_31/32/11040_2.png) [@Shiwangini\_31](https://forums.percona.com/u/Shiwangini_31)\
**Post date:** [October 19, 2020, 12:45pm UTC](https://forums.percona.com/t/pg-stat-monitor-see-placeholders-instead-of-actual-query/8152/5 "2020-10-19T12:45:33Z")

</div>

@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](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/1X/2e9ce14b3fdf1f3fc55b1ffbc50728db258c1a54.png)

 ![image.png](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/1X/fdc735d0cbff4f143f5f260cd451cfa96e0f74b7.png)

---

<div class="post-metadata">

**Author:** ![Ibrar](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ibrar/32/1378_2.png) [@Ibrar](https://forums.percona.com/u/Ibrar)\
**Post date:** [October 19, 2020, 12:53pm UTC](https://forums.percona.com/t/pg-stat-monitor-see-placeholders-instead-of-actual-query/8152/6 "2020-10-19T12:53:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Shiwangini\_31](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/shiwangini_31/32/11040_2.png) [@Shiwangini\_31](https://forums.percona.com/u/Shiwangini_31)\
**Post date:** [October 19, 2020, 1:11pm UTC](https://forums.percona.com/t/pg-stat-monitor-see-placeholders-instead-of-actual-query/8152/7 "2020-10-19T13:11:24Z")

</div>

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

 ![image.png](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/1X/283cc1c56a4c3195248fc965f8674acedaa4beb4.png)

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.

---

<div class="post-metadata">

**Author:** ![Ibrar](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ibrar/32/1378_2.png) [@Ibrar](https://forums.percona.com/u/Ibrar)\
**Post date:** [October 19, 2020, 1:26pm UTC](https://forums.percona.com/t/pg-stat-monitor-see-placeholders-instead-of-actual-query/8152/8 "2020-10-19T13:26:06Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![Shiwangini\_31](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/shiwangini_31/32/11040_2.png) [@Shiwangini\_31](https://forums.percona.com/u/Shiwangini_31)\
**Post date:** [October 19, 2020, 1:59pm UTC](https://forums.percona.com/t/pg-stat-monitor-see-placeholders-instead-of-actual-query/8152/9 "2020-10-19T13:59:14Z")

</div>

[output.txt](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/1X/a7547bdcb0b364e8affcc57c90e86b66662e3abb.txt)

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

---

<div class="post-metadata">

**Author:** ![Ibrar](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ibrar/32/1378_2.png) [@Ibrar](https://forums.percona.com/u/Ibrar)\
**Post date:** [October 19, 2020, 2:59pm UTC](https://forums.percona.com/t/pg-stat-monitor-see-placeholders-instead-of-actual-query/8152/10 "2020-10-19T14:59:46Z")

</div>

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/](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?

---

<div class="post-metadata">

**Author:** ![Shiwangini\_31](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/shiwangini_31/32/11040_2.png) [@Shiwangini\_31](https://forums.percona.com/u/Shiwangini_31)\
**Post date:** [October 20, 2020, 12:56pm UTC](https://forums.percona.com/t/pg-stat-monitor-see-placeholders-instead-of-actual-query/8152/11 "2020-10-20T12:56:16Z")

</div>

ok. Thanks!
