alert clients when prepared statements are deallocated

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

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

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?

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

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

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

On Wed, Jun 3, 2026 at 2:17 PM Nathan Bossart <nathandbossart@gmail.com> 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

On Fri, 29 May 2026 at 19:07, Jacob Champion
<jacob.champion@enterprisedb.com> 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.

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

On Sat, Jun 6, 2026, 00:03 Nathan Bossart <nathandbossart@gmail.com> 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.