Re: [PATCH] Extensible ReadyForQuery wire protocol message and C hook, for connection pools and WAIT FOR LSN

Hi hackers,

Attached is version 3 of the patch to extend the PostgreSQL ReadyForQuery
('Z') frontend/backend wire protocol message and introduce the
ready_for_query_hook C API.

This version incorporates the benchmarking measurements and review
feedback provided by Manu on v2.

Background & Motivation
-----------------------

Connection poolers operating in transaction-pooling mode (e.g. PgBouncer,
pgpool-II, Odyssey) must determine whether a backend connection is safe
to return to the shared pool or reuse for another client without executing
a costly DISCARD ALL round-trip. Similarly, proxies and applications routing
read-after-write queries to standby replicas need the primary's commit LSN
to coordinate with pg_wal_replay_wait() / WAIT FOR LSN without an extra
round-trip query.

This patch allows clients, proxies, and connection poolers to opt in to
a rich ReadyForQuery message containing session state indicators and the
transaction commit LSN, while keeping the default plain mode 100%
byte-identical to master.

Changes in v3 (Addressing Manu's Review)
----------------------------------------

1. Wire Purity in Plain Mode (Removed GUC_REPORT):
ready_for_query_message no longer has GUC_REPORT. In plain mode, the
connection startup handshake sends zero additional ParameterStatus
messages, ensuring startup wire bytes are byte-identical to master.

2. Accurate Temporary Table Lifecycle ('T'):
In v2, 'T' remained 1 even after temporary objects were removed. In
v3, 'T' reliably resets to 0 after DROP TABLE, DISCARD TEMP, and
DISCARD ALL.
The catalog scan on pg_depend is executed at transaction pre-commit
(PreCommit_Namespace()) using SnapshotSelf, and only when rich mode
is enabled AND the transaction actually accessed temporary objects
(XACT_FLAGS_ACCESSEDTEMPNAMESPACE). Normal queries that do not touch
temporary objects incur zero catalog scan overhead.
Added assign_ready_for_query_message() GUC hook so that if
ready_for_query_message is switched from plain to rich mid-session,
any pre-existing temporary tables are immediately detected.

3. Session-Local Prepared Statements ('P'):
In v2, 'P' reflected cluster-wide two-phase commit transactions
(TwoPhaseState). In v3, 'P' tracks session-local active prepared
statements (O(1) check via HasActivePreparedStatements()).
All two-phase transaction changes from previous versions have been
reverted. 'P' correctly reflects PREPARE, DEALLOCATE, and DISCARD ALL.

4. Binary Commit LSN ('l'):
Replaced the string key 'L' (formatted with %X/%X) with binary key
'l', transmitting XactLastCommitEnd as an 8-byte unsigned integer in
network byte order (big-endian via pg_hton64). This avoids string
parsing overhead and padding discrepancies across platforms.

5. O(1) With-Hold Cursors ('H'):
In v2, the 'H' check traversed the full PortalHashTable on every
ReadyForQuery, scaling as O(cursors). In v3, portalmem.c maintains an
O(1) active_with_hold_portal_count, making the check strictly O(1) with
no table walks.

6. PQtrace() / fe-trace.c Compatibility:
Updated fe-trace.c (pqTraceOutput_ReadyForQuery) to parse and display
extension key-value pairs without triggering "mismatched message length"
warnings. All libpq_pipeline trace tests now pass under both plain and
rich modes.

7. Documentation on Unpatched Client Compatibility:
Retained PGC_USERSET for flexibility. Added explicit notes in
config.sgml and protocol.sgml clarifying that unpatched client
libraries will reject extended ReadyForQuery messages with length
mismatch errors, so rich mode should only be enabled in sessions
connecting via compatible clients or connection poolers.

8. Cleaned Tree:
Reverted unrelated changes in src/test/perl/PostgreSQL/Test/Cluster.pm.

Verification & Testing
----------------------

- test_ready_for_query: All 42 subtests in t/001_ready_for_query.pl pass,
verifying:
* Startup wire purity (no extra ParameterStatus in plain mode).
* Accurate 'T' transitions for CREATE TEMP TABLE, DROP TABLE,
DISCARD TEMP, and DISCARD ALL.
* O(1) 'H' behavior with 100 cursors inside a transaction and cursors
declared WITH HOLD.
* Binary 'l' unpacking and value matching.
* 'P' tracking of PREPARE, DEALLOCATE, and DISCARD ALL.
* Mid-session GUC switching via assign_ready_for_query_message().
- libpq_pipeline: All 24 tests pass cleanly.
- make check-world: Passed 100% across the full PostgreSQL test suite
with zero assertion failures, memory leaks, or ResourceOwner leaks.

The patch applies cleanly against current master (061065e28f).

Git branch: ready_for_query_hook (db3fd1023de) on
https://github.com/postsql/postgres.git.

Feedback and reviews are welcome!

Regards,
Hannu

On Fri, Oct 9, 2026 at 1:36 AM Manu <manuelreyesbravo(at)gmail(dot)com> wrote:
>
> Hi Hannu,
>
> Glad it was useful.
>
> > Hmm, this is what I get on PostgreSQL 16.4
> > [...] 0/17F5830
>
> On master pg_lsn now zero-pads the low word -- commit 2633dae2e48
> ("Standardize LSN formatting by zero padding", Jul 2025) made
> pg_lsn_out use %X/%08X. 16.4, 17 and 18 still use %X, so L matches
> pg_lsn there; on master it does not, since the patch formats L with
> %X. If L is meant to mirror pg_lsn, %08X would track the new format.
>
> Happy to re-test once you post the next version.
>
> Regards,
> Manu