Re: Set query_id for query contained in utility statement
jian he <jian.universality@gmail.com>
From: jian he <jian.universality@gmail.com>
To: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2024-09-30T15:14:01Z
Lists: pgsql-hackers
PREPARE test_prepare_pgss1(int, int) AS select generate_series($1, $2);
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
EXECUTE test_prepare_pgss1 (1,2);
EXECUTE test_prepare_pgss1 (1,3);
SELECT toplevel, calls, query, queryid, rows FROM pg_stat_statements
ORDER BY query COLLATE "C", toplevel;
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
---the above works just fine. just for demo purpose
explain(verbose) EXECUTE test_prepare_pgss1(1, 2);
explain(verbose) EXECUTE test_prepare_pgss1(1, 3);
SELECT toplevel, calls, query, queryid, rows FROM pg_stat_statements
ORDER BY query COLLATE "C", toplevel;
toplevel | calls | query
| queryid | rows
----------+-------+------------------------------------------------------------------------+----------------------+------
f | 2 | PREPARE test_prepare_pgss1(int, int) AS select
generate_series($1, $2) | -3421048434214482065 | 0
t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
| 3366652201587963567 | 1
t | 0 | SELECT toplevel, calls, query, queryid, rows FROM
pg_stat_statements +| -6410939316132384446 | 0
| | ORDER BY query COLLATE "C", toplevel
| |
t | 1 | explain(verbose) EXECUTE test_prepare_pgss1(1, 2)
| 7618807962395633001 | 0
t | 1 | explain(verbose) EXECUTE test_prepare_pgss1(1, 3)
| -2281958002956676857 | 0
Is it possible to normalize top level utilities explain query, make
these two have the same queryid?
explain(verbose) EXECUTE test_prepare_pgss1(1, 2);
explain(verbose) EXECUTE test_prepare_pgss1(1, 3);
I guess this is a corner case.
Commits
-
Set query ID for inner queries of CREATE TABLE AS and DECLARE
- 6b652e6ce85a 18.0 landed
-
Track more precisely query locations for nested statements
- 499edb09741b 18.0 landed
-
pg_stat_statements: Add tests for nested queries with level tracking
- 45e0ba30fc40 18.0 landed
-
Fix race condition in COMMIT PREPARED causing orphaned 2PC files
- cf4401fe6cf5 18.0 cited