Not the answer you need?
Register and ask your own question!

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:


Tagged:

Answers

  • IbrarIbrar Percona Staff Role

    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.

  • Shiwangini_31Shiwangini_31 Current User Role Contributor
    edited October 19

    @Ibrar - it's on.

    Still it's not showing actual parameters

  • IbrarIbrar Percona Staff Role

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

  • Shiwangini_31Shiwangini_31 Current User Role Contributor
    edited October 19

    @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 :



  • IbrarIbrar Percona Staff Role

    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.

  • Shiwangini_31Shiwangini_31 Current User Role Contributor

    @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.

  • IbrarIbrar Percona Staff Role

    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;

     

  • Shiwangini_31Shiwangini_31 Current User Role Contributor

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

  • IbrarIbrar Percona Staff Role

    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?

  • Shiwangini_31Shiwangini_31 Current User Role Contributor
    edited October 20

    ok. Thanks!

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.