alert clients when prepared statements are deallocated

First seen: 2026-05-29 16:33:37+00:00 · Messages: 9 · Participants: 4

Latest Update

2026-06-04 · claude-opus-4-6

Incremental Update: Patch v2 Posted, Critical Race Condition Identified

Major Development: Protocol Extension Approach (Not Version Bump)

Nathan Bossart posted a concrete patch (v2) implementing the notification mechanism as a protocol extension rather than a protocol version bump. This is architecturally significant — it leverages the protocol extension infrastructure added in commit ae65f6066d (2018), meaning it works with any PostgreSQL version since then without requiring a full protocol version negotiation. However, older v10 instances (pre-2018 builds) would error on the extension, raising pg_upgrade compatibility concerns for v20 (which will support v10 as minimum source version).

Design Decisions Crystallized in v2

  1. Callback-only API — Nathan explored a polling/retrieval model but abandoned it due to complexity (duplicate reports, filtering by statement name, clearing state). The patch uses only the callback mechanism for now.
  2. Named statements only — Unnamed prepared statements do not generate notifications. No clear use-case identified.
  3. No tests included — Testing deferred to the libpq LO interface revamp patch.
  4. New connection parameter added in a follow-up revision to allow disabling the feature in libpq (addressing the pg_upgrade concern).

Bug Found: Empty Notification on DISCARD ALL with No Statements

Zsolt Parragi identified that DropAllPreparedStatements() would send a deallocation notification (with empty string meaning "all") even when no statements were actually removed — specifically when prepared_queries is initialized but empty. Nathan acknowledged and fixed this in a subsequent revision.

Critical Showstopper Identified: Async Race Condition

Nathan identified a fundamental timing problem that he himself calls "a pretty obvious problem" and potentially a "showstopper":

Scenario: A user calls PQsendQuery(conn, "DISCARD ALL") asynchronously, then immediately calls an lo_* function (which would use prepared statements internally). The deallocation notification callback hasn't fired yet (because the result hasn't been consumed), so libpq still believes its internal prepared statements exist. The LO function will fail.

This is not merely a race condition in the network sense — it's a logical ordering problem inherent to asynchronous libpq usage. The callback fires when results are consumed, but nothing forces consumption before the next operation.

Implications for the Larger Effort

Nathan explicitly questions whether moving libpq's LO interface to prepared statements makes sense at all given this hole. If this approach fails, the consequences are:

He frames this as a fundamental limitation: "there's not a great way to use prepared statements internally in libpq."

History (1 prior analysis)
2026-06-01 · claude-opus-4-6

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.