Hi hackers, On Tue, Nov 25, 2025 at 7:51 PM Xuneng Zhou <[email protected]> wrote: > > Hi! > > > > > > At the moment, the WAIT FOR LSN command supports only the replay mode. > > > > > If we intend to extend its functionality more broadly, one option is > > > > > to add a mode option or something similar. Are users expected to wait > > > > > for flush(or others) completion in such cases? If not, and the TAP > > > > > test is the only intended use, this approach might be a bit of an > > > > > overkill. > > > > > > > > I would say that adding mode parameter seems to be a pretty natural > > > > extension of what we have at the moment. I can imagine some > > > > clustering solution can use it to wait for certain transaction to be > > > > flushed at the replica (without delaying the commit at the primary). > > > > > > > > ------ > > > > Regards, > > > > Alexander Korotkov > > > > Supabase > > > > > > Makes sense. I'll play with it and try to prepare a follow-up patch. > > > > > > -- > > > Best, > > > Xuneng > > > > In terms of extending the functionality of the command, I see two > > possible approaches here. One is to keep mode as a mandatory keyword, > > and the other is to introduce it as an option in the WITH clause. > > > > Syntax Option A: Mode in the WITH Clause > > > > WAIT FOR LSN '0/12345' WITH (mode = 'replay'); > > WAIT FOR LSN '0/12345' WITH (mode = 'flush'); > > WAIT FOR LSN '0/12345' WITH (mode = 'write'); > > > > With this option, we can keep "replay" as the default mode. That means > > existing TAP tests won’t need to be refactored unless they explicitly > > want a different mode. > > > > Syntax Option B: Mode as Part of the Main Command > > > > WAIT FOR LSN '0/12345' MODE 'replay'; > > WAIT FOR LSN '0/12345' MODE 'flush'; > > WAIT FOR LSN '0/12345' MODE 'write'; > > > > Or a more concise variant using keywords: > > > > WAIT FOR LSN '0/12345' REPLAY; > > WAIT FOR LSN '0/12345' FLUSH; > > WAIT FOR LSN '0/12345' WRITE; > > > > This option produces a cleaner syntax if the intent is simply to wait > > for a particular LSN type, without specifying additional options like > > timeout or no_throw. > > > > I don’t have a clear preference among them. I’d be interested to hear > > what you or others think is the better direction. > > > > I've implemented a patch that adds MODE support to WAIT FOR LSN > > The new grammar looks like: > > —— > WAIT FOR LSN '<lsn>' [MODE { REPLAY | WRITE | FLUSH }] [WITH (...)] > —— > > Two modes added: flush and write > > Design decisions: > > 1. MODE as a separate keyword (not in WITH clause) - This follows the > pattern used by LOCK command. It also makes the common case more > concise. > > 2. REPLAY as the default - When MODE is not specified, it defaults to REPLAY. > > 3. Keywords rather than strings - Using `MODE WRITE` rather than `MODE > 'write'` > > The patch set includes: > ------- > 0001 - Extend xlogwait infrastructure with write and flush wait types > > Adds WAIT_LSN_TYPE_WRITE and WAIT_LSN_TYPE_FLUSH to WaitLSNType enum, > along with corresponding wait events and pairing heaps. Introduces > GetCurrentLSNForWaitType() to retrieve the appropriate LSN based on > wait type, and adds wakeup calls in walreceiver for write/flush > events. > > ------- > 0002 - Add pg_last_wal_write_lsn() SQL function > > Adds a new SQL function that returns the current WAL write position on > a standby using GetWalRcvWriteRecPtr(). This complements existing > pg_last_wal_receive_lsn() (flush) and pg_last_wal_replay_lsn() > functions, enabling verification of WAIT FOR LSN MODE WRITE in TAP > tests. > > ------- > 0003 - Add MODE parameter to WAIT FOR LSN command > > Extends the parser and executor to support the optional MODE > parameter. Updates documentation with new syntax and mode > descriptions. Adds TAP tests covering all three modes including > mixed-mode concurrent waiters. > > ------- > 0004 - Add tab completion for WAIT FOR LSN MODE parameter > > Adds psql tab completion support: completes MODE after LSN value, > completes REPLAY/WRITE/FLUSH after MODE keyword, and completes WITH > after mode selection. > > ------- > 0005 - Use WAIT FOR LSN in PostgreSQL::Test::Cluster::wait_for_catchup() > > Replaces polling-based wait_for_catchup() with WAIT FOR LSN when the > target is a standby in recovery, improving test efficiency by avoiding > repeated queries. > > The WRITE and FLUSH modes enable scenarios where applications need to > ensure WAL has been received or persisted on the standby without > waiting for replay to complete. > > Feedback welcome. >
Here is the updated v2 patch set. Most of the updates are in patch 3. Changes from v1: Patch 1 (Extend wait types in xlogwait infra) - Renamed enum values for consistency (WAIT_LSN_TYPE_REPLAY → WAIT_LSN_TYPE_REPLAY_STANDBY, etc.) Patch 2 (pg_last_wal_write_lsn): - Clarified documentation and comment - Improved pg_proc.dat description Patch 3 (MODE parameter): - Replaced direct cast with explicit switch statement for WaitLSNMode → WaitLSNType conversion - Improved FLUSH/WRITE mode documentation with verification function references - TAP tests (7b, 7c, 7d): Added walreceiver control for concurrency, explicit blocking verification via poll_query_until, and log-based completion verification via wait_for_log - Fix the timing issue in wait for all three sessions to get the errors after promotion of tap test 8. -- Best, Xuneng
v2-0005-Use-WAIT-FOR-LSN-in.patch
Description: Binary data
v2-0004-Add-tab-completion-for-WAIT-FOR-LSN-MODE-paramete.patch
Description: Binary data
v2-0002-Add-pg_last_wal_write_lsn-SQL-function.patch
Description: Binary data
v2-0001-Extend-xlogwait-infrastructure-with-write-and-flu.patch
Description: Binary data
v2-0003-Add-MODE-parameter-to-WAIT-FOR-LSN-command.patch
Description: Binary data
