pg_stat_statements stores WITH HOLD cursor query with literals

Поиск
Список
Период
Сортировка
Искать

pg_stat_statements stores WITH HOLD cursor query with literals

От:
Gaurav Singh <gaurav.singh@yugabyte.com>
Дата:
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

Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Sami Imseih <samimseih.pg@gmail.com>
Дата:
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)


Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Gaurav Singh <gaurav.singh@yugabyte.com>
Дата:
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)

Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Sami Imseih <samimseih.pg@gmail.com>
Дата:
Вложения:
> 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)

Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Sami Imseih <samimseih.pg@gmail.com>
Дата:
Вложения:
> 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)

Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Gaurav Singh <gaurav.singh@yugabyte.com>
Дата:
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)

Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Sami Imseih <samimseih.pg@gmail.com>
Дата:
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)


Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Gaurav Singh <gaurav.singh@yugabyte.com>
Дата:
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)

Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Michael Paquier <michael@paquier.xyz>
Дата:
Вложения:
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

Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Michael Paquier <michael@paquier.xyz>
Дата:
Вложения:
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

Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Sami Imseih <samimseih.pg@gmail.com>
Дата:
> > 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


Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Michael Paquier <michael@paquier.xyz>
Дата:
Вложения:
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

Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Michael Paquier <michael@paquier.xyz>
Дата:
Вложения:
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

Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Lukas Fittl <lukas@fittl.com>
Дата:
On Tue, Oct 27, 2026 at 12:23 AM Michael Paquier  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


Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Sami Imseih <samimseih.pg@gmail.com>
Дата:
> 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)


Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Michael Paquier <michael@paquier.xyz>
Дата:
Вложения:
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

Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Sami Imseih <samimseih.pg@gmail.com>
Дата:
> 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


Re: pg_stat_statements stores WITH HOLD cursor query with literals

От:
Michael Paquier <michael@paquier.xyz>
Дата:
Вложения:
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
FAQ