Re: refreshing query id for pg_stat_statements based on comment in sql

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Zhihong Yu <zyu(at)yugabyte(dot)com>
Cc: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: refreshing query id for pg_stat_statements based on comment in sql
Date: 2022-03-08 00:06:08
Message-ID: Yiad8CAOrcB8DFAI@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2022-03-08 01:00:07 Re: Time to drop plpython2?
Previous Message Joseph Koshakow 2022-03-08 00:00:44 Re: Fix overflow in DecodeInterval