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

Now it also passes CI

Hi Hannu,

I measured v2 on current master (it applies cleanly). Behaviourally
it is v1 plus the postgresql.conf.sample entry, so the observations
below hold on both versions; the attached scripts reproduce every
point. I looked at the behaviour rather than the design; some of this
may well be intended.

The good news first: in plain mode the cost is negligible -- +2
instructions per ReadyForQuery (+0.01%), tps within noise over two
runs, and the 'Z' message is byte-identical to master.

Two of the stated claims did not hold in my tests.

libpq (fe-protocol3.c) is updated so that clients ignore any trailing
extension bytes in ReadyForQuery messages if they receive them

An unpatched client does not tolerate a rich 'Z'. With master's own
libpq and with PG18's libpq, a rich session fails to connect:
message contents do not agree with length in message type "Z"
And since ready_for_query_message is PGC_USERSET, a client that
connects in plain and later runs SET ready_for_query_message = rich
hits the same error mid-session and hangs (I had to kill psql after
8s, twice).

Unless you set ready_for_query_message=rich the protocol does not
change at all

The 'Z' itself is unchanged, but because the GUC is GUC_REPORT the
patched server sends one extra ParameterStatus
(ready_for_query_message=plain) per connection that master does not.
Harmless, but not strictly no change.

On the session indicators, comparing the rich values with SQL in the
same session:
- T stays 1 after the temp objects are gone -- DROP, DISCARD TEMP and
DISCARD ALL all leave T=1 with an empty pg_class. A pooler would
read the connection as dirty permanently.
- P reflects the whole cluster, not the session: a session that never
prepared a transaction reports P=1 while another session has one
prepared.
- L matches pg_current_wal_insert_lsn() numerically, but it is printed
with %X where pg_lsn uses %08X, so the text differs only while the
low word is under 8 hex digits, e.g. on a fresh cluster (0/17F5830
vs 0/017F5830).
- H was correct in every case.

Cost of rich grows with open cursors. The H check walks the full
portal list on every 'Z', so it is O(cursors), not O(1): +5.9%
instructions per 'Z' on a clean session, +21% with 100 open cursors,
+160% with 1000. As 'Z' is on the per-query path, that may matter for
the pooling case the patch targets.

On CI: v2 turns it green by adding the GUC to postgresql.conf.sample
(that was the 003_check_guc failure). The rich path itself is
unchanged -- fe-trace.c is still not updated, so PQtrace() prints
"mismatched message length" on each rich 'Z' and the 7 libpq_pipeline
trace tests fail under rich; they pass in plain, which is what CI
runs. make check is 239/239 in both modes. The new test module
passes but does not exercise any of the cases above, and there is
still an unrelated change to Cluster.pm.

The attached README lists the exact steps for each point above.

Regards,
Manu

Thanks a lot for review, very useful!

On Thu, Oct 8, 2026 at 1:48 PM Manu <manuelreyesbravo@gmail.com> wrote:

Hi Hannu,

I measured v2 on current master (it applies cleanly). Behaviourally
it is v1 plus the postgresql.conf.sample entry, so the observations
below hold on both versions; the attached scripts reproduce every
point. I looked at the behaviour rather than the design; some of this
may well be intended.

The good news first: in plain mode the cost is negligible -- +2
instructions per ReadyForQuery (+0.01%), tps within noise over two
runs, and the 'Z' message is byte-identical to master.

Two of the stated claims did not hold in my tests.

libpq (fe-protocol3.c) is updated so that clients ignore any trailing
extension bytes in ReadyForQuery messages if they receive them

An unpatched client does not tolerate a rich 'Z'.

Sorry for being unclear - the protocol change is for the distant
future. The main protection is that one should not set it to rich when
client libarary can not handle this.

With master's own
libpq and with PG18's libpq, a rich session fails to connect:
message contents do not agree with length in message type "Z"
And since ready_for_query_message is PGC_USERSET, a client that
connects in plain and later runs SET ready_for_query_message = rich
hits the same error mid-session and hangs (I had to kill psql after
8s, twice).

This is indeed the current behaviour.

But as there are plenty of other ways to make the connection
unresponsive I am not too worried about such abuse.

Unless you set ready_for_query_message=rich the protocol does not
change at all

The 'Z' itself is unchanged, but because the GUC is GUC_REPORT the
patched server sends one extra ParameterStatus
(ready_for_query_message=plain) per connection that master does not.
Harmless, but not strictly no change.

Agreed, and there is really no need to be GUC_REPORT.

Will remove

On the session indicators, comparing the rich values with SQL in the
same session:
- T stays 1 after the temp objects are gone -- DROP, DISCARD TEMP and
DISCARD ALL all leave T=1 with an empty pg_class. A pooler would
read the connection as dirty permanently.

Ouch. Thanks!

- P reflects the whole cluster, not the session: a session that never
prepared a transaction reports P=1 while another session has one
prepared.

Will check.

I need to figure out how it even knows what other sessions have
prepared. That should be backend-local info...

- L matches pg_current_wal_insert_lsn() numerically, but it is printed
with %X where pg_lsn uses %08X, so the text differs only while the
low word is under 8 hex digits, e.g. on a fresh cluster (0/17F5830
vs 0/017F5830).

Hmm, this is what I get on PostgreSQL 16.4

hannuk=# select '0/017F5830'::pg_lsn;
pg_lsn
───────────
0/17F5830
(1 row)

Maybe there should also be 'l' which just returns the binary uint64 ?

- H was correct in every case.

Cost of rich grows with open cursors. The H check walks the full
portal list on every 'Z', so it is O(cursors), not O(1): +5.9%
instructions per 'Z' on a clean session, +21% with 100 open cursors,
+160% with 1000. As 'Z' is on the per-query path, that may matter for
the pooling case the patch targets.

Yup, at least the default should be boolean 0/1. If someone wants the
number of cursors they can write an extension :)

On CI: v2 turns it green by adding the GUC to postgresql.conf.sample
(that was the 003_check_guc failure). The rich path itself is
unchanged -- fe-trace.c is still not updated, so PQtrace() prints
"mismatched message length" on each rich 'Z' and the 7 libpq_pipeline
trace tests fail under rich; they pass in plain, which is what CI
runs. make check is 239/239 in both modes. The new test module
passes but does not exercise any of the cases above, and there is
still an unrelated change to Cluster.pm.

Thanks, will add that test as well.

The attached README lists the exact steps for each point above.

Thanks
Hannu

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

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

Hi Hannu,

I re-tested v3 on your base (061065e28f) and on today's master HEAD; it
applies cleanly to both. All eight points from the v2 review are
addressed and reproduce here -- plain mode is byte-identical to master
(startup 459 B, 'Z' 66 B), the extra ParameterStatus is gone, P is now
session-local, 'l' decodes to the COMMIT record's end_lsn, compat is
back (unpatched libpq completes in plain, rejects rich with the
documented length error), and check-world is clean under cassert (the
new module's 42 subtests, libpq_pipeline 24/24).

5. O(1) With-Hold Cursors ('H')

Confirmed, and it is a real win: the per-'Z' cost in rich went from
+41,703 instructions at 1000 open cursors in v2 to a flat +861 in v3.

Measuring the indicators against SQL in the same session, four edge
cases still report a state that does not match the catalog. Full
sequences are in the attached file; in short:

- T = 0 with a live temp table, two ways: (a) BEGIN; SET LOCAL
ready_for_query_message = plain; CREATE TEMP TABLE; COMMIT -- the
pre-commit check runs while the setting is plain, and the assign hook
rechecks only when IsTransactionState(); (b) switching to rich via
ALTER SYSTEM + pg_reload_conf() -- the SIGHUP is handled between
statements, so the hook skips the check. A plain SET does recheck.
- H = 1 with no cursors, after a procedure that COMMITs inside a FOR
loop: HoldPinnedPortals() -> HoldPortal() increments the counter, but
that portal has no CURSOR_OPT_HOLD, so PortalDrop() never decrements.
- H = 0 with a WITH HOLD cursor still open, if another WITH HOLD cursor
errors while materializing at COMMIT: the failed portal already has a
holdStore, so PortalDrop() decrements for it although HoldPortal()
never counted it.

Three of these (the two T cases and the last H case) are false
negatives -- a pooler would read the session as clean while it still
has state -- so they seem worth closing before the indicators are
relied on.

Two more, not contradicting any claim:

- Rich costs nothing extra when a transaction touches no temp objects,
as you say (-S prepared: within noise). When every transaction does
touch a temp table, rich is ~13-15% slower (two runs, both CIs below
zero), from the pg_depend scan at each pre-commit. Might be worth a
note that the 'T' bookkeeping has a cost under temp-heavy workloads.
- 'l' advances outside a commit. A read-only SELECT after a checkpoint
moved 'l' forward; pg_waldump shows only FPI_FOR_HINT and
PRUNE_ON_ACCESS records (xid 0) in that range. RecordTransactionCommit()
sets XactLastCommitEnd from XactLastRecEnd even for an xid-less
transaction that wrote WAL, so 'l' is never behind the last commit
(fine for read-your-writes) but is not exactly "the last commit LSN"
the docs describe. This is master's behaviour, not v3's.

Attached: the exact reproductions, and the wire client updated to
decode 'l'.

Regards,
Manu

Hi Hannu,

Measuring the indicators against SQL in the same session, four edge cases
still report a state that does not match the catalog.

I went back to this with sanitizers, a session generator with a catalog
oracle, and a protocol fuzzer, all on v3 over master 26bd08fb5c. A few things
worth flagging; the four v3 cases are only used here as controls.

Indicator desyncs -- the class is larger than those four. Over 24,000 random
sessions (246,368 oracle checks) the generator found five more mechanisms.
Three are false negatives with a live temporary table (T=0), which a pool
would read as a clean session; two need only one connection:

- CREATE TEMP TABLE t; BEGIN; DROP TABLE t; ROLLBACK -> T=0, but t is back.
- CREATE TEMP TABLE t; BEGIN; DISCARD TEMP; ROLLBACK -> T=0, t is back.
- a SERIALIZABLE COMMIT that drops a temp table and then fails with 40001
-> T=0, the table survives (two sessions).

The ROLLBACK/40001 restore and DISCARD-in-a-block are master's behaviour; what
the patch adds is the indicator not reflecting it. Two more are about what T
counts: a temp type or function created through pg_temp_N (not the pg_temp
alias) leaves T=0, and DROP SCHEMA pg_temp_N CASCADE leaves T=1 with nothing
there. The doc says "temporary tables" while the code counts more kinds of
temp object, so that one depends on which definition you intend. P and l had
no mismatches in the whole run.

ready_for_query_hook, error path: a hook that raises ERROR puts the backend
into a loop that sends ErrorResponse without stopping (about a million in two
seconds here), stays at 100% CPU after the client disconnects, and does not
respond to pg_terminate_backend() or "pg_ctl stop -m fast" -- only SIGKILL
ends it. Might be worth saying in the hook's contract that it must not throw,
or wrapping the call so a thrown error can't recurse through ReadyForQuery.

Compatibility: plain is transparent for libpq 14..master. rich at connect
fails cleanly ("message contents do not agree with length in message type
Z"). But turning rich on mid-session, by SET or by reload, hangs every
unpatched libpq I tried (7 of 7, 14 through master) -- so enabling it on a
connection an old client is already using is not safe. Also, patched libpq
drops the connection once a rich 'Z' is longer than 30,000 bytes (master's
long-message guard doesn't list 'Z'), so a hook with a large payload can trip
that.

Memory: the server was clean under ASan+UBSan across make check,
installcheck-parallel with every session in rich plus a hook on each
ReadyForQuery, the TAP suites, and the 24,000 generator sessions. One
client-side report: PQtrace reads past libpq's input buffer on a malformed
rich 'Z' (fe-trace.c), but master's tracer has the same over-read for a
DataRow, so it's a new instance of an existing pattern rather than something
new to the design.

Happy to share the generator, the minimal repros and the hook test module.

Regards,
Manu