pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Paquier <michael@paquier.xyz>
To: Sami Imseih <samimseih.pg@gmail.com>
Cc: Gaurav Singh <gaurav.singh@yugabyte.com>
Cc: pgsql-bugs@lists.postgresql.org, Lukas Fittl <lukas@fittl.com>
Subject: Re: pg_stat_statements stores WITH HOLD cursor query with literals
Date: Tue, 27 Oct 2026 08:23:17 +0900
Message-ID: <at_g5VZJD2ZCluDW@paquier.xyz> (raw)
In-Reply-To: <CAN12+YKFJot+StpC7Dgwb68EYVWCZVrB5Gb5L8KML=8srn5OJA@mail.gmail.com>
References: <CAN12+YLPn65OFk+POCnLo1QJ=n3Gooz6Bsz_VsF2uh5c45iuPQ@mail.gmail.com>
	<CAEcQ1bZJUzjxe-tSOU25T3XtMEL3YyC4ESjk9k+KVAHRvyhtAQ@mail.gmail.com>
	<CAN12+Y+KwJc8HH9BRzyfuG9vcU-=4vv_gc2BQ24sGpmxZi9Caw@mail.gmail.com>
	<CAN12+YL7jf5_04Lme1Qaf2=_JuMAKAoCQiSY7-4__Svfc4igBA@mail.gmail.com>
	<CAEcQ1bYf03gGvvZWXCTv2L198zNArFrVoNDNQWecJ_7YmfE6UA@mail.gmail.com>
	<CAN12+YJdyb6zRHU+3owvBpKxgqB-tweRjjhUUTa5TyDn433GRw@mail.gmail.com>
	<CAEcQ1bZ0oW_RaNFAo6S9COOLnhdskx=z6p285WALq10QgVpYBA@mail.gmail.com>
	<atl-hB3haQF62tlN@paquier.xyz>
	<atruZFUdH40HVUJh@paquier.xyz>
	<CAN12+YKFJot+StpC7Dgwb68EYVWCZVrB5Gb5L8KML=8srn5OJA@mail.gmail.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

view thread (18+ messages)  latest in thread

Message-ID: <at_g5VZJD2ZCluDW@paquier.xyz>
Permalink:  ../at_g5VZJD2ZCluDW@paquier.xyz/
Also on:    postgresql.org/message-id/at_g5VZJD2ZCluDW@paquier.xyz

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: michael@paquier.xyz, samimseih.pg@gmail.com, gaurav.singh@yugabyte.com, lukas@fittl.com
  Subject: Re: pg_stat_statements stores WITH HOLD cursor query with literals
  In-Reply-To: <at_g5VZJD2ZCluDW@paquier.xyz>
  References: <CAN12+YLPn65OFk+POCnLo1QJ=n3Gooz6Bsz_VsF2uh5c45iuPQ@mail.gmail.com> <CAEcQ1bZJUzjxe-tSOU25T3XtMEL3YyC4ESjk9k+KVAHRvyhtAQ@mail.gmail.com> <CAN12+Y+KwJc8HH9BRzyfuG9vcU-=4vv_gc2BQ24sGpmxZi9Caw@mail.gmail.com> <CAN12+YL7jf5_04Lme1Qaf2=_JuMAKAoCQiSY7-4__Svfc4igBA@mail.gmail.com> <CAEcQ1bYf03gGvvZWXCTv2L198zNArFrVoNDNQWecJ_7YmfE6UA@mail.gmail.com> <CAN12+YJdyb6zRHU+3owvBpKxgqB-tweRjjhUUTa5TyDn433GRw@mail.gmail.com> <CAEcQ1bZ0oW_RaNFAo6S9COOLnhdskx=z6p285WALq10QgVpYBA@mail.gmail.com> <atl-hB3haQF62tlN@paquier.xyz> <atruZFUdH40HVUJh@paquier.xyz> <CAN12+YKFJot+StpC7Dgwb68EYVWCZVrB5Gb5L8KML=8srn5OJA@mail.gmail.com> <at_g5VZJD2ZCluDW@paquier.xyz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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