On Mon, Aug 5, 2024 at 3:19 PM Anthonin Bonnefoy <anthonin.bonne...@datadoghq.com> wrote: > > I've realised my initial approach was wrong, calling the post parse > for all nested queries in analyze.c prevents extension like pgss to > correctly track the query's nesting level. > > I've changed the approach to replicate what's done in ExplainQuery to > both CreateTableAs and DeclareCursor: Jumble the query contained by > the utility statement and call the post parse hook before it is > planned or executed. Additionally, explain's nested query can itself > be a CreateTableAs or DeclareCursor which also needs to be jumbled. > The issue is visible when explaining a CreateTableAs or DeclareCursor > Query, the queryId is missing despite the verbose. > > EXPLAIN (verbose) create table test_t as select 1; > QUERY PLAN > ------------------------------------------ > Result (cost=0.00..0.01 rows=1 width=4) > Output: 1 > > Post patch, the query id is correctly displayed. > > EXPLAIN (verbose) create table test_t as select 1; > QUERY PLAN > ------------------------------------------ > Result (cost=0.00..0.01 rows=1 width=4) > Output: 1 > Query Identifier: 2800308901962295548 >
play with pg_stat_statements. settings: name | setting -----------------------------------+--------- pg_stat_statements.max | 5000 pg_stat_statements.save | on pg_stat_statements.track | all pg_stat_statements.track_planning | on pg_stat_statements.track_utility | on SELECT pg_stat_statements_reset(); select 1; select 2; SELECT queryid, left(query, 60),plans, calls, rows FROM pg_stat_statements ORDER BY calls DESC LIMIT 5; returns: queryid | left | plans | calls | rows ----------------------+--------------------------------------------------------------+-------+-------+------ 2800308901962295548 | select $1 | 2 | 2 | 2 The output is what we expect. now after applying your patch. SELECT pg_stat_statements_reset(); EXPLAIN (verbose) create table test_t as select 1; EXPLAIN (verbose) create table test_t as select 2; SELECT queryid, left(query, 60),plans, calls, rows FROM pg_stat_statements ORDER BY calls DESC LIMIT 5; the output is: queryid | left | plans | calls | rows ----------------------+--------------------------------------------------------------+-------+-------+------ 2800308901962295548 | EXPLAIN (verbose) create table test_t as select 1; | 2 | 2 | 0 2093602470903273926 | EXPLAIN (verbose) create table test_t as select $1 | 0 | 2 | 0 -2694081619397734273 | SELECT pg_stat_statements_reset() | 0 | 1 | 1 2643570658797872815 | SELECT queryid, left(query, $1),plans, calls, rows FROM pg_s | 1 | 0 | 0 "EXPLAIN (verbose) create table test_t as select 1;" called twice, is that what we expect? should first row, the normalized query becomes EXPLAIN (verbose) create table test_t as select $1; ?