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 93667104994fa0393208fa95b76d18296debc0bd Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Tue, 2 Jun 2026 17:01:12 -0500 Subject: [PATCH v3 1/1] tell client when prepared statements are deallocated Add a PrepStmtDeallocated protocol message, negotiated via the _pq_.report_stmt_dealloc extension, that tells clients when a named prepared statement is dropped by DEALLOCATE, DISCARD, or a Close message. libpq dispatches the message to callbacks registered with PQaddPrepStmtDeallocCallback, and PQprepStmtDeallocSupported reports whether the server accepted the extension. --- doc/src/sgml/libpq.sgml | 74 +++++++++++++++++++ doc/src/sgml/protocol.sgml | 62 +++++++++++++++- src/backend/commands/prepare.c | 29 ++++++++ src/backend/tcop/backend_startup.c | 13 ++-- src/include/libpq/libpq-be.h | 3 + src/include/libpq/protocol.h | 1 + src/interfaces/libpq/exports.txt | 2 + src/interfaces/libpq/fe-connect.c | 58 +++++++++++++++ src/interfaces/libpq/fe-protocol3.c | 46 ++++++++++++ src/interfaces/libpq/fe-trace.c | 10 +++ src/interfaces/libpq/libpq-fe.h | 11 +++ src/interfaces/libpq/libpq-int.h | 6 ++ .../libpq_pipeline/traces/prepared.trace | 1 + 13 files changed, 309 insertions(+), 7 deletions(-) diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index 7d3c3bb66d8..8bc51fffad0 100644 --- a/doc/src/sgml/libpq.sgml +++ b/doc/src/sgml/libpq.sgml @@ -7253,6 +7253,80 @@ typedef struct pgNotify </sect1> + <sect1 id="libpq-prepstmt-dealloc"> + <title>Prepared Statement Deallocation Notifications</title> + + <indexterm zone="libpq-prepstmt-dealloc"> + <primary>prepared statement deallocation</primary> + <secondary>in libpq</secondary> + </indexterm> + + <para> + The server can notify the client whenever a named prepared statement is + deallocated by + <link linkend="sql-deallocate"><command>DEALLOCATE</command></link>, + <link linkend="sql-discard"><command>DISCARD</command></link>, + <xref linkend="libpq-PQclosePrepared"/>, or + <xref linkend="libpq-PQsendClosePrepared"/>. This is useful when multiple + layers of client code share a connection and one drops a statement another + prepared. This behavior is negotiated through the + <link linkend="protocol-extensions"><literal>_pq_.report_stmt_dealloc</literal></link> + protocol extension, which is only available on servers running + <productname>PostgreSQL</productname> 20 and later. + <application>libpq</application> always requests this protocol extension. + </para> + + <para> + Notifications are delivered to callbacks registered with + <function>PQaddPrepStmtDeallocCallback</function>. + + <variablelist> + <varlistentry id="libpq-PQaddPrepStmtDeallocCallback"> + <term><function>PQaddPrepStmtDeallocCallback</function><indexterm><primary>PQaddPrepStmtDeallocCallback</primary></indexterm></term> + <listitem> + <para> + Registers a callback to be invoked immediately upon receiving a prepared + statement deallocation notification. The value of + <parameter>arg</parameter> is passed unaltered to the callback. The + <parameter>name</parameter> argument will contain the name of the + deallocated prepared statement, or an empty string if all were + deallocated. Callbacks run while <application>libpq</application> + processes incoming data, so they must not call any + <application>libpq</application> functions on the same + <parameter>conn</parameter>, and they must not assume that + <parameter>name</parameter> survives after returning (copy it if it is + needed later). Returns <literal>1</literal> on success or + <literal>0</literal> if the callback could not be registered (e.g., due + to running out of memory). + +<synopsis> +typedef void (*PQprepStmtDeallocCallback) (PGconn *conn, void *arg, const char *name); + +int PQaddPrepStmtDeallocCallback(PGconn *conn, PQprepStmtDeallocCallback cb, void *arg); +</synopsis> + </para> + </listitem> + </varlistentry> + + <varlistentry id="libpq-PQprepStmtDeallocSupported"> + <term><function>PQprepStmtDeallocSupported</function><indexterm><primary>PQprepStmtDeallocSupported</primary></indexterm></term> + <listitem> + <para> + Returns <literal>1</literal> if the server accepted the + <literal>_pq_.report_stmt_dealloc</literal> protocol extension. + Otherwise, returns <literal>0</literal> to indicate that no prepared + statement deallocation notifications will be sent. + +<synopsis> +int PQprepStmtDeallocSupported(PGconn *conn); +</synopsis> + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </sect1> + <sect1 id="libpq-copy"> <title>Functions Associated with the <command>COPY</command> Command</title> diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index 49f81676712..c881486d707 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -346,8 +346,15 @@ <tbody> <row> - <entry namest="last" align="center" valign="middle"> - <emphasis>(No supported protocol extensions are currently defined.)</emphasis> + <entry><literal>_pq_.report_stmt_dealloc</literal></entry> + <entry><emphasis>none</emphasis></entry> + <entry>PostgreSQL 20 and later</entry> + <entry>When negotiated, the server sends a + <link linkend="protocol-message-formats-PrepStmtDeallocated">PrepStmtDeallocated</link> + message whenever a named prepared statement is deallocated by + <link linkend="sql-deallocate"><command>DEALLOCATE</command></link>, + <link linkend="sql-discard"><command>DISCARD</command></link>, or a + <link linkend="protocol-message-formats-Close">Close</link> message. </entry> </row> </tbody> @@ -1587,6 +1594,20 @@ SELCT 1/0;<!-- this typo is intentional --> point in the protocol. </para> </note> + + <para> + If the client requested the + <link linkend="protocol-extensions"><literal>_pq_.report_stmt_dealloc</literal></link> + protocol extension, the backend sends a PrepStmtDeallocated message + whenever a named prepared statement is deallocated by + <link linkend="sql-deallocate"><command>DEALLOCATE</command></link>, + <link linkend="sql-discard"><command>DISCARD</command></link>, or a + <link linkend="protocol-message-formats-Close">Close</link> message. This + alerts the client that a statement it prepared is gone (e.g., if another + layer of the client stack dropped it) and that it must be re-prepared + before reuse. The message carries the deallocated statement's name, or an + empty string to mean that all prepared statements were deallocated. + </para> </sect2> <sect2 id="protocol-flow-canceling-requests"> @@ -5873,6 +5894,43 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;" </listitem> </varlistentry> + <varlistentry id="protocol-message-formats-PrepStmtDeallocated"> + <term>PrepStmtDeallocated (B)</term> + <listitem> + <variablelist> + <varlistentry> + <term>Byte1('i')</term> + <listitem> + <para> + Identifies the message as a prepared statement deallocation + notification. This is sent only if the client requested the + <literal>_pq_.report_stmt_dealloc</literal> protocol extension. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>Int32</term> + <listitem> + <para> + Length of message contents in bytes, including self. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>String</term> + <listitem> + <para> + The name of the deallocated prepared statement. An empty string + indicates that all prepared statements were deallocated. + </para> + </listitem> + </varlistentry> + </variablelist> + </listitem> + </varlistentry> + <varlistentry id="protocol-message-formats-Query"> <term>Query (F)</term> <listitem> diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c index 876aad2100a..2c3e881cf42 100644 --- a/src/backend/commands/prepare.c +++ b/src/backend/commands/prepare.c @@ -26,6 +26,9 @@ #include "commands/explain_state.h" #include "commands/prepare.h" #include "funcapi.h" +#include "libpq/libpq.h" +#include "libpq/pqformat.h" +#include "miscadmin.h" #include "nodes/nodeFuncs.h" #include "parser/parse_coerce.h" #include "parser/parse_collate.h" @@ -512,6 +515,26 @@ DeallocateQuery(DeallocateStmt *stmt) DropAllPreparedStatements(); } +/* + * Tell the client that a prepared statement has been deallocated (an empty + * string means all of them). Only sent to clients that requested the + * _pq_.report_stmt_dealloc protocol extension. + */ +static void +SendStmtDeallocMsg(const char *name) +{ + StringInfoData buf; + + if (whereToSendOutput != DestRemote) + return; + if (!MyProcPort || !MyProcPort->report_stmt_dealloc) + return; + + pq_beginmessage(&buf, PqMsg_PrepStmtDeallocated); + pq_sendstring(&buf, name); + pq_endmessage(&buf); +} + /* * Internal version of DEALLOCATE * @@ -532,6 +555,9 @@ DropPreparedStatement(const char *stmt_name, bool showError) /* Now we can remove the hash table entry */ hash_search(prepared_queries, entry->stmt_name, HASH_REMOVE, NULL); + + /* Alert the client */ + SendStmtDeallocMsg(stmt_name); } } @@ -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(""); } /* diff --git a/src/backend/tcop/backend_startup.c b/src/backend/tcop/backend_startup.c index 25205cee0fa..7e5a2d08310 100644 --- a/src/backend/tcop/backend_startup.c +++ b/src/backend/tcop/backend_startup.c @@ -806,12 +806,15 @@ retry: else if (strncmp(nameptr, "_pq_.", 5) == 0) { /* - * Any option beginning with _pq_. is reserved for use as a - * protocol-level option, but at present no such options are - * defined. + * Options beginning with _pq_. are protocol extensions. + * Recognized ones are handled here; report the rest as + * unsupported. */ - unrecognized_protocol_options = - lappend(unrecognized_protocol_options, pstrdup(nameptr)); + if (strcmp(nameptr, "_pq_.report_stmt_dealloc") == 0) + port->report_stmt_dealloc = true; + else + unrecognized_protocol_options = + lappend(unrecognized_protocol_options, pstrdup(nameptr)); } else { diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h index 921b2daa4ff..82296a61aac 100644 --- a/src/include/libpq/libpq-be.h +++ b/src/include/libpq/libpq-be.h @@ -152,6 +152,9 @@ typedef struct Port char *cmdline_options; List *guc_options; + /* did client request prepared statement deallocation notifications? */ + bool report_stmt_dealloc; + /* * The startup packet application name, only used here for the "connection * authorized" log message. We shouldn't use this post-startup, instead diff --git a/src/include/libpq/protocol.h b/src/include/libpq/protocol.h index eae8f0e7238..7ea331f7210 100644 --- a/src/include/libpq/protocol.h +++ b/src/include/libpq/protocol.h @@ -53,6 +53,7 @@ #define PqMsg_FunctionCallResponse 'V' #define PqMsg_CopyBothResponse 'W' #define PqMsg_ReadyForQuery 'Z' +#define PqMsg_PrepStmtDeallocated 'i' #define PqMsg_NoData 'n' #define PqMsg_PortalSuspended 's' #define PqMsg_ParameterDescription 't' diff --git a/src/interfaces/libpq/exports.txt b/src/interfaces/libpq/exports.txt index 1e3d5bd5867..1cf4d4b4980 100644 --- a/src/interfaces/libpq/exports.txt +++ b/src/interfaces/libpq/exports.txt @@ -211,3 +211,5 @@ PQdefaultAuthDataHook 208 PQfullProtocolVersion 209 appendPQExpBufferVA 210 PQgetThreadLock 211 +PQaddPrepStmtDeallocCallback 212 +PQprepStmtDeallocSupported 213 diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c index 4272d386e64..c55f7ec9942 100644 --- a/src/interfaces/libpq/fe-connect.c +++ b/src/interfaces/libpq/fe-connect.c @@ -700,6 +700,7 @@ pqDropServerData(PGconn *conn) /* Reset assorted other per-connection state */ conn->last_sqlstate[0] = '\0'; conn->pversion_negotiated = false; + conn->report_stmt_dealloc = true; /* unset if needed at conn start */ conn->auth_req_received = false; conn->client_finished_auth = false; conn->password_needed = false; @@ -5178,6 +5179,10 @@ freePGconn(PGconn *conn) free(conn->rowBuf); termPQExpBuffer(&conn->errorMessage); termPQExpBuffer(&conn->workBuffer); + if (conn->prepStmtDeallocCallbacks) + free(conn->prepStmtDeallocCallbacks); + if (conn->prepStmtDeallocCallbackArgs) + free(conn->prepStmtDeallocCallbackArgs); free(conn); } @@ -8426,3 +8431,56 @@ PQgetThreadLock(void) Assert(pg_g_threadlock); return pg_g_threadlock; } + +/* + * Registers a callback to be invoked whenever the server reports a prepared + * statement deallocation. arg is passed through to the callback unaltered. + */ +int +PQaddPrepStmtDeallocCallback(PGconn *conn, PQprepStmtDeallocCallback cb, + void *arg) +{ + int i; + PQprepStmtDeallocCallback *new_cbs; + void **new_args; + + if (!conn) + return 0; + + i = conn->nPrepStmtDeallocCallbacks; + + new_cbs = realloc(conn->prepStmtDeallocCallbacks, + (i + 1) * sizeof(PQprepStmtDeallocCallback)); + if (!new_cbs) + { + libpq_append_conn_error(conn, "out of memory"); + return 0; + } + conn->prepStmtDeallocCallbacks = new_cbs; + + new_args = realloc(conn->prepStmtDeallocCallbackArgs, + (i + 1) * sizeof(void *)); + if (!new_args) + { + libpq_append_conn_error(conn, "out of memory"); + return 0; + } + conn->prepStmtDeallocCallbackArgs = new_args; + + new_cbs[i] = cb; + new_args[i] = arg; + conn->nPrepStmtDeallocCallbacks = i + 1; + + return 1; +} + +/* + * Returns true if the server accepted the _pq_.report_stmt_dealloc extension. + * If false, no notifications will arrive and the caller must re-prepare on + * error. + */ +int +PQprepStmtDeallocSupported(PGconn *conn) +{ + return conn && conn->report_stmt_dealloc; +} diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c index 78ffb1025d0..9da883f2ada 100644 --- a/src/interfaces/libpq/fe-protocol3.c +++ b/src/interfaces/libpq/fe-protocol3.c @@ -61,6 +61,30 @@ static size_t build_startup_packet(const PGconn *conn, char *packet, const PQEnvironmentOption *options); +/* + * Read a PrepStmtDeallocated message and invoke the registered callbacks. + * Broken out as a subroutine since it can occur in several places. + * + * Entry: 'i' message type and length already consumed. + * Exit: 0 on success, EOF if not enough data. + */ +static int +getPrepStmtDeallocated(PGconn *conn) +{ + if (pqGets(&conn->workBuffer, conn)) + return EOF; + + for (int i = 0; i < conn->nPrepStmtDeallocCallbacks; i++) + { + PQprepStmtDeallocCallback cb = conn->prepStmtDeallocCallbacks[i]; + void *arg = conn->prepStmtDeallocCallbackArgs[i]; + + cb(conn, arg, conn->workBuffer.data); + } + + return 0; +} + /* * parseInput: if appropriate, parse input data from backend * until input is exhausted or a stopping state is reached. @@ -184,6 +208,11 @@ pqParseInput3(PGconn *conn) if (getParameterStatus(conn)) return; } + else if (id == PqMsg_PrepStmtDeallocated) + { + if (getPrepStmtDeallocated(conn)) + return; + } else { /* Any other case is unexpected and we summarily skip it */ @@ -305,6 +334,10 @@ pqParseInput3(PGconn *conn) if (getParameterStatus(conn)) return; break; + case PqMsg_PrepStmtDeallocated: + if (getPrepStmtDeallocated(conn)) + return; + break; case PqMsg_BackendKeyData: /* @@ -1545,6 +1578,8 @@ pqGetNegotiateProtocolVersion3(PGconn *conn) { found_test_protocol_negotiation = true; } + else if (strcmp(conn->workBuffer.data, "_pq_.report_stmt_dealloc") == 0) + conn->report_stmt_dealloc = false; else { libpq_append_conn_error(conn, "received invalid protocol negotiation message: server reported an unsupported parameter that was not requested (\"%s\")", @@ -1906,6 +1941,10 @@ getCopyDataMessage(PGconn *conn) if (getParameterStatus(conn)) return 0; break; + case PqMsg_PrepStmtDeallocated: + if (getPrepStmtDeallocated(conn)) + return 0; + break; case PqMsg_CopyData: return msgLength; case PqMsg_CopyDone: @@ -2410,6 +2449,10 @@ pqFunctionCall3(PGconn *conn, Oid fnid, if (getParameterStatus(conn)) continue; break; + case PqMsg_PrepStmtDeallocated: + if (getPrepStmtDeallocated(conn)) + continue; + break; default: /* The backend violates the protocol. */ libpq_append_conn_error(conn, "protocol error: id=0x%x", id); @@ -2525,6 +2568,9 @@ build_startup_packet(const PGconn *conn, char *packet, if (conn->client_encoding_initial && conn->client_encoding_initial[0]) ADD_STARTUP_OPTION("client_encoding", conn->client_encoding_initial); + /* Ask the server to report prepared statement deallocations. */ + ADD_STARTUP_OPTION("_pq_.report_stmt_dealloc", ""); + /* * Add the test_protocol_negotiation option when greasing, to test that * servers properly report unsupported protocol options in addition to diff --git a/src/interfaces/libpq/fe-trace.c b/src/interfaces/libpq/fe-trace.c index c348b08c39b..e9f734187a2 100644 --- a/src/interfaces/libpq/fe-trace.c +++ b/src/interfaces/libpq/fe-trace.c @@ -543,6 +543,13 @@ pqTraceOutput_ParameterStatus(FILE *f, const char *message, int *cursor) pqTraceOutputString(f, message, cursor, false); } +static void +pqTraceOutput_PrepStmtDeallocated(FILE *f, const char *message, int *cursor) +{ + fprintf(f, "PrepStmtDeallocated\t"); + pqTraceOutputString(f, message, cursor, false); +} + static void pqTraceOutput_ParameterDescription(FILE *f, const char *message, int *cursor, bool regress) { @@ -793,6 +800,9 @@ pqTraceOutputMessage(PGconn *conn, const char *message, bool toServer) else pqTraceOutput_ParameterStatus(conn->Pfdebug, message, &logCursor); break; + case PqMsg_PrepStmtDeallocated: + pqTraceOutput_PrepStmtDeallocated(conn->Pfdebug, message, &logCursor); + break; case PqMsg_ParameterDescription: pqTraceOutput_ParameterDescription(conn->Pfdebug, message, &logCursor, regress); break; diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h index 8ecb9b4a4c7..d7432ce75ce 100644 --- a/src/interfaces/libpq/libpq-fe.h +++ b/src/interfaces/libpq/libpq-fe.h @@ -486,6 +486,17 @@ typedef void (*pgthreadlock_t) (int acquire); extern pgthreadlock_t PQregisterThreadLock(pgthreadlock_t newhandler); extern pgthreadlock_t PQgetThreadLock(void); +/* callbacks for prepared statement deallocation notifications */ +typedef void (*PQprepStmtDeallocCallback) (PGconn *conn, void *arg, + const char *name); + +extern int PQaddPrepStmtDeallocCallback(PGconn *conn, + PQprepStmtDeallocCallback cb, + void *arg); + +/* whether the server will report prepared statement deallocations */ +extern int PQprepStmtDeallocSupported(PGconn *conn); + /* === in fe-trace.c === */ extern void PQtrace(PGconn *conn, FILE *debug_port); extern void PQuntrace(PGconn *conn); diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h index 461b39620c3..0631535b58d 100644 --- a/src/interfaces/libpq/libpq-int.h +++ b/src/interfaces/libpq/libpq-int.h @@ -507,6 +507,8 @@ struct pg_conn int sversion; /* server version, e.g. 70401 for 7.4.1 */ bool pversion_negotiated; /* true if NegotiateProtocolVersion * was received */ + bool report_stmt_dealloc; /* true if the server accepted the + * _pq_.report_stmt_dealloc extension */ bool auth_req_received; /* true if any type of auth req received */ bool password_needed; /* true if server demanded a password */ bool gssapi_used; /* true if authenticated via gssapi */ @@ -532,6 +534,10 @@ struct pg_conn void (*cleanup_async_auth) (PGconn *conn); pgsocket altsock; /* alternative socket for client to poll */ + /* Callbacks and pass-through args for prepared statement deallocations */ + PQprepStmtDeallocCallback *prepStmtDeallocCallbacks; + void **prepStmtDeallocCallbackArgs; + int nPrepStmtDeallocCallbacks; /* Transient state needed while establishing connection */ PGTargetServerType target_server_type; /* desired session properties */ diff --git a/src/test/modules/libpq_pipeline/traces/prepared.trace b/src/test/modules/libpq_pipeline/traces/prepared.trace index aeb5de109e0..5d36fb0056d 100644 --- a/src/test/modules/libpq_pipeline/traces/prepared.trace +++ b/src/test/modules/libpq_pipeline/traces/prepared.trace @@ -7,6 +7,7 @@ B 113 RowDescription 4 "?column?" NNNN 0 NNNN 4 -1 0 "?column?" NNNN 0 NNNN 655 B 5 ReadyForQuery I F 16 Close S "select_one" F 4 Sync +B 15 PrepStmtDeallocated "select_one" B 4 CloseComplete B 5 ReadyForQuery I F 16 Describe S "select_one" -- 2.50.1 (Apple Git-155)
