From 447aae13b0305780e87cac7b0dd669db6fab3d9d Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Wed, 5 Nov 2025 11:43:55 +0200 Subject: Implement WAIT FOR command MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit WAIT FOR is to be used on standby and specifies waiting for the specific WAL location to be replayed. This option is useful when the user makes some data changes on primary and needs a guarantee to see these changes are on standby. WAIT FOR needs to wait without any snapshot held. Otherwise, the snapshot could prevent the replay of WAL records, implying a kind of self-deadlock. This is why separate utility command seems appears to be the most robust way to implement this functionality. It's not possible to implement this as a function. Previous experience shows that stored procedures also have limitation in this aspect. Discussion: https://www.postgresql.org/message-id/flat/CAPpHfdsjtZLVzxjGT8rJHCYbM0D5dwkO+BBjcirozJ6nYbOW8Q@mail.gmail.com Discussion: https://www.postgresql.org/message-id/flat/CABPTF7UNft368x-RgOXkfj475OwEbp%2BVVO-wEXz7StgjD_%3D6sw%40mail.gmail.com Author: Kartyshov Ivan Author: Alexander Korotkov Author: Xuneng Zhou Reviewed-by: Michael Paquier Reviewed-by: Peter Eisentraut Reviewed-by: Dilip Kumar Reviewed-by: Amit Kapila Reviewed-by: Alexander Lakhin Reviewed-by: Bharath Rupireddy Reviewed-by: Euler Taveira Reviewed-by: Heikki Linnakangas Reviewed-by: Kyotaro Horiguchi Reviewed-by: jian he Reviewed-by: Álvaro Herrera Reviewed-by: Xuneng Zhou --- doc/src/sgml/ref/wait_for.sgml | 234 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 234 insertions(+) create mode 100644 doc/src/sgml/ref/wait_for.sgml (limited to 'doc/src/sgml/ref/wait_for.sgml') diff --git a/doc/src/sgml/ref/wait_for.sgml b/doc/src/sgml/ref/wait_for.sgml new file mode 100644 index 00000000000..3b8e842d1de --- /dev/null +++ b/doc/src/sgml/ref/wait_for.sgml @@ -0,0 +1,234 @@ + + + + + WAIT FOR + + + + WAIT FOR + 7 + SQL - Language Statements + + + + WAIT FOR + wait for target LSN to be replayed, optionally with a timeout + + + + +WAIT FOR LSN 'lsn' [ WITH ( option [, ...] ) ] + +where option can be: + + TIMEOUT 'timeout' + NO_THROW + + + + + Description + + + Waits until recovery replays lsn. + If no timeout is specified or it is set to + zero, this command waits indefinitely for the + lsn. + On timeout, or if the server is promoted before + lsn is reached, an error is emitted, + unless NO_THROW is specified in the WITH clause. + If NO_THROW is specified, then the command + doesn't throw errors. + + + + The possible return values are success, + timeout, and not in recovery. + + + + + Parameters + + + + lsn + + + Specifies the target LSN to wait for. + + + + + + WITH ( option [, ...] ) + + + This clause specifies optional parameters for the wait operation. + The following parameters are supported: + + + + TIMEOUT 'timeout' + + + When specified and timeout is greater than zero, + the command waits until lsn is reached or + the specified timeout has elapsed. + + + The timeout might be given as integer number of + milliseconds. Also it might be given as string literal with + integer number of milliseconds or a number with unit + (see ). + + + + + + NO_THROW + + + Specify to not throw an error in the case of timeout or + running on the primary. In this case the result status can be get from + the return value. + + + + + + + + + + + + Outputs + + + + success + + + This return value denotes that we have successfully reached + the target lsn. + + + + + + timeout + + + This return value denotes that the timeout happened before reaching + the target lsn. + + + + + + not in recovery + + + This return value denotes that the database server is not in a recovery + state. This might mean either the database server was not in recovery + at the moment of receiving the command, or it was promoted before + reaching the target lsn. + + + + + + + + Notes + + + WAIT FOR command waits till + lsn to be replayed on standby. + That is, after this command execution, the value returned by + pg_last_wal_replay_lsn should be greater or equal + to the lsn value. This is useful to achieve + read-your-writes-consistency, while using async replica for reads and + primary for writes. In that case, the lsn of the last + modification should be stored on the client application side or the + connection pooler side. + + + + WAIT FOR command should be called on standby. + If a user runs WAIT FOR on primary, it + will error out unless NO_THROW is specified in the WITH clause. + However, if WAIT FOR is + called on primary promoted from standby and lsn + was already replayed, then the WAIT FOR command just + exits immediately. + + + + + + Examples + + + You can use WAIT FOR command to wait for + the pg_lsn value. For example, an application could update + the movie table and get the lsn after + changes just made. This example uses pg_current_wal_insert_lsn + on primary server to get the lsn given that + synchronous_commit could be set to + off. + + +postgres=# UPDATE movie SET genre = 'Dramatic' WHERE genre = 'Drama'; +UPDATE 100 +postgres=# SELECT pg_current_wal_insert_lsn(); +pg_current_wal_insert_lsn +-------------------- +0/306EE20 +(1 row) + + + Then an application could run WAIT FOR + with the lsn obtained from primary. After that the + changes made on primary should be guaranteed to be visible on replica. + + +postgres=# WAIT FOR LSN '0/306EE20'; + status +-------- + success +(1 row) +postgres=# SELECT * FROM movie WHERE genre = 'Drama'; + genre +------- +(0 rows) + + + + + If the target LSN is not reached before the timeout, the error is thrown. + + +postgres=# WAIT FOR LSN '0/306EE20' WITH (TIMEOUT '0.1s'); +ERROR: timed out while waiting for target LSN 0/306EE20 to be replayed; current replay LSN 0/306EA60 + + + + + The same example uses WAIT FOR with + NO_THROW option. + +postgres=# WAIT FOR LSN '0/306EE20' WITH (TIMEOUT '100ms', NO_THROW); + status +-------- + timeout +(1 row) + + + + -- cgit v1.2.3