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
v1-0001-Report-each-commit-s-WAL-position-to-the-client.patch
Description: Binary data
