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