Re: refreshing query id for pg_stat_statements based on comment in sql - Mailing list pgsql-hackers

On Mon, Mar  7, 2022 at 09:42:26AM -0800, Zhihong Yu wrote:
> Hi,
> Currently the query id for pg_stat_statements gets calculated based on the
> parse nodes specifics. 
> This means that the user cannot add a comment to a SQL query to test something.
> (though some other RDBMS allows this practice).
> 
> Consider this use case: for query q, admin looks at stats and performs some
> optimization (without changing the query). Admin adds / modifies the comment
> for q - now the query becomes q'. If query id doesn't change, there still would
> be one row in pg_stat_statements which makes it difficult to gauge the
> effectiveness of the tuning.
> 
> I want to get opinion from the community whether adding / changing comment in
> SQL query should result in new query id for pg_stat_statements.

Uh, we don't have a parse node for comments, and I didn't think comments
were part of the query id, and my testing confirms that:

    psql -c "SET log_statement = 'all'" -c "select pg_sleep(10000) -- test1;" test
    psql -c "SET log_statement = 'all'" -c "select pg_sleep(10000) -- test2;" test

shows the comment in the logs:


    2022-03-07 19:02:19.509 EST [1075860] LOG:  statement: select pg_sleep(10000) -- test1;
    2022-03-07 19:02:24.389 EST [1075860] ERROR:  canceling statement due to user request
    2022-03-07 19:02:24.389 EST [1075860] STATEMENT:  select pg_sleep(10000) -- test1;
    2022-03-07 19:02:27.029 EST [1075893] LOG:  statement: select pg_sleep(10000) -- test2;
    2022-03-07 19:02:47.915 EST [1075893] ERROR:  canceling statement due to user request
    2022-03-07 19:02:47.915 EST [1075893] STATEMENT:  select pg_sleep(10000) -- test2;

and I see the same query_id for both:

    test=> select query, query_id from pg_stat_activity;
                         query                     |       query_id
    -----------------------------------------------+----------------------
                                                   |
                                                   |
-->     select pg_sleep(10000) -- test1;              |  2920433178127795318
     select query, query_id from pg_stat_activity; | -8032661921273433383
                                                   |
                                                   |
                                                   |
    (7 rows)
    
    test=> select query, query_id from pg_stat_activity;
                         query                     |       query_id
    -----------------------------------------------+----------------------
                                                   |
                                                   |
-->     select pg_sleep(10000) -- test2;              |  2920433178127795318
     select query, query_id from pg_stat_activity; | -8032661921273433383

I think you need to show us the problem you are having.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.