Alert Clients When Prepared Statements Are Deallocated

Core Problem

When a PostgreSQL client creates prepared statements (at the protocol level), those statements can be invalidated by SQL commands like DISCARD ALL or DEALLOCATE ALL issued by other layers in the client stack — connection poolers, middleware, ORMs, or even different parts of the same application. The client code that created the prepared statement receives no notification that its statement has been destroyed, leading to runtime errors when it attempts to execute a now-nonexistent statement.

This problem was discovered in a concrete, practical context: the effort to modernize libpq's Large Object (LO) interface to use prepared statements instead of the deprecated PQfn() fast-path protocol. If libpq internally prepares statements for LO operations, but a higher-level application or connection pooler issues DISCARD ALL, libpq's internal state becomes inconsistent with the server's — it believes statements exist that the server has already destroyed.

Architectural Significance

This is fundamentally a layered abstraction problem. In modern PostgreSQL deployments, the communication path between application logic and the server often traverses multiple layers:

Application Code (ORMs, frameworks)
    ↓
Language Bindings (e.g., psycopg, node-postgres)
    ↓
libpq (C client library)
    ↓
Connection Pooler (PgBouncer, Odyssey)
    ↓
PostgreSQL Server

Each layer may independently create and manage prepared statements. A DISCARD ALL at any layer invalidates all prepared statements, but only the layer issuing the command is aware. This creates a state synchronization problem that has no current solution in the PostgreSQL protocol.

Proposed Solutions

1. Server-Side Notification Mechanism (Primary Proposal — Nathan Bossart)

Add a server-to-client notification that fires whenever prepared statements are deallocated. This would be a new protocol message or piggyback on existing notification infrastructure, informing the client which statements have been destroyed.

Tradeoff: Only directly helps the lowest layer (libpq). Higher layers need their own propagation mechanism.

2. Callback Mechanism in libpq (Extension of Proposal 1)

A libpq API where callers register callbacks that fire upon receiving deallocation notifications from the server. Additionally, a default callback would maintain a list of deallocated statement names that callers can poll and reset. This two-tier approach (push via callback, pull via list) accommodates both C-level users and language bindings that may not easily use function pointers.

3. Alternative Approaches (Jacob Champion)

• Pinnable prepared statements: Allow protocol-level prepared statements to be marked as "pinned" so that DISCARD ALL skips them. This solves the problem by preventing the invalidation rather than reporting it.
• Separate client/middleware contexts: Namespace or isolate prepared statements by layer, so one layer's DISCARD cannot affect another's.
• Opportunistic rebuild on failure: Detect prepared statement does not exist errors and transparently re-prepare. This is a retry-based approach that avoids protocol changes but adds latency on first failure.

Key Design Tensions

Protocol Change vs. Client-Side Workaround

Adding a new protocol message is the cleanest solution architecturally, but it requires protocol version negotiation and has backward compatibility implications. Client-side approaches (like opportunistic rebuild) work with existing servers but are less robust.

Notification Granularity

Should the server report every individual statement name deallocated, or just signal "some statements were deallocated"? The former is more useful but potentially expensive if DISCARD ALL destroys many statements. The latter requires the client to assume all its statements are gone.

Layered Propagation

Tom Lane's key insight: even with server-side notification, the information only directly reaches the bottom of the client stack (libpq). There must be an API for propagating this upward. The callback mechanism addresses this, but it cannot solve the general case for arbitrary middleware architectures — it can only make libpq "a responsible citizen" in the notification chain.

Fallback Behavior

Any solution must degrade gracefully when the server doesn't support notifications (older versions). The callback API must allow callers to detect whether the feature is available and fall back to error-handling-based recovery.

Implications for the PQfn() Deprecation

This thread is a direct prerequisite for removing the fast-path protocol (PQfn()). The Large Object interface currently uses PQfn() to invoke server functions directly. Replacing this with prepared statements is architecturally cleaner and aligns with protocol simplification goals, but it cannot be done safely without solving the deallocation notification problem — otherwise libpq's LO functions would be less robust than the current implementation.