alert clients when prepared statements are deallocated
(Moving to a new thread since this seems like an independent feature.
Original discussion can be found here:
/messages/by-id/ahXE28klgxIJXBLq@nathan)
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 <nathandbossart@gmail.com> 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
Attachments:
v2-0001-tell-client-when-prep-stmts-are-deallocated.patchtext/plain; charset=us-asciiDownload+131-8
On Fri, May 29, 2026 at 9:33 AM Nathan Bossart <nathandbossart@gmail.com> 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
Nathan Bossart <nathandbossart@gmail.com> 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
On Fri, May 29, 2026 at 01:09:03PM -0400, Tom Lane wrote:
Nathan Bossart <nathandbossart@gmail.com> 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