Re: Set query_id for query contained in utility statement - Mailing list pgsql-hackers

On Mon, Aug 5, 2024 at 3:19 PM Anthonin Bonnefoy
<anthonin.bonnefoy@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;

?