Subject: alert clients when prepared statements are deallocated

From: Nathan Bossart <[email-masked]>
Date: Fri, 29 May 2026 11:33:37 -0500

(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-masked]> 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

---

From: Jacob Champion <[email-masked]>
Date: Fri, 29 May 2026 10:07:34 -0700

> > 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

---

From: Tom Lane <[email-masked]>
Date: Fri, 29 May 2026 13:09:03 -0400

Nathan Bossart <[email-masked]> writes:
> 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

---

From: Nathan Bossart <[email-masked]>
Date: Fri, 29 May 2026 13:29:02 -0500

> prepared statements and not be aware that they're gone.

Right.

> hear about these reports, and a way for it to fall back if they're

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

---

From: Nathan Bossart <[email-masked]>
Date: Tue, 2 Jun 2026 17:11:52 -0500

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

---

From: Zsolt Parragi <[email-masked]>
Date: Wed, 3 Jun 2026 07:14:53 -0700

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?

---

From: Nathan Bossart <[email-masked]>
Date: Wed, 3 Jun 2026 09:37:54 -0500

> removed. This seems inconsistent to me, is it intentional?

Nope, will fix.

-- 
nathan

---

From: Nathan Bossart <[email-masked]>
Date: Wed, 3 Jun 2026 13:59:37 -0500

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

---

From: Nathan Bossart <[email-masked]>
Date: Wed, 3 Jun 2026 16:17:31 -0500

> 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

---

From: Jacob Champion <[email-masked]>
Date: Thu, 4 Jun 2026 16:56:04 -0700

> 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

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Fri, 5 Jun 2026 11:34:14 +0200

> - 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.

---

From: Nathan Bossart <[email-masked]>
Date: Fri, 5 Jun 2026 17:03:05 -0500

> 4. Add a protocol-level message to deallocate all prepared statements

Do we need to guard who can create protocol-level statements?  And if so,
how would we do that?

-- 
nathan

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Sat, 6 Jun 2026 00:24:48 +0200

> Do we need to guard who can create protocol-level statements?  And if so,

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.