pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pg_stat_statements stores WITH HOLD cursor query with literals
18+ messages / 4 participants
[nested] [flat]

Commits from this thread:
  2026-10-27 23bf901fa5  pg_stat_statements: Fix toplevel tracking across nesting levels
  2026-10-27 e5d5d1ad1e  Add custom extension states to QueryDesc
* pg_stat_statements stores WITH HOLD cursor query with literals
@ 2026-10-12 20:10 Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Gaurav Singh @ 2026-10-12 20:10 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org

Hi,

On PostgreSQL 19beta1, pg_stat_statements can store the query of a
WITH HOLD cursor as a separate top-level entry, without normalizing
its constants.

Repro (shared_preload_libraries = 'pg_stat_statements'):

  CREATE EXTENSION pg_stat_statements;
  LOAD 'auto_explain';
  SET auto_explain.log_min_duration = 0;
  SELECT pg_stat_statements_reset();
  DECLARE c CURSOR WITH HOLD FOR SELECT 1;
  CLOSE c;
  SELECT toplevel, calls, query FROM pg_stat_statements
  WHERE query LIKE 'DECLARE%';

   toplevel | calls |                  query
  ----------+-------+------------------------------------------
   t        |     1 | DECLARE c CURSOR WITH HOLD FOR SELECT $1
   t        |     1 | DECLARE c CURSOR WITH HOLD FOR SELECT 1;
Without auto_explain, only the first row appears. Without auto_explain
but with pg_stat_statements.track = 'all', the second row appears too.

The cursor's query starts inside DECLARE (nesting level 1), but a
WITH HOLD cursor finishes it at commit (nesting level 0).
pgss_ExecutorEnd stores any query that has a queryId and query_instr,
even when pg_stat_statements did not request query_instr itself or
requested it at another nesting level. It then finds no normalized
entry, so it stores the raw text.

Thanks,
Gaurav Singh

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
@ 2026-10-12 21:50 ` Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Sami Imseih @ 2026-10-12 21:50 UTC (permalink / raw)
  To: Gaurav Singh <gaurav.singh@yugabyte.com>; Michael Paquier <michael@paquier.xyz>; +Cc: pgsql-bugs@lists.postgresql.org

Hi,

> Without auto_explain, only the first row appears. Without auto_explain
> but with pg_stat_statements.track = 'all', the second row appears too.
>
> The cursor's query starts inside DECLARE (nesting level 1), but a
> WITH HOLD cursor finishes it at commit (nesting level 0).
> pgss_ExecutorEnd stores any query that has a queryId and query_instr,
> even when pg_stat_statements did not request query_instr itself or
> requested it at another nesting level. It then finds no normalized
> entry, so it stores the raw text.

This was also discussed in https://commitfest.postgresql.org/patch/6342/.

The core issue is that nesting level is currently backend-local,
while a QueryDesc can outlive the executor/utility nesting level at which it
was created.  WITH HOLD cursors are one example: the cursor query starts
inside DECLARE, but can finish later when the cursor is implicitly closed.

I got hung up in the earlier thread because I was not sure whether the best
path was for core to track nesting_level directly, or to let extensions attach
their own nesting level to a QueryDesc.  I now think the latter is the smaller
and safer fix.

The patch I am testing adds a nesting_level field to QueryDesc, initialized to
-1, and exposes a small API so extensions can set it once:

static inline void
SetQueryDescNestingLevel(QueryDesc *queryDesc, int nesting_level)
{
          if (queryDesc->nesting_level == -1)
                  queryDesc->nesting_level = nesting_level;
}

pg_stat_statements then records the nesting level when it requests query-level
instrumentation, and later uses the saved QueryDesc level in ExecutorEnd
instead of the current backend-local level.  auto_explain can also set the
same field when it requests query-level instrumentation; the helper preserves
the first value if multiple extensions are loaded.

Adding Michael for his thoughts on this also.

--
Sami Imseih
Amazon Web Services (AWS)






^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
@ 2026-10-13 08:14   ` Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Gaurav Singh @ 2026-10-13 08:14 UTC (permalink / raw)
  To: Sami Imseih <samimseih.pg@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; pgsql-bugs@lists.postgresql.org

Hi Sami,

Thanks for the pointer to the earlier thread.

One concern with a single shared field: extensions count nesting
differently. auto_explain has no ProcessUtility hook, so when DECLARE
starts the cursor's query, auto_explain is at level 0 while
pg_stat_statements is at level 1.

In my first repro (track = top), pg_stat_statements does not request
instrumentation at level 1, so only auto_explain sets the field, to 0.
pg_stat_statements would then read 0 in ExecutorEnd and still store
the cursor query as top level. With track = 'all' both set it, and
which value wins depends on which extension's hook runs first.

So pg_stat_statements probably needs to know whether it set the level
itself, and skip the query when it did not. Would a per-extension slot,
or a single nesting level tracked by core, work better here?

Thanks,
Gaurav Singh


On Tue, Oct 13, 2026 at 3:20 AM Sami Imseih <samimseih.pg@gmail.com> wrote:

> Hi,
>
> > Without auto_explain, only the first row appears. Without auto_explain
> > but with pg_stat_statements.track = 'all', the second row appears too.
> >
> > The cursor's query starts inside DECLARE (nesting level 1), but a
> > WITH HOLD cursor finishes it at commit (nesting level 0).
> > pgss_ExecutorEnd stores any query that has a queryId and query_instr,
> > even when pg_stat_statements did not request query_instr itself or
> > requested it at another nesting level. It then finds no normalized
> > entry, so it stores the raw text.
>
> This was also discussed in https://commitfest.postgresql.org/patch/6342/.
>
> The core issue is that nesting level is currently backend-local,
> while a QueryDesc can outlive the executor/utility nesting level at which
> it
> was created.  WITH HOLD cursors are one example: the cursor query starts
> inside DECLARE, but can finish later when the cursor is implicitly closed.
>
> I got hung up in the earlier thread because I was not sure whether the best
> path was for core to track nesting_level directly, or to let extensions
> attach
> their own nesting level to a QueryDesc.  I now think the latter is the
> smaller
> and safer fix.
>
> The patch I am testing adds a nesting_level field to QueryDesc,
> initialized to
> -1, and exposes a small API so extensions can set it once:
>
> static inline void
> SetQueryDescNestingLevel(QueryDesc *queryDesc, int nesting_level)
> {
>           if (queryDesc->nesting_level == -1)
>                   queryDesc->nesting_level = nesting_level;
> }
>
> pg_stat_statements then records the nesting level when it requests
> query-level
> instrumentation, and later uses the saved QueryDesc level in ExecutorEnd
> instead of the current backend-local level.  auto_explain can also set the
> same field when it requests query-level instrumentation; the helper
> preserves
> the first value if multiple extensions are loaded.
>
> Adding Michael for his thoughts on this also.
>
> --
> Sami Imseih
> Amazon Web Services (AWS)
>

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
@ 2026-10-13 21:05     ` Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Sami Imseih @ 2026-10-13 21:05 UTC (permalink / raw)
  To: Gaurav Singh <gaurav.singh@yugabyte.com>; +Cc: Michael Paquier <michael@paquier.xyz>; pgsql-bugs@lists.postgresql.org

> One concern with a single shared field: extensions count nesting
> differently. auto_explain has no ProcessUtility hook, so when DECLARE
> starts the cursor's query, auto_explain is at level 0 while
> pg_stat_statements is at level 1.
>
> In my first repro (track = top), pg_stat_statements does not request
> instrumentation at level 1, so only auto_explain sets the field, to 0.
> pg_stat_statements would then read 0 in ExecutorEnd and still store
> the cursor query as top level. With track = 'all' both set it, and
> which value wins depends on which extension's hook runs first.

Good point, a single field can't hold two different ideas of what the
nesting level is.

> So pg_stat_statements probably needs to know whether it set the level
> itself, and skip the query when it did not. Would a per-extension slot,
> or a single nesting level tracked by core, work better here?

per-extension slot sounds a bit too heavy-handed for me. And I'd rather
not have core track the nesting level either, since core would have to
pick one definition for every extension, and extensions can
legitimately disagree on what counts as nesting.

So the attached v1 goes the simpler way. It adds a field called
start_nesting_level, which can be set at ExecutorStart by any extension
that counts nesting levels the way pg_stat_statements does. The comment
on the field spells out what that means, and an extension that counts
differently, like auto_explain, should just not set it, so v1 doesn't
change auto_explain at all.

There is precedent for this kind of field. Query.queryId and
PlannedStmt.planId are both core fields that "can be set by plugins",
and it's up to the plugins that set them to agree on what they mean.

Since every extension that sets it computes the same value, it doesn't
matter who sets it or in what order. And in your track = top case,
nobody sets the field, so pg_stat_statements skips the query.

v1 is a sketch of that without any tests yet. I will add if there is
agreement on
this being the way forward. This seems like the most straightforward way.
Maybe others have a different idea.

--
Sami Imseih
Amazon Web Services (AWS)

Attachments:

v1-0001-Allow-extensions-to-set-nesting_level.patch (application/octet-stream, 5.5K) download From 60a686d38d7becb0602bc88f49bd2d7d841712cd Mon Sep 17 00:00:00 2001 From: Sami Imseih <samimseih.pg@gmail.com> Date: Mon, 12 Oct 2026 23:09:43 +0000 Subject: [PATCH v1 1/1] Allow extensions to set nesting_level --- .../pg_stat_statements/pg_stat_statements.c | 20 +++++++++++++++++-- src/backend/tcop/pquery.c | 1 + src/include/executor/execdesc.h | 20 +++++++++++++++++++ 3 files changed, 39 insertions(+), 2 deletions(-) diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 4562222c9fc..8e380d06467 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -356,6 +356,7 @@ static void pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, DestReceiver *dest, QueryCompletion *qc); static void pgss_store(const char *query, int64 queryId, int query_location, int query_len, + bool toplevel, pgssStoreKind kind, double total_time, uint64 rows, const BufferUsage *bufusage, @@ -866,6 +867,7 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query, const JumbleState *jst query->queryId, query->stmt_location, query->stmt_len, + nesting_level == 0, PGSS_INVALID, 0, 0, @@ -948,6 +950,7 @@ pgss_planner(Query *parse, parse->queryId, parse->stmt_location, parse->stmt_len, + nesting_level == 0, PGSS_PLAN, INSTR_TIME_GET_MILLISEC(duration), 0, @@ -1001,6 +1004,9 @@ pgss_ExecutorStart(QueryDesc *queryDesc, int eflags) { /* Request all summary instrumentation, i.e. timing, buffers and WAL */ queryDesc->query_instr_options |= INSTRUMENT_ALL; + + /* Remember the nesting level for ExecutorEnd */ + queryDesc->start_nesting_level = nesting_level; } if (prev_ExecutorStart) @@ -1059,13 +1065,21 @@ pgss_ExecutorEnd(QueryDesc *queryDesc) { int64 queryId = queryDesc->plannedstmt->queryId; + /* + * Use the nesting level saved at ExecutorStart rather than the current + * one, since the query may finish at a different level, as for a cursor + * closed at COMMIT. If no level was saved, the query was not tracked at + * ExecutorStart. + */ if (queryId != INT64CONST(0) && queryDesc->query_instr && - pgss_enabled(nesting_level)) + queryDesc->start_nesting_level >= 0 && + pgss_enabled(queryDesc->start_nesting_level)) { pgss_store(queryDesc->sourceText, queryId, queryDesc->plannedstmt->stmt_location, queryDesc->plannedstmt->stmt_len, + queryDesc->start_nesting_level == 0, PGSS_EXEC, INSTR_TIME_GET_MILLISEC(queryDesc->query_instr->total), queryDesc->estate->es_total_processed, @@ -1202,6 +1216,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, saved_queryId, saved_stmt_location, saved_stmt_len, + nesting_level == 0, PGSS_EXEC, INSTR_TIME_GET_MILLISEC(duration), rows, @@ -1267,6 +1282,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, static void pgss_store(const char *query, int64 queryId, int query_location, int query_len, + bool toplevel, pgssStoreKind kind, double total_time, uint64 rows, const BufferUsage *bufusage, @@ -1310,7 +1326,7 @@ pgss_store(const char *query, int64 queryId, key.userid = GetUserId(); key.dbid = MyDatabaseId; key.queryid = queryId; - key.toplevel = (nesting_level == 0); + key.toplevel = toplevel; /* Lookup the hash table entry with shared lock. */ LWLockAcquire(&pgss->lock.lock, LW_SHARED); diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c index fa2ef20f178..8d72c870ec1 100644 --- a/src/backend/tcop/pquery.c +++ b/src/backend/tcop/pquery.c @@ -87,6 +87,7 @@ CreateQueryDesc(PlannedStmt *plannedstmt, qd->queryEnv = queryEnv; qd->instrument_options = instrument_options; /* instrumentation wanted? */ qd->query_instr_options = 0; + qd->start_nesting_level = -1; /* null these fields until set by ExecutorStart */ qd->tupDesc = NULL; diff --git a/src/include/executor/execdesc.h b/src/include/executor/execdesc.h index 37c2576e4bc..e946343e7e7 100644 --- a/src/include/executor/execdesc.h +++ b/src/include/executor/execdesc.h @@ -45,6 +45,26 @@ typedef struct QueryDesc int query_instr_options; /* OR of InstrumentOption flags for * query_instr */ + /* + * Nesting level at which this query was started, or -1 if not set. + * + * A query can end at a different nesting level than it started at. For + * example, a WITH HOLD cursor declared at top level is closed at COMMIT. + * An extension that needs the starting level in ExecutorEnd can save it + * here from its ExecutorStart hook. + * + * A statement sent by the client is at level 0, and so is one run by + * EXECUTE. A query started by another statement, while that statement is + * being planned, executed or processed as a utility command, is one level + * deeper. For example, the query of a top-level DECLARE CURSOR, EXPLAIN + * or CREATE TABLE AS is at level 1, and so is a query run by a function + * called from a top-level statement. This is how, for example, + * pg_stat_statements counts levels. + * + * An extension that counts levels differently must not set this field. + */ + int start_nesting_level; + /* These fields are set by ExecutorStart */ TupleDesc tupDesc; /* descriptor for result tuples */ EState *estate; /* executor's query-wide state */ -- 2.54.0 (Apple Git-157)
^ permalink raw reply [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
@ 2026-10-14 02:02       ` Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 06:45         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Sami Imseih @ 2026-10-14 02:02 UTC (permalink / raw)
  To: Gaurav Singh <gaurav.singh@yugabyte.com>; +Cc: Michael Paquier <michael@paquier.xyz>; pgsql-bugs@lists.postgresql.org

> v1 is a sketch of that without any tests yet. I will add if there is
> agreement on
> this being the way forward. This seems like the most straightforward way.
> Maybe others have a different idea.

Attached v2 is cleaned up with a test.


--
Sami Imseih
Amazon Web Services (AWS)

Attachments:

v2-0001-pg_stat_statements-Fix-toplevel-tracking-across-n.patch (application/octet-stream, 8.8K) download From 0a2c631a3f2fc4013b5a2960c6a46e0b4b224568 Mon Sep 17 00:00:00 2001 From: Sami Imseih <samimseih.pg@gmail.com> Date: Mon, 12 Oct 2026 23:09:43 +0000 Subject: [PATCH v2 1/1] pg_stat_statements: Fix toplevel tracking across nesting levels pg_stat_statements decides whether a query is toplevel from the nesting level at the time each hook runs. A QueryDesc can outlive the level it started at. For example, a cursor's query starts at level 1, inside DECLARE, but if the cursor is closed implicitly at COMMIT, ExecutorEnd runs at level 0. The entry created for the query at parse analysis has toplevel = false, so ExecutorEnd misses it and stores a duplicate with the raw query text and toplevel = true. The same happens with track = top when another extension such as auto_explain requests query instrumentation. ExecutorEnd stores the query even though ExecutorStart did not track it. Add start_nesting_level to QueryDesc, initialized to -1. pg_stat_statements sets it in ExecutorStart when it tracks a query. ExecutorEnd then uses it in place of the current level and skips the query if it is unset. Other extensions may set it too, as long as they count nesting levels the same way pg_stat_statements does. No backpatch due to lack of widespread reports. Reported-by: Gaurav Singh <gaurav.singh@yugabyte.com> Author: Sami Imseih <samimseih@gmail.com> Discussion: https://postgr.es/m/CAEcQ1bZrgwKsauC=03otebkLk77Zc_hYNjhTP3OUdpXs++u+BA@mail.gmail.com Discussion: https://postgr.es/m/CAA5RZ0v281uwSudcFrY8BymSRDM4J6PAHfzqg_9R+i7zWbTb2A@mail.gmail.com --- .../expected/level_tracking.out | 25 +++++++++++++++++++ .../pg_stat_statements/pg_stat_statements.c | 18 +++++++++++-- .../pg_stat_statements/sql/level_tracking.sql | 11 ++++++++ src/backend/tcop/pquery.c | 1 + src/include/executor/execdesc.h | 14 +++++++++++ 5 files changed, 67 insertions(+), 2 deletions(-) diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out index 832d65e97ca..ccebf8b2960 100644 --- a/contrib/pg_stat_statements/expected/level_tracking.out +++ b/contrib/pg_stat_statements/expected/level_tracking.out @@ -1151,6 +1151,31 @@ SELECT toplevel, calls, query FROM pg_stat_statements t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (7 rows) +-- DECLARE CURSOR, all-level tracking with implicit close. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +BEGIN; +DECLARE FOOCUR CURSOR FOR SELECT * FROM stats_track_tab WHERE x = 1; +FETCH FORWARD 1 FROM foocur; + x +--- +(0 rows) + +COMMIT; +SELECT toplevel, calls, query FROM pg_stat_statements + WHERE query LIKE 'DECLARE FOOCUR%' + ORDER BY query COLLATE "C", toplevel; + toplevel | calls | query +----------+-------+----------------------------------------------------------------------- + t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * FROM stats_track_tab WHERE x = $1 + f | 1 | DECLARE FOOCUR CURSOR FOR SELECT * FROM stats_track_tab WHERE x = $1; +(2 rows) + -- DECLARE CURSOR, top-level tracking. SET pg_stat_statements.track = 'top'; SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 4562222c9fc..87d11f79717 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -356,6 +356,7 @@ static void pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, DestReceiver *dest, QueryCompletion *qc); static void pgss_store(const char *query, int64 queryId, int query_location, int query_len, + bool toplevel, pgssStoreKind kind, double total_time, uint64 rows, const BufferUsage *bufusage, @@ -866,6 +867,7 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query, const JumbleState *jst query->queryId, query->stmt_location, query->stmt_len, + nesting_level == 0, PGSS_INVALID, 0, 0, @@ -948,6 +950,7 @@ pgss_planner(Query *parse, parse->queryId, parse->stmt_location, parse->stmt_len, + nesting_level == 0, PGSS_PLAN, INSTR_TIME_GET_MILLISEC(duration), 0, @@ -1001,6 +1004,9 @@ pgss_ExecutorStart(QueryDesc *queryDesc, int eflags) { /* Request all summary instrumentation, i.e. timing, buffers and WAL */ queryDesc->query_instr_options |= INSTRUMENT_ALL; + + /* Remember the nesting level for ExecutorEnd; see execdesc.h */ + queryDesc->start_nesting_level = nesting_level; } if (prev_ExecutorStart) @@ -1059,13 +1065,19 @@ pgss_ExecutorEnd(QueryDesc *queryDesc) { int64 queryId = queryDesc->plannedstmt->queryId; + /* + * Use the nesting level saved at ExecutorStart, since the query may end + * at a different level (e.g. a cursor closed at COMMIT). + */ if (queryId != INT64CONST(0) && queryDesc->query_instr && - pgss_enabled(nesting_level)) + queryDesc->start_nesting_level >= 0 && + pgss_enabled(queryDesc->start_nesting_level)) { pgss_store(queryDesc->sourceText, queryId, queryDesc->plannedstmt->stmt_location, queryDesc->plannedstmt->stmt_len, + queryDesc->start_nesting_level == 0, PGSS_EXEC, INSTR_TIME_GET_MILLISEC(queryDesc->query_instr->total), queryDesc->estate->es_total_processed, @@ -1202,6 +1214,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, saved_queryId, saved_stmt_location, saved_stmt_len, + nesting_level == 0, PGSS_EXEC, INSTR_TIME_GET_MILLISEC(duration), rows, @@ -1267,6 +1280,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, static void pgss_store(const char *query, int64 queryId, int query_location, int query_len, + bool toplevel, pgssStoreKind kind, double total_time, uint64 rows, const BufferUsage *bufusage, @@ -1310,7 +1324,7 @@ pgss_store(const char *query, int64 queryId, key.userid = GetUserId(); key.dbid = MyDatabaseId; key.queryid = queryId; - key.toplevel = (nesting_level == 0); + key.toplevel = toplevel; /* Lookup the hash table entry with shared lock. */ LWLockAcquire(&pgss->lock.lock, LW_SHARED); diff --git a/contrib/pg_stat_statements/sql/level_tracking.sql b/contrib/pg_stat_statements/sql/level_tracking.sql index 003efb8184b..cc14a9808dd 100644 --- a/contrib/pg_stat_statements/sql/level_tracking.sql +++ b/contrib/pg_stat_statements/sql/level_tracking.sql @@ -262,6 +262,17 @@ COMMIT; SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- DECLARE CURSOR, all-level tracking with implicit close. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +BEGIN; +DECLARE FOOCUR CURSOR FOR SELECT * FROM stats_track_tab WHERE x = 1; +FETCH FORWARD 1 FROM foocur; +COMMIT; +SELECT toplevel, calls, query FROM pg_stat_statements + WHERE query LIKE 'DECLARE FOOCUR%' + ORDER BY query COLLATE "C", toplevel; + -- DECLARE CURSOR, top-level tracking. SET pg_stat_statements.track = 'top'; SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c index fa2ef20f178..8d72c870ec1 100644 --- a/src/backend/tcop/pquery.c +++ b/src/backend/tcop/pquery.c @@ -87,6 +87,7 @@ CreateQueryDesc(PlannedStmt *plannedstmt, qd->queryEnv = queryEnv; qd->instrument_options = instrument_options; /* instrumentation wanted? */ qd->query_instr_options = 0; + qd->start_nesting_level = -1; /* null these fields until set by ExecutorStart */ qd->tupDesc = NULL; diff --git a/src/include/executor/execdesc.h b/src/include/executor/execdesc.h index 37c2576e4bc..f07f35d9dc0 100644 --- a/src/include/executor/execdesc.h +++ b/src/include/executor/execdesc.h @@ -45,6 +45,20 @@ typedef struct QueryDesc int query_instr_options; /* OR of InstrumentOption flags for * query_instr */ + /* + * Nesting level at which this query started, or -1 if not set. An + * extension may set this from its ExecutorStart hook and read it back at + * ExecutorEnd, which can run at a different level. For example, a + * cursor's query starts inside DECLARE but may end at COMMIT. + * + * The level counts as pg_stat_statements does: 0 for a statement sent by + * the client, including EXECUTE of a prepared statement; one deeper for + * each query run by another statement or by a function. All extensions + * that set this field must use that definition; one that counts + * differently must leave it alone. + */ + int start_nesting_level; + /* These fields are set by ExecutorStart */ TupleDesc tupDesc; /* descriptor for result tuples */ EState *estate; /* executor's query-wide state */ -- 2.54.0 (Apple Git-157)
^ permalink raw reply [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
@ 2026-10-14 06:45         ` Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-14 14:41           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Gaurav Singh @ 2026-10-14 06:45 UTC (permalink / raw)
  To: Sami Imseih <samimseih.pg@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; pgsql-bugs@lists.postgresql.org

LGTM! Thank you for the patch.

Warm Regards,
Gaurav Singh



On Wed, Oct 14, 2026 at 7:33 AM Sami Imseih <samimseih.pg@gmail.com> wrote:

> > v1 is a sketch of that without any tests yet. I will add if there is
> > agreement on
> > this being the way forward. This seems like the most straightforward way.
> > Maybe others have a different idea.
>
> Attached v2 is cleaned up with a test.
>
>
> --
> Sami Imseih
> Amazon Web Services (AWS)
>

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 06:45         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
@ 2026-10-14 14:41           ` Sami Imseih <samimseih.pg@gmail.com>
  2026-10-15 05:06             ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Sami Imseih @ 2026-10-14 14:41 UTC (permalink / raw)
  To: Gaurav Singh <gaurav.singh@yugabyte.com>; +Cc: Michael Paquier <michael@paquier.xyz>; pgsql-bugs@lists.postgresql.org

Thanks. I modified [1] to link to this thread now instead of [2]

[1] https://commitfest.postgresql.org/patch/6342/
[2] https://postgr.es/m/CAA5RZ0v281uwSudcFrY8BymSRDM4J6PAHfzqg_9R+i7zWbTb2A@mail.gmail.com

--
Sami Imseih
Amazon Web Services (AWS)






^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 06:45         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-14 14:41           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
@ 2026-10-15 05:06             ` Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-22 03:09               ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 18+ messages in thread

From: Gaurav Singh @ 2026-10-15 05:06 UTC (permalink / raw)
  To: Sami Imseih <samimseih.pg@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; pgsql-bugs@lists.postgresql.org

I think the issue has existed since PG18, could we have a backpatch too?

Thanks,
Gaurav Singh

On Wed, Oct 14, 2026 at 8:11 PM Sami Imseih <samimseih.pg@gmail.com> wrote:

> Thanks. I modified [1] to link to this thread now instead of [2]
>
> [1] https://commitfest.postgresql.org/patch/6342/
> [2]
> https://postgr.es/m/CAA5RZ0v281uwSudcFrY8BymSRDM4J6PAHfzqg_9R+i7zWbTb2A@mail.gmail.com
>
> --
> Sami Imseih
> Amazon Web Services (AWS)
>

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 06:45         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-14 14:41           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-15 05:06             ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
@ 2026-10-22 03:09               ` Michael Paquier <michael@paquier.xyz>
  2026-10-23 05:19                 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 18+ messages in thread

From: Michael Paquier @ 2026-10-22 03:09 UTC (permalink / raw)
  To: Gaurav Singh <gaurav.singh@yugabyte.com>; +Cc: Sami Imseih <samimseih.pg@gmail.com>; pgsql-bugs@lists.postgresql.org

On Thu, Oct 15, 2026 at 10:36:06AM +0530, Gaurav Singh wrote:
> I think the issue has existed since PG18, could we have a backpatch too?

With a change in QueryDesc?  I'm afraid not.

+	/*
+	 * Nesting level at which this query started, or -1 if not set.  An
+	 * extension may set this from its ExecutorStart hook and read it back at
+	 * ExecutorEnd, which can run at a different level.  For example, a
+	 * cursor's query starts inside DECLARE but may end at COMMIT.
+	 *
+	 * The level counts as pg_stat_statements does: 0 for a statement sent by
+	 * the client, including EXECUTE of a prepared statement; one deeper for
+	 * each query run by another statement or by a function.  All extensions
+	 * that set this field must use that definition; one that counts
+	 * differently must leave it alone.
+	 */
+	int			start_nesting_level;

This is a one-time extension field, tied exclusively to PGSS that may
or may not conflict depending on what other extensions do.  This does
not give me good vibes in terms of extensibility, due to the easy
conflicts with other extensions.

We have a similar concept for ExplainState with its extension_state
indexed by GetExplainExtensionId() based on an extension name.
Perhaps we should do something similar for QueryDesc?  That would take
care of the conflicting states across extensions at least, giving
extensions way to store in the QueryDesc what they want.  Here, that's
the nesting_level that you want to track *only* in PGSS and have no
other extensions meddle with it.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 06:45         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-14 14:41           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-15 05:06             ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-22 03:09               ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
@ 2026-10-23 05:19                 ` Michael Paquier <michael@paquier.xyz>
  2026-10-26 22:16                   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Michael Paquier @ 2026-10-23 05:19 UTC (permalink / raw)
  To: Gaurav Singh <gaurav.singh@yugabyte.com>; +Cc: Sami Imseih <samimseih.pg@gmail.com>; pgsql-bugs@lists.postgresql.org

On Thu, Oct 22, 2026 at 12:09:56PM +0900, Michael Paquier wrote:
> We have a similar concept for ExplainState with its extension_state
> indexed by GetExplainExtensionId() based on an extension name.
> Perhaps we should do something similar for QueryDesc?  That would take
> care of the conflicting states across extensions at least, giving
> extensions way to store in the QueryDesc what they want.  Here, that's
> the nesting_level that you want to track *only* in PGSS and have no
> other extensions meddle with it.

This was itching me, so I have looked at what it would look like,
finishing with the attached set of two patches:
- One to introduce a backend-side facility to give for extensions a
way to plug in custom data into QueryDesc.
- The second patch is a refactored v2, that makes use of the new
infra.

That feels rather neat.  This facility could be extended to other
things, like auto_explain perhaps?
--
Michael

Attachments: download all (3 files, .tar.gz)

v3-0001-Add-custom-extension-states-to-QueryDesc.patch (text/plain, 6.5K) download From 416354866d1379793da8d95e754b676c933228e9 Mon Sep 17 00:00:00 2001 From: Michael Paquier <michael@paquier.xyz> Date: Fri, 23 Oct 2026 14:05:23 +0900 Subject: [PATCH v3 1/2] Add custom extension states to QueryDesc Extensions sometimes need to carry private data from one executor hook to another for a given query. ExecutorEnd() can run in a different context than ExecutorStart(), so backend-wide variables cannot be used for that. This commit adds a way for loadable modules to attach private state to a QueryDesc, modeled on the extension state of PlannerGlobal, PlannerInfo, RelOptInfo and ExplainState. GetQueryDescExtensionId() maps an extension name to a backend-local ID. SetQueryDescExtensionState() and GetQueryDescExtensionState() store and fetch an opaque pointer for that ID. The array of pointers is allocated in the memory context of the QueryDesc, and is freed by FreeQueryDesc(). What the pointers refer to is owned by the extensions. There are no users of this facility yet. A follow-up commit will make use of it. Author: Michael Paquier <michael@paquier.xyz> Discussion: https://postgr.es/m/ --- src/include/executor/execdesc.h | 29 ++++++++++ src/backend/tcop/pquery.c | 95 ++++++++++++++++++++++++++++++++- 2 files changed, 123 insertions(+), 1 deletion(-) diff --git a/src/include/executor/execdesc.h b/src/include/executor/execdesc.h index 37c2576e4bc9..b83efbd3315a 100644 --- a/src/include/executor/execdesc.h +++ b/src/include/executor/execdesc.h @@ -55,6 +55,10 @@ typedef struct QueryDesc /* This field is allocated by ExecutorStart if needed */ struct Instrumentation *query_instr; /* query level instrumentation */ + + /* Private state of extensions; see GetQueryDescExtensionId() */ + void **extension_state; + int extension_state_allocated; } QueryDesc; /* in pquery.c */ @@ -69,4 +73,29 @@ extern QueryDesc *CreateQueryDesc(PlannedStmt *plannedstmt, extern void FreeQueryDesc(QueryDesc *qdesc); +/* + * Loadable modules can attach private state to a QueryDesc. Get an integer + * ID for the module with GetQueryDescExtensionId(), which returns the same ID + * for the same name within a backend, and use it with the functions below. + * The state lives as long as the QueryDesc, and is not touched by core + * otherwise, so the module is responsible for what it points to. + */ +extern int GetQueryDescExtensionId(const char *extension_name); +extern void SetQueryDescExtensionState(QueryDesc *qdesc, int extension_id, + void *opaque); + +/* + * Get extension-specific state from a QueryDesc, or NULL if none was set. + */ +static inline void * +GetQueryDescExtensionState(QueryDesc *qdesc, int extension_id) +{ + Assert(extension_id >= 0); + + if (extension_id >= qdesc->extension_state_allocated) + return NULL; + + return qdesc->extension_state[extension_id]; +} + #endif /* EXECDESC_H */ diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c index fa2ef20f178a..a94550544cec 100644 --- a/src/backend/tcop/pquery.c +++ b/src/backend/tcop/pquery.c @@ -23,6 +23,7 @@ #include "executor/tstoreReceiver.h" #include "miscadmin.h" #include "pg_trace.h" +#include "port/pg_bitutils.h" #include "tcop/pquery.h" #include "tcop/utility.h" #include "utils/memutils.h" @@ -97,6 +98,10 @@ CreateQueryDesc(PlannedStmt *plannedstmt, /* not yet executed */ qd->already_executed = false; + /* no extension state yet */ + qd->extension_state = NULL; + qd->extension_state_allocated = 0; + return qd; } @@ -113,10 +118,98 @@ FreeQueryDesc(QueryDesc *qdesc) UnregisterSnapshot(qdesc->snapshot); UnregisterSnapshot(qdesc->crosscheck_snapshot); - /* Only the QueryDesc itself need be freed */ + /* + * Free the array of extension state pointers. What these point to is + * owned by the extensions. + */ + if (qdesc->extension_state != NULL) + pfree(qdesc->extension_state); + + /* Free the QueryDesc itself */ pfree(qdesc); } +static const char **QueryDescExtensionNameArray = NULL; +static int QueryDescExtensionNamesAssigned = 0; +static int QueryDescExtensionNamesAllocated = 0; + +/* + * Map the name of an extension to an integer ID, to be used with + * SetQueryDescExtensionState() and GetQueryDescExtensionState(). + * + * Within the lifetime of a particular backend, the same name will be mapped + * to the same ID every time. IDs are not stable across backends. + */ +int +GetQueryDescExtensionId(const char *extension_name) +{ + /* Search for an existing extension by this name; if found, return ID. */ + for (int i = 0; i < QueryDescExtensionNamesAssigned; ++i) + if (strcmp(QueryDescExtensionNameArray[i], extension_name) == 0) + return i; + + /* If there is no array yet, create one. */ + if (QueryDescExtensionNameArray == NULL) + { + QueryDescExtensionNamesAllocated = 16; + QueryDescExtensionNameArray = (const char **) + MemoryContextAlloc(TopMemoryContext, + QueryDescExtensionNamesAllocated + * sizeof(char *)); + } + + /* If there's an array but it's currently full, expand it. */ + if (QueryDescExtensionNamesAssigned >= QueryDescExtensionNamesAllocated) + { + int i = pg_nextpower2_32(QueryDescExtensionNamesAssigned + 1); + + QueryDescExtensionNameArray = + repalloc_array(QueryDescExtensionNameArray, const char *, i); + QueryDescExtensionNamesAllocated = i; + } + + /* Assign and return new ID. The name is copied, to be on the safe side. */ + QueryDescExtensionNameArray[QueryDescExtensionNamesAssigned] = + MemoryContextStrdup(TopMemoryContext, extension_name); + return QueryDescExtensionNamesAssigned++; +} + +/* + * Store extension-specific state into a QueryDesc. + * + * The array of pointers is allocated in the memory context of the QueryDesc, + * and is freed by FreeQueryDesc(). + */ +void +SetQueryDescExtensionState(QueryDesc *qdesc, int extension_id, void *opaque) +{ + Assert(extension_id >= 0); + + /* If there is no array yet, create one. */ + if (qdesc->extension_state == NULL) + { + qdesc->extension_state_allocated = + Max(4, pg_nextpower2_32(extension_id + 1)); + qdesc->extension_state = + MemoryContextAllocZero(GetMemoryChunkContext(qdesc), + qdesc->extension_state_allocated + * sizeof(void *)); + } + + /* If there's an array but it's currently full, expand it. */ + if (extension_id >= qdesc->extension_state_allocated) + { + int i = pg_nextpower2_32(extension_id + 1); + + qdesc->extension_state = + repalloc0_array(qdesc->extension_state, void *, + qdesc->extension_state_allocated, i); + qdesc->extension_state_allocated = i; + } + + qdesc->extension_state[extension_id] = opaque; +} + /* * ProcessQuery -- 2.56.0
v3-0002-pg_stat_statements-Fix-toplevel-tracking-across-n.patch (text/plain, 9.0K) download From 715a60ffd8e849da453afe9830d187f2f8ac3c32 Mon Sep 17 00:00:00 2001 From: Michael Paquier <michael@paquier.xyz> Date: Fri, 23 Oct 2026 14:17:39 +0900 Subject: [PATCH v3 2/2] pg_stat_statements: Fix toplevel tracking across nesting levels pg_stat_statements decides whether a query is toplevel from the nesting level at the time each hook runs. A QueryDesc can outlive the level it started at. For example, a cursor's query starts at level 1, inside DECLARE, but if the cursor is closed implicitly at COMMIT, ExecutorEnd runs at level 0. The entry created for the query at parse analysis has toplevel = false, so ExecutorEnd misses it and stores a duplicate with the raw query text and toplevel = true. The same happens with track = top when another extension such as auto_explain requests query instrumentation. ExecutorEnd stores the query even though ExecutorStart did not track it. PGSS is upgraded to save the nesting level in the extension state of the QueryDesc when ExecutorStart() tracks a query. ExecutorEnd() then uses it in place of the current level and skips the query if no state was saved. No backpatch is done, due to the ABI changes required. Reported-by: Gaurav Singh <gaurav.singh@yugabyte.com> Author: Sami Imseih <samimseih@gmail.com> Author: Michael Paquier <michael@paquier.xyz> Discussion: https://postgr.es/m/CAEcQ1bZrgwKsauC=03otebkLk77Zc_hYNjhTP3OUdpXs++u+BA@mail.gmail.com Discussion: https://postgr.es/m/CAA5RZ0v281uwSudcFrY8BymSRDM4J6PAHfzqg_9R+i7zWbTb2A@mail.gmail.com --- .../expected/level_tracking.out | 25 +++++++++++ .../pg_stat_statements/pg_stat_statements.c | 43 ++++++++++++++++++- .../pg_stat_statements/sql/level_tracking.sql | 11 +++++ src/tools/pgindent/typedefs.list | 1 + 4 files changed, 78 insertions(+), 2 deletions(-) diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contrib/pg_stat_statements/expected/level_tracking.out index 832d65e97cad..ccebf8b29604 100644 --- a/contrib/pg_stat_statements/expected/level_tracking.out +++ b/contrib/pg_stat_statements/expected/level_tracking.out @@ -1151,6 +1151,31 @@ SELECT toplevel, calls, query FROM pg_stat_statements t | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (7 rows) +-- DECLARE CURSOR, all-level tracking with implicit close. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +BEGIN; +DECLARE FOOCUR CURSOR FOR SELECT * FROM stats_track_tab WHERE x = 1; +FETCH FORWARD 1 FROM foocur; + x +--- +(0 rows) + +COMMIT; +SELECT toplevel, calls, query FROM pg_stat_statements + WHERE query LIKE 'DECLARE FOOCUR%' + ORDER BY query COLLATE "C", toplevel; + toplevel | calls | query +----------+-------+----------------------------------------------------------------------- + t | 1 | DECLARE FOOCUR CURSOR FOR SELECT * FROM stats_track_tab WHERE x = $1 + f | 1 | DECLARE FOOCUR CURSOR FOR SELECT * FROM stats_track_tab WHERE x = $1; +(2 rows) + -- DECLARE CURSOR, top-level tracking. SET pg_stat_statements.track = 'top'; SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index 4562222c9fcf..8db4b3d6e24b 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -274,6 +274,19 @@ static const ShmemCallbacks pgss_shmem_callbacks = { /* Current nesting depth of planner/ExecutorRun/ProcessUtility calls */ static int nesting_level = 0; +/* + * State saved in a QueryDesc at ExecutorStart, for use at ExecutorEnd. A + * query may end at a different nesting level than the one it started at, + * for example a cursor declared in a transaction and closed at COMMIT. + */ +typedef struct pgssQueryState +{ + int nesting_level; /* nesting level at ExecutorStart */ +} pgssQueryState; + +/* ID of pg_stat_statements for QueryDesc extension state */ +static int pgss_queryDesc_id = -1; + /* Saved hook values */ static post_parse_analyze_hook_type prev_post_parse_analyze_hook = NULL; static planner_hook_type prev_planner_hook = NULL; @@ -356,6 +369,7 @@ static void pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, DestReceiver *dest, QueryCompletion *qc); static void pgss_store(const char *query, int64 queryId, int query_location, int query_len, + bool toplevel, pgssStoreKind kind, double total_time, uint64 rows, const BufferUsage *bufusage, @@ -474,6 +488,11 @@ _PG_init(void) */ RegisterShmemCallbacks(&pgss_shmem_callbacks); + /* + * Get an ID to store state in QueryDesc. + */ + pgss_queryDesc_id = GetQueryDescExtensionId("pg_stat_statements"); + /* * Install hooks. */ @@ -866,6 +885,7 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query, const JumbleState *jst query->queryId, query->stmt_location, query->stmt_len, + nesting_level == 0, PGSS_INVALID, 0, 0, @@ -948,6 +968,7 @@ pgss_planner(Query *parse, parse->queryId, parse->stmt_location, parse->stmt_len, + nesting_level == 0, PGSS_PLAN, INSTR_TIME_GET_MILLISEC(duration), 0, @@ -992,6 +1013,8 @@ pgss_planner(Query *parse, static void pgss_ExecutorStart(QueryDesc *queryDesc, int eflags) { + pgssQueryState *qstate; + /* * If query has queryId zero, don't track it. This prevents double * counting of optimizable statements that are directly contained in @@ -1001,6 +1024,12 @@ pgss_ExecutorStart(QueryDesc *queryDesc, int eflags) { /* Request all summary instrumentation, i.e. timing, buffers and WAL */ queryDesc->query_instr_options |= INSTRUMENT_ALL; + + /* Remember the nesting level for ExecutorEnd */ + qstate = MemoryContextAlloc(GetMemoryChunkContext(queryDesc), + sizeof(pgssQueryState)); + qstate->nesting_level = nesting_level; + SetQueryDescExtensionState(queryDesc, pgss_queryDesc_id, qstate); } if (prev_ExecutorStart) @@ -1058,14 +1087,22 @@ static void pgss_ExecutorEnd(QueryDesc *queryDesc) { int64 queryId = queryDesc->plannedstmt->queryId; + pgssQueryState *qstate = GetQueryDescExtensionState(queryDesc, + pgss_queryDesc_id); + /* + * Use the nesting level saved at ExecutorStart, if available, since the + * query may end at a different level (e.g. a cursor closed at COMMIT). + */ if (queryId != INT64CONST(0) && queryDesc->query_instr && - pgss_enabled(nesting_level)) + qstate != NULL && + pgss_enabled(qstate->nesting_level)) { pgss_store(queryDesc->sourceText, queryId, queryDesc->plannedstmt->stmt_location, queryDesc->plannedstmt->stmt_len, + qstate->nesting_level == 0, PGSS_EXEC, INSTR_TIME_GET_MILLISEC(queryDesc->query_instr->total), queryDesc->estate->es_total_processed, @@ -1202,6 +1239,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, saved_queryId, saved_stmt_location, saved_stmt_len, + nesting_level == 0, PGSS_EXEC, INSTR_TIME_GET_MILLISEC(duration), rows, @@ -1267,6 +1305,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString, static void pgss_store(const char *query, int64 queryId, int query_location, int query_len, + bool toplevel, pgssStoreKind kind, double total_time, uint64 rows, const BufferUsage *bufusage, @@ -1310,7 +1349,7 @@ pgss_store(const char *query, int64 queryId, key.userid = GetUserId(); key.dbid = MyDatabaseId; key.queryid = queryId; - key.toplevel = (nesting_level == 0); + key.toplevel = toplevel; /* Lookup the hash table entry with shared lock. */ LWLockAcquire(&pgss->lock.lock, LW_SHARED); diff --git a/contrib/pg_stat_statements/sql/level_tracking.sql b/contrib/pg_stat_statements/sql/level_tracking.sql index 003efb8184b1..cc14a9808dd5 100644 --- a/contrib/pg_stat_statements/sql/level_tracking.sql +++ b/contrib/pg_stat_statements/sql/level_tracking.sql @@ -262,6 +262,17 @@ COMMIT; SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- DECLARE CURSOR, all-level tracking with implicit close. +SET pg_stat_statements.track = 'all'; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +BEGIN; +DECLARE FOOCUR CURSOR FOR SELECT * FROM stats_track_tab WHERE x = 1; +FETCH FORWARD 1 FROM foocur; +COMMIT; +SELECT toplevel, calls, query FROM pg_stat_statements + WHERE query LIKE 'DECLARE FOOCUR%' + ORDER BY query COLLATE "C", toplevel; + -- DECLARE CURSOR, top-level tracking. SET pg_stat_statements.track = 'top'; SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index a7a8dbdd23f7..cfef572df80f 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -4124,6 +4124,7 @@ pgsql_thing_t pgssEntry pgssGlobalStats pgssHashKey +pgssQueryState pgssSharedState pgssStoreKind pgssVersion -- 2.56.0
signature.asc (application/pgp-signature, 832B) download ^ permalink raw reply [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 06:45         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-14 14:41           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-15 05:06             ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-22 03:09               ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-23 05:19                 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
@ 2026-10-26 22:16                   ` Sami Imseih <samimseih.pg@gmail.com>
  2026-10-26 23:23                     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 18+ messages in thread

From: Sami Imseih @ 2026-10-26 22:16 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Gaurav Singh <gaurav.singh@yugabyte.com>; pgsql-bugs@lists.postgresql.org

> > We have a similar concept for ExplainState with its extension_state
> > indexed by GetExplainExtensionId() based on an extension name.
> > Perhaps we should do something similar for QueryDesc? That would take
> > care of the conflicting states across extensions at least, giving
> > extensions way to store in the QueryDesc what they want. Here, that's
> > the nesting_level that you want to track *only* in PGSS and have no
> > other extensions meddle with it.
>
> This was itching me, so I have looked at what it would look like,
> finishing with the attached set of two patches:
> - One to introduce a backend-side facility to give for extensions a
> way to plug in custom data into QueryDesc.
> - The second patch is a refactored v2, that makes use of the new
> infra.
>
> That feels rather neat. This facility could be extended to other
> things, like auto_explain perhaps?

I spent some time thinking about this today, and I can now see
its usefulness. Initially I was thinking that nesting_level is the only
thing that needs this state in pg_stat_statements and auto_explain, and
was not sure if this complexity is worth it.

But then I found a few cases. I am not sure how prevalent they are in
real-world use, but they seem worth handling.

Take for example a role switch between the DECLARE CURSOR and the cursor
cleanup. Maybe not very common, but perhaps applications dealing with
multi-tenancy/RLS could end up running into this without realizing.

```
CREATE ROLE user1;
CREATE ROLE user2;

SET pg_stat_statements.track = 'all';
SELECT pg_stat_statements_reset();

BEGIN;
SET ROLE user1;
DECLARE c CURSOR FOR SELECT 1 WHERE 1 = 1;
FETCH 1 FROM c;

SET ROLE user2;
COMMIT;

RESET ROLE;

SELECT userid::regrole, toplevel, calls, query
FROM pg_stat_statements
WHERE query LIKE 'DECLARE c%'
ORDER BY userid::regrole::text, toplevel, query;
```

This currently gives me:

```
 userid | toplevel | calls |                    query
--------+----------+-------+----------------------------------------------
 user1  | t        |     1 | DECLARE c CURSOR FOR SELECT $1 WHERE $2 = $3
 user2  | t        |     1 | DECLARE c CURSOR FOR SELECT 1 WHERE 1 = 1;
```

So I think pg_stat_statements should save the userid in the QueryDesc
state as well. Otherwise ExecutorEnd() can use a different current user than
 the one that started the query.

There is also a similar shape with auto_explain. The cursors were not
sampled when they started. After raising auto_explain.sample_rate, the
later SELECT makes the new sample rate effective. Then COMMIT closes
the cursors while auto_explain thinks the current query should be
sampled.

```
LOAD 'auto_explain';
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SET client_min_messages = notice;
SET auto_explain.log_level = notice;
SET auto_explain.log_min_duration = 0;
SET auto_explain.sample_rate = 0;
SET pg_stat_statements.track = 'all';

BEGIN;
DECLARE c1 CURSOR WITH HOLD FOR SELECT 'secret 1';
DECLARE c2 CURSOR WITH HOLD FOR SELECT 'secret 2';
DECLARE c3 CURSOR WITH HOLD FOR SELECT 'secret 3';

SET auto_explain.sample_rate = 1;
SELECT 1;

COMMIT;
```

One could argue which behavior we want to see in the above, but I think
the point is that this QueryDesc state support could help us close some
of these issues.

There is also a smaller question around toplevel. A later change to
pg_stat_statements.track can still affect whether ExecutorEnd() stores
the entry. We could also track the toplevel if we choose to make this
fix also.

So, it is nothing we need to do now, fixing the nesting_level is as is done in
v3- is good to close this thread, and as a follow-up I do think we should
fix the userid one. That one seems quite important as it deals with wrong
userid attribution.

As far as v3- goes, they both look good to me, and I could not find
anything wrong with them.

--
Sami






^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 06:45         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-14 14:41           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-15 05:06             ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-22 03:09               ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-23 05:19                 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-26 22:16                   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
@ 2026-10-26 23:23                     ` Michael Paquier <michael@paquier.xyz>
  2026-10-27 06:30                       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-27 07:29                       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Lukas Fittl <lukas@fittl.com>
  0 siblings, 2 replies; 18+ messages in thread

From: Michael Paquier @ 2026-10-26 23:23 UTC (permalink / raw)
  To: Sami Imseih <samimseih.pg@gmail.com>; +Cc: Gaurav Singh <gaurav.singh@yugabyte.com>; pgsql-bugs@lists.postgresql.org, Lukas Fittl <lukas@fittl.com>

On Mon, Oct 26, 2026 at 05:16:45PM -0500, Sami Imseih wrote:
> I spent some time thinking about this today, and I can now see
> its usefulness. Initially I was thinking that nesting_level is the only
> thing that needs this state in pg_stat_statements and auto_explain, and
> was not sure if this complexity is worth it.

I was wondering a bit myself about the potential interactions between
auto_explain and PGSS, especially once they're bundled together, but I
did not dig much about the details of the interactions..

> Take for example a role switch between the DECLARE CURSOR and the cursor
> cleanup. Maybe not very common, but perhaps applications dealing with
> multi-tenancy/RLS could end up running into this without realizing.
> 
> ```
>  userid | toplevel | calls |                    query
> --------+----------+-------+----------------------------------------------
>  user1  | t        |     1 | DECLARE c CURSOR FOR SELECT $1 WHERE $2 = $3
>  user2  | t        |     1 | DECLARE c CURSOR FOR SELECT 1 WHERE 1 = 1;
> ```

Fancy.  I've never seen that, but I suspect you would..

> There is also a similar shape with auto_explain. The cursors were not
> sampled when they started. After raising auto_explain.sample_rate, the
> later SELECT makes the new sample rate effective. Then COMMIT closes
> the cursors while auto_explain thinks the current query should be
> sampled.

That counts as one more case here.

> One could argue which behavior we want to see in the above, but I think
> the point is that this QueryDesc state support could help us close some
> of these issues.

I don't really want to enter in the realm where we add more
per-extension fields in backend structures, especially if multiple
extensions have a risk of stepping on each other.

> As far as v3- goes, they both look good to me, and I could not find
> anything wrong with them.

Okay, thanks.  Let's use this infrastructure, then.  I suspect that
Lukas Fittl would also find uses for it, as he's a lot into monitoring
and, err.. analyzing.  I am adding him in CC to make him aware of this
thread, at least, with QueryDesc extensibility in mind.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 06:45         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-14 14:41           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-15 05:06             ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-22 03:09               ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-23 05:19                 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-26 22:16                   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-26 23:23                     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
@ 2026-10-27 06:30                       ` Michael Paquier <michael@paquier.xyz>
  1 sibling, 0 replies; 18+ messages in thread

From: Michael Paquier @ 2026-10-27 06:30 UTC (permalink / raw)
  To: Sami Imseih <samimseih.pg@gmail.com>; +Cc: Gaurav Singh <gaurav.singh@yugabyte.com>; pgsql-bugs@lists.postgresql.org, Lukas Fittl <lukas@fittl.com>

On Tue, Oct 27, 2026 at 08:23:17AM +0900, Michael Paquier wrote:
> Okay, thanks.  Let's use this infrastructure, then.  I suspect that
> Lukas Fittl would also find uses for it, as he's a lot into monitoring
> and, err.. analyzing.  I am adding him in CC to make him aware of this
> thread, at least, with QueryDesc extensibility in mind.

FYI, I've applied the infra as of e5d5d1ad1e3a and the change for PGSS
as of 23bf901fa530.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 06:45         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-14 14:41           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-15 05:06             ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-22 03:09               ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-23 05:19                 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-26 22:16                   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-26 23:23                     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
@ 2026-10-27 07:29                       ` Lukas Fittl <lukas@fittl.com>
  2026-10-27 22:03                         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  1 sibling, 1 reply; 18+ messages in thread

From: Lukas Fittl @ 2026-10-27 07:29 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Sami Imseih <samimseih.pg@gmail.com>; Gaurav Singh <gaurav.singh@yugabyte.com>; pgsql-bugs@lists.postgresql.org

On Tue, Oct 27, 2026 at 12:23 AM Michael Paquier <michael@paquier.xyz> wrote:
> > One could argue which behavior we want to see in the above, but I think
> > the point is that this QueryDesc state support could help us close some
> > of these issues.
>
> I don't really want to enter in the realm where we add more
> per-extension fields in backend structures, especially if multiple
> extensions have a risk of stepping on each other.
>
> > As far as v3- goes, they both look good to me, and I could not find
> > anything wrong with them.
>
> Okay, thanks.  Let's use this infrastructure, then.  I suspect that
> Lukas Fittl would also find uses for it, as he's a lot into monitoring
> and, err.. analyzing.  I am adding him in CC to make him aware of this
> thread, at least, with QueryDesc extensibility in mind.

Thanks! I was actually briefly discussing this with Sami at PGConf.EU
last week, so I had already skimmed this :)

In general the extensibility seems helpful (I was briefly worried
about memory overhead / cache line impact, but even if another use
case adds more fields the increase is probably fine for QueryDesc
since it only exists once per query), though I don't have an immediate
use case for it in the extensions I'm working on.

That said, I'm still not convinced that using this extensibility for
tracking nesting level is the best approach, vs figuring a way in-core
to pass the current nesting level to executor hooks. It seems every
extension re-invents this, and keeps copying the same mistakes, e.g.
I'm sure pg_stat_monitor, pg_stat_kcache and pg_stat_plans will need
to solve this same tracking problem that is being talked about in this
thread.

AFAIK we had discussed this previously on a different thread, but I'm
not clear on why we couldn't have one nesting level definition for all
of these extensions?

Thanks,
Lukas

-- 
Lukas Fittl






^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 06:45         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-14 14:41           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-15 05:06             ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-22 03:09               ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-23 05:19                 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-26 22:16                   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-26 23:23                     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-27 07:29                       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Lukas Fittl <lukas@fittl.com>
@ 2026-10-27 22:03                         ` Sami Imseih <samimseih.pg@gmail.com>
  2026-10-27 23:35                           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 18+ messages in thread

From: Sami Imseih @ 2026-10-27 22:03 UTC (permalink / raw)
  To: Lukas Fittl <lukas@fittl.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Gaurav Singh <gaurav.singh@yugabyte.com>; pgsql-bugs@lists.postgresql.org

> That said, I'm still not convinced that using this extensibility for
> tracking nesting level is the best approach, vs figuring a way in-core
> to pass the current nesting level to executor hooks. It seems every
> extension re-invents this, and keeps copying the same mistakes, e.g.
> I'm sure pg_stat_monitor, pg_stat_kcache and pg_stat_plans will need
> to solve this same tracking problem that is being talked about in this
> thread.

One point raised is that the two contrib extensions that currently keep a
nesting_level, auto_explain and pg_stat_statements, do not give it quite the
same meaning. They both bump it around ExecutorRun and ExecutorFinish, but
pg_stat_statements also bumps it around the planner and ProcessUtility calls.

For auto_explain, nesting_level == 0 is used at ExecutorStart and ExecutorEnd
as part of its sampling, instrumentation and logging decisions. If that value
also counted utility or planning nesting, auto_explain would start treating
some currently top-level plans, such as CREATE TABLE AS queries, as nested.

So at least for these two contrib modules, preserving the current behavior
would seem to require keeping the nesting decision decoupled from a single
shared nesting_level.

Those differences could perhaps be worked around, but each extension would
still need to decide what the value means for its own purposes, so they may
still need some form of queryDesc state.

--
Sami Imseih
Amazon Web Services (AWS)






^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 06:45         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-14 14:41           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-15 05:06             ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-22 03:09               ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-23 05:19                 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-26 22:16                   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-26 23:23                     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-27 07:29                       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Lukas Fittl <lukas@fittl.com>
  2026-10-27 22:03                         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
@ 2026-10-27 23:35                           ` Michael Paquier <michael@paquier.xyz>
  2026-10-29 20:37                             ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  0 siblings, 1 reply; 18+ messages in thread

From: Michael Paquier @ 2026-10-27 23:35 UTC (permalink / raw)
  To: Sami Imseih <samimseih.pg@gmail.com>; +Cc: Lukas Fittl <lukas@fittl.com>; Gaurav Singh <gaurav.singh@yugabyte.com>; pgsql-bugs@lists.postgresql.org

On Tue, Oct 27, 2026 at 05:03:21PM -0500, Sami Imseih wrote:
> So at least for these two contrib modules, preserving the current behavior
> would seem to require keeping the nesting decision decoupled from a single
> shared nesting_level.
> 
> Those differences could perhaps be worked around, but each extension would
> still need to decide what the value means for its own purposes, so they may
> still need some form of queryDesc state.

And I doubt that it would fly far without a way to handle the
concurrent activity of more than one extension.  FWIW, to me,
QueryDesc is a natural fit to track the nesting level, still my
previous sentence points out that we had better use a different memory
area for each definition we'd want to put on a query basis.

I am wondering if we don't have a bit more we could do for each
QueryDesc at extension level, like activity tracking or stuff in this
area.  
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 06:45         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-14 14:41           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-15 05:06             ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-22 03:09               ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-23 05:19                 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-26 22:16                   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-26 23:23                     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-27 07:29                       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Lukas Fittl <lukas@fittl.com>
  2026-10-27 22:03                         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-27 23:35                           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
@ 2026-10-29 20:37                             ` Sami Imseih <samimseih.pg@gmail.com>
  2026-10-29 22:46                               ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 18+ messages in thread

From: Sami Imseih @ 2026-10-29 20:37 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Lukas Fittl <lukas@fittl.com>; Gaurav Singh <gaurav.singh@yugabyte.com>; pgsql-bugs@lists.postgresql.org

> I am wondering if we don't have a bit more we could do for each
> QueryDesc at extension level, like activity tracking or stuff in this
> area.

I will send out a few patches for some follow-up items mentioned
above.

--
Sami






^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: pg_stat_statements stores WITH HOLD cursor query with literals
  2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-12 21:50 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-13 08:14   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-13 21:05     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 02:02       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-14 06:45         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-14 14:41           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-15 05:06             ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
  2026-10-22 03:09               ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-23 05:19                 ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-26 22:16                   ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-26 23:23                     ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-27 07:29                       ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Lukas Fittl <lukas@fittl.com>
  2026-10-27 22:03                         ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
  2026-10-27 23:35                           ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Michael Paquier <michael@paquier.xyz>
  2026-10-29 20:37                             ` Re: pg_stat_statements stores WITH HOLD cursor query with literals Sami Imseih <samimseih.pg@gmail.com>
@ 2026-10-29 22:46                               ` Michael Paquier <michael@paquier.xyz>
  0 siblings, 0 replies; 18+ messages in thread

From: Michael Paquier @ 2026-10-29 22:46 UTC (permalink / raw)
  To: Sami Imseih <samimseih.pg@gmail.com>; +Cc: Lukas Fittl <lukas@fittl.com>; Gaurav Singh <gaurav.singh@yugabyte.com>; pgsql-bugs@lists.postgresql.org

On Thu, Oct 29, 2026 at 03:37:11PM -0500, Sami Imseih wrote:
> I will send out a few patches for some follow-up items mentioned
> above.

Thanks for that.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 18+ messages in thread


end of thread, other threads:[~2026-10-29 22:46 UTC | newest]

Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-12 20:10 pg_stat_statements stores WITH HOLD cursor query with literals Gaurav Singh <gaurav.singh@yugabyte.com>
2026-10-12 21:50 ` Sami Imseih <samimseih.pg@gmail.com>
2026-10-13 08:14   ` Gaurav Singh <gaurav.singh@yugabyte.com>
2026-10-13 21:05     ` Sami Imseih <samimseih.pg@gmail.com>
2026-10-14 02:02       ` Sami Imseih <samimseih.pg@gmail.com>
2026-10-14 06:45         ` Gaurav Singh <gaurav.singh@yugabyte.com>
2026-10-14 14:41           ` Sami Imseih <samimseih.pg@gmail.com>
2026-10-15 05:06             ` Gaurav Singh <gaurav.singh@yugabyte.com>
2026-10-22 03:09               ` Michael Paquier <michael@paquier.xyz>
2026-10-23 05:19                 ` Michael Paquier <michael@paquier.xyz>
2026-10-26 22:16                   ` Sami Imseih <samimseih.pg@gmail.com>
2026-10-26 23:23                     ` Michael Paquier <michael@paquier.xyz>
2026-10-27 06:30                       ` Michael Paquier <michael@paquier.xyz>
2026-10-27 07:29                       ` Lukas Fittl <lukas@fittl.com>
2026-10-27 22:03                         ` Sami Imseih <samimseih.pg@gmail.com>
2026-10-27 23:35                           ` Michael Paquier <michael@paquier.xyz>
2026-10-29 20:37                             ` Sami Imseih <samimseih.pg@gmail.com>
2026-10-29 22:46                               ` Michael Paquier <michael@paquier.xyz>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox