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 <br...@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
