| From: | Colin McDonnell <colinmcd94(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Cc: | postgres(at)jeltef(dot)nl, peter(at)eisentraut(dot)org, hlinnaka(at)iki(dot)fi, hannuk(at)google(dot)com, aekorotkov(at)gmail(dot)com |
| Subject: | [PATCH] Report each commit's WAL position to the client |
| Date: | 2026-11-20 06:32:01 |
| Message-ID: | CALdYYtTtj_5FbKiz8JuoovHuT_fc_yMi89qCkh=kXDS5jVPriQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi hackers,
With WAIT FOR LSN in 19, a client that writes on the primary and reads
from a streaming standby can see its own writes there, as long as it
knows an LSN at or after the end of its commit record. Getting one
today takes another statement after each write, usually
pg_current_wal_insert_lsn(), which also covers everyone else's WAL, so
the standby wait can be longer than it needs to be.
A driver can pipeline that query so it costs no round trip, but it
still has to add it after every statement that might have written,
since it can't tell from the SQL (a SELECT can call a function that
writes). It also has to keep the query out of the write's own
transaction: sent in the same simple Query as an autocommit INSERT, it
runs before the commit and returns an LSN that the standby passes too
early. And a pooler that routes reads to standbys would have to inject
it into its clients' traffic.
The attached patch has the server send it with the commit's own
response. It adds two settings:
- last_commit_lsn: read-only, the end of the commit record of the
session's most recent commit, or 0/00000000 if there is none.
- report_commit_lsn: off by default. When on, the server sends
last_commit_lsn in a ParameterStatus message whenever it has changed,
just before ReadyForQuery.
An autocommit INSERT with it on:
C: Parse / Bind / Execute / Sync
S: ParseComplete
S: BindComplete
S: CommandComplete INSERT 0 1
S: ParameterStatus last_commit_lsn=0/017C2A90
S: ReadyForQuery I
No message format changes, and nothing new for clients that don't turn
it on. The protocol already allows ParameterStatus at any time and tells
frontends to ignore parameters they don't know, so existing drivers and
poolers cope. libpq applications read it with PQparameterStatus(), and
pgbouncer forwards it in transaction mode, so a pooler can turn it on
for its server connections and every client sees its own commits. LSNs
aren't new to clients either: any role can call pg_current_wal_lsn().
This has come up before. In Peter's protocol-level wait-for-LSN thread
[1], Jelte asked whether the LSN could be a read-only GUC reported
through ParameterStatus, which is what this does. Hannu's extensible
ReadyForQuery patch [2] carries the same LSN, but in a longer 'Z'
message that unpatched clients can't parse. And with Jelte's
report_parameters [3], a client could ask for last_commit_lsn by name,
and report_commit_lsn would go away.
Some details:
- The value moves only when a commit record is written: COMMIT of a
transaction with an XID, and COMMIT PREPARED. Read-only transactions,
rollbacks, PREPARE TRANSACTION, and transactions that wrote WAL
without an XID (pruning, for example) leave it alone. That's why it
has its own variable instead of XactLastCommitEnd, which also moves in
that last case.
- Like other ParameterStatus reports, it goes out at ReadyForQuery, so a
pipeline with several commits and one Sync reports only the last.
That's the one read-your-writes needs.
- last_commit_lsn isn't GUC_REPORT. ReportChangedGUCOptions sends it
when report_commit_lsn is on, next to the in_hot_standby check.
- It's a record end, so it never points past a page header, unlike
pg_current_wal_insert_lsn() (see 24723e7cf3).
- Parallel workers get the leader's value, so it reads the same in a
parallel query.
One open question: whether the opt-in should be a _pq_ protocol option
rather than a setting. As a setting, a client can't put it in the
startup packet without knowing the server has it, since older servers
reject the unknown parameter, so drivers will mostly SET it after
connecting. A _pq_.report_commit_lsn would degrade cleanly through
NegotiateProtocolVersion instead. I went with the setting because no
_pq_ option exists yet and it keeps the patch small, but I'm happy to
switch.
Tests: a regression test for the setting, a libpq_pipeline test for the
reports (pipelined too), and a recovery test that waits on a delayed
standby for the reported value after an autocommit INSERT, an explicit
transaction, a procedure that commits twice, and COMMIT PREPARED.
check-world passes.
I wrote this with an AI assistant (Claude).
[1]
https://postgr.es/m/8e6ba982-fd41-4779-8729-eb120df69ea0%40eisentraut.org
[2]
https://postgr.es/m/CAMT0RQSP2Qopgr%2B5Z5NH1wur17%3D2fs0VAn5v3F8TtyQEApCmAQ%40mail.gmail.com
[3]
https://postgr.es/m/CAGECzQScQ3N-Ykv2j4NDyDtrPPc3FpRoa%3DLZ-2Uj2ocA4zr%3D4Q%40mail.gmail.com
Regards,
Colin McDonnell
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Report-each-commit-s-WAL-position-to-the-client.patch | application/octet-stream | 28.5 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Fujii Masao | 2026-11-20 06:43:18 | Re: Disable startup progress timeout during standby WAL replay |
| Previous Message | Mingjie Gao | 2026-11-20 06:20:39 | Re: [PATCH v1] Fix initial sync of zero-column published tables |