public inbox for [email protected]
help / color / mirror / Atom feedalert clients when prepared statements are deallocated
13+ messages / 5 participants
[nested] [flat]
* alert clients when prepared statements are deallocated
@ 2026-05-29 16:33 Nathan Bossart <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Nathan Bossart @ 2026-05-29 16:33 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; +Cc: pgsql-hackers
(Moving to a new thread since this seems like an independent feature.
Original discussion can be found here:
https://postgr.es/m/ahXE28klgxIJXBLq%40nathan)
When trying to take our own advice and teach the frontend LO interface to
use prepared statements instead of PQfn(), I discovered a couple of
problems. The biggest problem is that clients aren't alerted when a
prepared statement is deallocated with DISCARD or DEALLOCATE. Since this
seems like a general problem that affects more than just libpq's LO
functions, I'm seeing whether it makes sense to add some sort of
notification mechanism so that clients can re-prepare as needed. Some
initial discussion about the work-in-progress patch (which I've attached
again here) follows:
On Fri, May 29, 2026 at 11:10:58AM -0500, Nathan Bossart wrote:
> On Fri, May 29, 2026 at 08:43:07AM -0700, Jacob Champion wrote:
>> On Fri, May 29, 2026 at 8:14 AM Nathan Bossart <[email protected]> wrote:
>>> Here is a work-in-progress patch set that goes this direction.
>>
>> At a high level, I think advertising support for a single new message
>> needs to be done in a protocol extension rather than a minor version
>> bump.
>
> WFM
>
>>> This
>>> introduces a callback mechanism in libpq that is used to handle statement
>>> deallocation notifications. Older servers/clients fall back to
>>> PQexecParams(), which is slower, but the alternative is to leave PQnfn()
>>> and related code around indefinitely.
>>
>> IMO there's no hurry in getting rid of that path. If we decide to go
>> this direction, a fallback to PQnfn() seems like it'd fine for a few
>> releases; we could eventually swap to a PQexecParams() fallback and
>> get rid of the extra code once the older servers have aged out.
>
> That's fine with me, too.
>
>>> I'm wondering whether this new message type is general enough. For
>>> example, perhaps we could make an extensible message type for tracking
>>> various things. And I want to ensure this is useful for other clients,
>>> too.
>>
>> If it's just a general notification message, what does negotiating
>> "support" mean? Is best-effort notification okay, if the client has no
>> idea what a future message type means, or if the server doesn't send
>> the specific type of message the client is hoping for?
>
> That's what I had in mind. But if we don't have anything specific in mind
> that this mechanism could be extended to support, maybe we shouldn't
> bother. Especially if we can just add protocol extensions as necessary.
>
>> (In general, I'm kind of down on the "notify the client that X
>> happened" method of working around architectural issues. Maybe that's
>> what we need to move this specific part forward, but it doesn't feel
>> like a long-term solution and I don't know that we need to genericize
>> it without a solid set of use cases.)
>
> I'm certainly open to other ideas, but I'm afraid this is the best I've
> come up with in my admittedly limited time thinking about the problem.
--
nathan
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: alert clients when prepared statements are deallocated
@ 2026-05-29 17:07 Jacob Champion <[email protected]>
parent: Nathan Bossart <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Jacob Champion @ 2026-05-29 17:07 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: pgsql-hackers
On Fri, May 29, 2026 at 9:33 AM Nathan Bossart <[email protected]> wrote:
> > I'm certainly open to other ideas, but I'm afraid this is the best I've
> > come up with in my admittedly limited time thinking about the problem.
Grab bag of alternatives from the other thread:
- let drivers pin protocol-level prepared statements so that
application-level DISCARD doesn't touch them
- explicitly separate client and middleware contexts or streams from each other
- rebuild discarded prepared statements opportunistically on a failure
--Jacob
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: alert clients when prepared statements are deallocated
@ 2026-05-29 17:09 Tom Lane <[email protected]>
parent: Nathan Bossart <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2026-05-29 17:09 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Jacob Champion <[email protected]>; pgsql-hackers
Nathan Bossart <[email protected]> writes:
> When trying to take our own advice and teach the frontend LO interface to
> use prepared statements instead of PQfn(), I discovered a couple of
> problems. The biggest problem is that clients aren't alerted when a
> prepared statement is deallocated with DISCARD or DEALLOCATE.
Of course the first question about that is "why doesn't the client
know that already ... didn't it issue the deallocate itself?".
The core answer to that question is that there might be multiple
levels of client code involved, so that while some level of the client
stack probably knows it in some way, other levels might have created
prepared statements and not be aware that they're gone.
Therefore, having the server report this is only a partial answer
to the problem: it will only directly provide a fix to the bottom
client code level. To go further you'd need some inside-the-client
mechanism for propagating the notification up the client stack.
We can't really create that in general, but we can at least make
libpq be a responsible citizen in that chain. In short, a proposed
fix for this must also provide a way for the calling application to
hear about these reports, and a way for it to fall back if they're
not available.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: alert clients when prepared statements are deallocated
@ 2026-05-29 18:29 Nathan Bossart <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Nathan Bossart @ 2026-05-29 18:29 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Jacob Champion <[email protected]>; pgsql-hackers
On Fri, May 29, 2026 at 01:09:03PM -0400, Tom Lane wrote:
> Nathan Bossart <[email protected]> writes:
>> When trying to take our own advice and teach the frontend LO interface to
>> use prepared statements instead of PQfn(), I discovered a couple of
>> problems. The biggest problem is that clients aren't alerted when a
>> prepared statement is deallocated with DISCARD or DEALLOCATE.
>
> Of course the first question about that is "why doesn't the client
> know that already ... didn't it issue the deallocate itself?".
> The core answer to that question is that there might be multiple
> levels of client code involved, so that while some level of the client
> stack probably knows it in some way, other levels might have created
> prepared statements and not be aware that they're gone.
Right.
> Therefore, having the server report this is only a partial answer
> to the problem: it will only directly provide a fix to the bottom
> client code level. To go further you'd need some inside-the-client
> mechanism for propagating the notification up the client stack.
> We can't really create that in general, but we can at least make
> libpq be a responsible citizen in that chain. In short, a proposed
> fix for this must also provide a way for the calling application to
> hear about these reports, and a way for it to fall back if they're
> not available.
This is the intent of the callback mechanism. In short, a libpq user could
register a callback that runs as soon as a deallocation notification is
received. We could also add a default callback that stores a list of
deallocated prepared statements (or a subset that a caller has indicated
interest in). Callers could then call libpq-provided functions to retrieve
and reset that list. My hunch is that might be more convenient for
projects that use language bindings.
--
nathan
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: alert clients when prepared statements are deallocated
@ 2026-06-02 22:11 Nathan Bossart <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 3 replies; 13+ messages in thread
From: Nathan Bossart @ 2026-06-02 22:11 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Jacob Champion <[email protected]>; pgsql-hackers
I've spent quite a bit of time on this one. Attached is a new version of
the patch. A few notes:
* This version uses a protocol extension instead of bumping the protocol
version. It looks like this is the first such extension, so it's entirely
possible I'm missing something. Note that this should work for any version
of PostgreSQL released since 2018 (see commit ae65f6066d), but IIUC older
versions will error on the protocol extension. I'm a little concerned that
this could break pg_upgrade from early versions of v10 (which will be the
minimum supported source version in v20), so we might need to provide a way
to disable it in libpq.
* I sketched out an alternative design that would allow client applications
to retrieve the notifications at their leisure, but I stopped when I
realized this would actually add quite a bit of complexity. We have to
think about duplicate reports, specifying which statements to collect,
clearing reports, and other subtle behavior. I'm hopeful that the callback
mechanism is good enough for now. If feedback indicates it is not, we can
certainly re-evaluate as a follow-up effort.
* I didn't add notifications for unnamed prepared statements. I'm not
seeing a real use-case for that, but I admittedly haven't thought about it
too hard.
* I haven't added any tests in this patch. My thinking is that it will get
tested as part of the libpq-LO-interface revamp in the other thread.
* I'm a little worried about race conditions involving a client trying to
use a statement while a deallocation message is in flight, but I haven't
identified anything concrete so far. This is something I'd like to
investigate some more, though.
--
nathan
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: alert clients when prepared statements are deallocated
@ 2026-06-03 14:14 Zsolt Parragi <[email protected]>
parent: Nathan Bossart <[email protected]>
2 siblings, 1 reply; 13+ messages in thread
From: Zsolt Parragi @ 2026-06-03 14:14 UTC (permalink / raw)
To: [email protected]
Hello
@@ -558,6 +584,9 @@ DropAllPreparedStatements(void)
/* Now we can remove the hash table entry */
hash_search(prepared_queries, entry->stmt_name, HASH_REMOVE, NULL);
}
+
+ /* Alert the client */
+ SendStmtDeallocMsg("");
}
DropAllPreparedStatements has an early return if prepared_queries is
uninitialized, so we don't send anything in that case - but it calls
SendStmtDeallocMsg if it is initialized even when no nothing was
removed. This seems inconsistent to me, is it intentional?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: alert clients when prepared statements are deallocated
@ 2026-06-03 14:37 Nathan Bossart <[email protected]>
parent: Zsolt Parragi <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Nathan Bossart @ 2026-06-03 14:37 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: [email protected]
On Wed, Jun 03, 2026 at 07:14:53AM -0700, Zsolt Parragi wrote:
> DropAllPreparedStatements has an early return if prepared_queries is
> uninitialized, so we don't send anything in that case - but it calls
> SendStmtDeallocMsg if it is initialized even when no nothing was
> removed. This seems inconsistent to me, is it intentional?
Nope, will fix.
--
nathan
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: alert clients when prepared statements are deallocated
@ 2026-06-03 18:59 Nathan Bossart <[email protected]>
parent: Nathan Bossart <[email protected]>
2 siblings, 0 replies; 13+ messages in thread
From: Nathan Bossart @ 2026-06-03 18:59 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Jacob Champion <[email protected]>; pgsql-hackers
Here is a new version of the patch that adds a connection parameter for
disabling it in libpq. I've also done a round of cleanup and added a first
draft of a real commit message.
--
nathan
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: alert clients when prepared statements are deallocated
@ 2026-06-03 21:17 Nathan Bossart <[email protected]>
parent: Nathan Bossart <[email protected]>
2 siblings, 1 reply; 13+ messages in thread
From: Nathan Bossart @ 2026-06-03 21:17 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Jacob Champion <[email protected]>; pgsql-hackers
On Tue, Jun 02, 2026 at 05:11:52PM -0500, Nathan Bossart wrote:
> * I'm a little worried about race conditions involving a client trying to
> use a statement while a deallocation message is in flight, but I haven't
> identified anything concrete so far. This is something I'd like to
> investigate some more, though.
Hm. So there's actually a pretty obvious problem here. Say a user
executes something like PQsendQuery(conn, "DISCARD ALL") and then tries to
execute an lo_* function (modified to use prepared statements) prior to
consuming the result. In that case, the callback won't be called in time
and the LO function will fail.
My first instinct is that this is a showstopper for $subject, but perhaps
it is a rare enough scenario that we could live with documenting it. My
suspicion is that it's uncommon for folks to asynchronously deallocate all
prepared statements, and I don't know why you'd use PQsendClosePrepared()
on statements named libpq_internal_*. Nevertheless, this seems like a
rather large hole.
I think this calls into question whether moving the libpq interface to
prepared statements makes sense. If we can't do that, I think we're pretty
much forced to keep the fast-path around forever or to accept a larger
performance hit. In any case, I find it a little strange that there's not
a great way to use prepared statements internally in libpq, which is why
I'm chasing this a little more than perhaps I should.
--
nathan
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: alert clients when prepared statements are deallocated
@ 2026-06-04 23:56 Jacob Champion <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Jacob Champion @ 2026-06-04 23:56 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers
On Wed, Jun 3, 2026 at 2:17 PM Nathan Bossart <[email protected]> wrote:
> Hm. So there's actually a pretty obvious problem here. Say a user
> executes something like PQsendQuery(conn, "DISCARD ALL") and then tries to
> execute an lo_* function (modified to use prepared statements) prior to
> consuming the result. In that case, the callback won't be called in time
> and the LO function will fail.
Are you allowed to call lo_*/PQfn() in the middle of an operation like
that? I.e. does it work today?
--Jacob
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: alert clients when prepared statements are deallocated
@ 2026-06-05 09:34 Jelte Fennema-Nio <[email protected]>
parent: Jacob Champion <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Jelte Fennema-Nio @ 2026-06-05 09:34 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; +Cc: Nathan Bossart <[email protected]>; pgsql-hackers
On Fri, 29 May 2026 at 19:07, Jacob Champion
<[email protected]> wrote:
> - let drivers pin protocol-level prepared statements so that
> application-level DISCARD doesn't touch them
> - explicitly separate client and middleware contexts or streams from each other
> - rebuild discarded prepared statements opportunistically on a failure
I think I like option 2 best (and after that 1). I'm often annoyed
that our application layer and protocol layer is so intertwined, so
any attempt to separate them is a welcome addition in my opinion.
One approach would be to:
1. add an optional additional text field to the Parse message as a
kind of "namespace" for prepared statements. Leaving this field out or
set to the empty string, would create an "application-level" prepared
statement. Setting it to anything else would create a protocol-level
prepared statement within that namespace. This would allow libpq to
create its own prepared statements, without conflicting with
protocol-level statements created by client libraries like psycopopg.
2. Application-level DISCARD/DEALLOCATE would not clean up these
namespaced protocol level prepared statements
3. Add a protocol-level version of DEALLOCATE ALL and DISCARD ALL
which also clean up all the protocol-level prepared statements
4. Add a protocol-level message to deallocate all prepared statements
in a certain schema.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: alert clients when prepared statements are deallocated
@ 2026-06-05 22:03 Nathan Bossart <[email protected]>
parent: Jelte Fennema-Nio <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Nathan Bossart @ 2026-06-05 22:03 UTC (permalink / raw)
To: Jelte Fennema-Nio <[email protected]>; +Cc: Jacob Champion <[email protected]>; pgsql-hackers
On Fri, Jun 05, 2026 at 11:34:14AM +0200, Jelte Fennema-Nio wrote:
> I think I like option 2 best (and after that 1). I'm often annoyed
> that our application layer and protocol layer is so intertwined, so
> any attempt to separate them is a welcome addition in my opinion.
>
> One approach would be to:
> 1. add an optional additional text field to the Parse message as a
> kind of "namespace" for prepared statements. Leaving this field out or
> set to the empty string, would create an "application-level" prepared
> statement. Setting it to anything else would create a protocol-level
> prepared statement within that namespace. This would allow libpq to
> create its own prepared statements, without conflicting with
> protocol-level statements created by client libraries like psycopopg.
> 2. Application-level DISCARD/DEALLOCATE would not clean up these
> namespaced protocol level prepared statements
> 3. Add a protocol-level version of DEALLOCATE ALL and DISCARD ALL
> which also clean up all the protocol-level prepared statements
> 4. Add a protocol-level message to deallocate all prepared statements
> in a certain schema.
Do we need to guard who can create protocol-level statements? And if so,
how would we do that?
--
nathan
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: alert clients when prepared statements are deallocated
@ 2026-06-05 22:24 Jelte Fennema-Nio <[email protected]>
parent: Nathan Bossart <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Jelte Fennema-Nio @ 2026-06-05 22:24 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Jacob Champion <[email protected]>; pgsql-hackers
On Sat, Jun 6, 2026, 00:03 Nathan Bossart <[email protected]> wrote:
> Do we need to guard who can create protocol-level statements? And if so,
> how would we do that?
>
I think the only thing we'd need to guard against is a libpq user messing
with libpq its own internal protocol-level namespace, let's call that
"pqnamespace" for now. We'd add a new version of PQsendPrepare that would
take a namespace, but we'd make that return an error if a user of libpq
passed pqnamespace as the namespace argument. Similarly for the new
PQsendClosePrepared variant that takes a namespace. That way we'd reserve
that namespace.
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2026-06-05 22:24 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-29 16:33 alert clients when prepared statements are deallocated Nathan Bossart <[email protected]>
2026-05-29 17:07 ` Jacob Champion <[email protected]>
2026-06-05 09:34 ` Jelte Fennema-Nio <[email protected]>
2026-06-05 22:03 ` Nathan Bossart <[email protected]>
2026-06-05 22:24 ` Jelte Fennema-Nio <[email protected]>
2026-05-29 17:09 ` Tom Lane <[email protected]>
2026-05-29 18:29 ` Nathan Bossart <[email protected]>
2026-06-02 22:11 ` Nathan Bossart <[email protected]>
2026-06-03 14:14 ` Zsolt Parragi <[email protected]>
2026-06-03 14:37 ` Nathan Bossart <[email protected]>
2026-06-03 18:59 ` Nathan Bossart <[email protected]>
2026-06-03 21:17 ` Nathan Bossart <[email protected]>
2026-06-04 23:56 ` Jacob Champion <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox