Subject: Implement waiting for wal lsn replay: reloaded

From: Alexander Korotkov <[email-masked]>
Date: Wed, 27 Nov 2024 06:08:51 +0200

Hi!

Introduction

The simple way to wait for a given lsn to replay on standby appears to
be useful because it provides a way to achieve read-your-writes
consistency while working with both replication leader and standby.
And it's both handy and cheaper to have built-in functionality for
that instead of polling pg_last_wal_replay_lsn().

Key problem

While this feature generally looks trivial, there is a surprisingly
hard problem.  While waiting for an LSN to replay, you should hold any
snapshots.  If you hold a snapshot on standby, that snapshot could
prevent the replay of WAL records.  In turn, that could prevent the
wait to finish, causing a kind of deadlock.  Therefore, waiting for
LSN to replay couldn't be implemented as a function.  My last attempt
implements this functionality as a stored procedure [1].  This
approach generally works but has a couple of serious limitations.
1) Given that a CALL statement has to lookup a catalog for the stored
procedure, we can't work inside a transaction of REPEATABLE READ or a
higher isolation level (even if nothing has been done before in that
transaction).  It is especially unpleasant that this limitation covers
the case of the implicit transaction when
default_transaction_isolation = 'repeatable read' [2].  I had a
workaround for that [3], but it looks a bit awkward.
2) Using output parameters for a stored procedure causes an extra
snapshot to be held.  And that snapshot is difficult (unsafe?) to
release [3].

Present solution

The present patch implements a new utility command WAIT FOR LSN
'target_lsn' [, TIMEOUT 'timeout'][, THROW 'throw'].  Unlike previous
attempts to implement custom syntax, it uses only one extra unreserved
keyword.  The parameters are implemented as generic_option_list.

Custom syntax eliminates the problem of running within an empty
transaction of REPEATABLE READ level or higher.  We don't need to
lookup a system catalog.  Thus, we have to set a transaction snapshot.

Also, revising PlannedStmtRequiresSnapshot() allows us to avoid
holding a snapshot to return a value.  Therefore, the WAIT command in
the attached patch returns its result status.

Also, the attached patch explicitly checks if the standby has been
promoted to throw the most relevant form of an error.  The issue of
inaccurate error messages has been previously spotted in [5].

Any comments?

Links.
1. https://www.postgresql.org/message-id/E1sZwuz-002NPQ-Lc%40gemulon.postgresql.org
2. https://www.postgresql.org/message-id/14de8671-e328-4c3e-b136-664f6f13a39f%40iki.fi
3. https://www.postgresql.org/message-id/CAPpHfdvRmTzGJw5rQdSMkTxUPZkjwtbQ%3DLJE2u9Jqh9gFXHpmg%40mail.gmail.com
4. https://www.postgresql.org/message-id/4953563546cb8c8851f84c7debf723ef%40postgrespro.ru
5. https://www.postgresql.org/message-id/ab0eddce-06d4-4db2-87ce-46fa2427806c%40iki.fi

------
Regards,
Alexander Korotkov
Supabase

---

From: Kirill Reshke <[email-masked]>
Date: Wed, 4 Dec 2024 16:12:07 +0500

Hi!

What's the current status of
https://commitfest.postgresql.org/50/5167/ ? Should we close it or
reattach to this thread?

-- 
Best regards,
Kirill Reshke

---

From: Andrei Lepikhov <[email-masked]>
Date: Thu, 6 Feb 2025 14:42:12 +0700

> https://commitfest.postgresql.org/50/5167/ ? Should we close it or
To push this feature further I rebased the patch onto current master.
Also, let's add a commitfest entry:
https://commitfest.postgresql.org/52/5550/

-- 
regards, Andrei Lepikhov

From ea224b84d343ea726f47af30a7a974e0736d79cc Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email-masked]>
Date: Thu, 6 Feb 2025 14:13:09 +0700
Subject: [PATCH v2] Implement WAIT FOR command

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.

The queue of waiters is stored in the shared memory as an LSN-ordered pairing
heap, where the waiter with the nearest LSN stays on the top.  During
the replay of WAL, waiters whose LSNs have already been replayed are deleted
from the shared memory pairing heap and woken up by setting their latches.

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://postgr.es/m/eb12f9b03851bb2583adab5df9579b4b%40postgrespro.ru
Author: Kartyshov Ivan, Alexander Korotkov
Reviewed-by: Michael Paquier, Peter Eisentraut, Dilip Kumar, Amit Kapila
Reviewed-by: Alexander Lakhin, Bharath Rupireddy, Euler Taveira
Reviewed-by: Heikki Linnakangas, Kyotaro Horiguchi
---
 doc/src/sgml/ref/allfiles.sgml                |   1 +
 doc/src/sgml/reference.sgml                   |   1 +
 src/backend/access/transam/Makefile           |   3 +-
 src/backend/access/transam/meson.build        |   1 +
 src/backend/access/transam/xact.c             |   6 +
 src/backend/access/transam/xlog.c             |   7 +
 src/backend/access/transam/xlogrecovery.c     |  11 +
 src/backend/access/transam/xlogwait.c         | 336 ++++++++++++++++++
 src/backend/commands/Makefile                 |   3 +-
 src/backend/commands/meson.build              |   1 +
 src/backend/commands/wait.c                   | 185 ++++++++++
 src/backend/lib/pairingheap.c                 |  18 +-
 src/backend/parser/gram.y                     |  15 +-
 src/backend/storage/ipc/ipci.c                |   3 +
 src/backend/storage/lmgr/proc.c               |   6 +
 src/backend/tcop/pquery.c                     |  12 +-
 src/backend/tcop/utility.c                    |  22 ++
 .../utils/activity/wait_event_names.txt       |   2 +
 src/include/access/xlogwait.h                 |  89 +++++
 src/include/commands/wait.h                   |  21 ++
 src/include/lib/pairingheap.h                 |   3 +
 src/include/nodes/parsenodes.h                |   7 +
 src/include/parser/kwlist.h                   |   1 +
 src/include/storage/lwlocklist.h              |   1 +
 src/include/tcop/cmdtaglist.h                 |   1 +
 src/test/recovery/meson.build                 |   1 +
 src/test/recovery/t/044_wait_for_lsn.pl       | 217 +++++++++++
 src/tools/pgindent/typedefs.list              |   4 +
 28 files changed, 967 insertions(+), 11 deletions(-)
 create mode 100644 src/backend/access/transam/xlogwait.c
 create mode 100644 src/backend/commands/wait.c
 create mode 100644 src/include/access/xlogwait.h
 create mode 100644 src/include/commands/wait.h
 create mode 100644 src/test/recovery/t/044_wait_for_lsn.pl

diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index f5be638867..8b585cba75 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -188,6 +188,7 @@ Complete list of usable sgml source files in this directory.
 <!ENTITY update             SYSTEM "update.sgml">
 <!ENTITY vacuum             SYSTEM "vacuum.sgml">
 <!ENTITY values             SYSTEM "values.sgml">
+<!ENTITY wait               SYSTEM "wait.sgml">
 
 <!-- applications and utilities -->
 <!ENTITY clusterdb          SYSTEM "clusterdb.sgml">
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index ff85ace83f..bd14ec00d2 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -216,6 +216,7 @@
    &update;
    &vacuum;
    &values;
+   &wait;
 
  </reference>
 
diff --git a/src/backend/access/transam/Makefile b/src/backend/access/transam/Makefile
index 661c55a9db..a32f473e0a 100644
--- a/src/backend/access/transam/Makefile
+++ b/src/backend/access/transam/Makefile
@@ -36,7 +36,8 @@ OBJS = \
 	xlogreader.o \
 	xlogrecovery.o \
 	xlogstats.o \
-	xlogutils.o
+	xlogutils.o \
+	xlogwait.o
 
 include $(top_srcdir)/src/backend/common.mk
 
diff --git a/src/backend/access/transam/meson.build b/src/backend/access/transam/meson.build
index e8ae9b13c8..74a62ab3ea 100644
--- a/src/backend/access/transam/meson.build
+++ b/src/backend/access/transam/meson.build
@@ -24,6 +24,7 @@ backend_sources += files(
   'xlogrecovery.c',
   'xlogstats.c',
   'xlogutils.c',
+  'xlogwait.c',
 )
 
 # used by frontend programs to build a frontend xlogreader
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index d331ab90d7..8336bb0cd1 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -31,6 +31,7 @@
 #include "access/xloginsert.h"
 #include "access/xlogrecovery.h"
 #include "access/xlogutils.h"
+#include "access/xlogwait.h"
 #include "catalog/index.h"
 #include "catalog/namespace.h"
 #include "catalog/pg_enum.h"
@@ -2826,6 +2827,11 @@ AbortTransaction(void)
 	 */
 	LWLockReleaseAll();
 
+	/*
+	 * Cleanup waiting for LSN if any.
+	 */
+	WaitLSNCleanup();
+
 	/* Clear wait information and command progress indicator */
 	pgstat_report_wait_end();
 	pgstat_progress_end_command();
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 9c270e7d46..62c37f31ee 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -62,6 +62,7 @@
 #include "access/xlogreader.h"
 #include "access/xlogrecovery.h"
 #include "access/xlogutils.h"
+#include "access/xlogwait.h"
 #include "backup/basebackup.h"
 #include "catalog/catversion.h"
 #include "catalog/pg_control.h"
@@ -6194,6 +6195,12 @@ StartupXLOG(void)
 	UpdateControlFile();
 	LWLockRelease(ControlFileLock);
 
+	/*
+	 * Wake up all waiters for replay LSN.  They need to report an error that
+	 * recovery was ended before reaching the target LSN.
+	 */
+	WaitLSNWakeup(InvalidXLogRecPtr);
+
 	/*
 	 * Shutdown the recovery environment.  This must occur after
 	 * RecoverPreparedTransactions() (see notes in lock_twophase_recover())
diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c
index 473de6710d..5364576ca5 100644
--- a/src/backend/access/transam/xlogrecovery.c
+++ b/src/backend/access/transam/xlogrecovery.c
@@ -40,6 +40,7 @@
 #include "access/xlogreader.h"
 #include "access/xlogrecovery.h"
 #include "access/xlogutils.h"
+#include "access/xlogwait.h"
 #include "backup/basebackup.h"
 #include "catalog/pg_control.h"
 #include "commands/tablespace.h"
@@ -1829,6 +1830,16 @@ PerformWalRecovery(void)
 				break;
 			}
 
+			/*
+			 * If we replayed an LSN that someone was waiting for then walk
+			 * over the shared memory array and set latches to notify the
+			 * waiters.
+			 */
+			if (waitLSNState &&
+				(XLogRecoveryCtl->lastReplayedEndRecPtr >=
+				 pg_atomic_read_u64(&waitLSNState->minWaitedLSN)))
+				WaitLSNWakeup(XLogRecoveryCtl->lastReplayedEndRecPtr);
+
 			/* Else, try to fetch the next WAL record */
 			record = ReadRecord(xlogprefetcher, LOG, false, replayTLI);
 		} while (record != NULL);
diff --git a/src/backend/access/transam/xlogwait.c b/src/backend/access/transam/xlogwait.c
new file mode 100644
index 0000000000..313c8cc35d
--- /dev/null
+++ b/src/backend/access/transam/xlogwait.c
@@ -0,0 +1,336 @@
+/*-------------------------------------------------------------------------
+ *
+ * xlogwait.c
+ *	  Implements waiting for the given replay LSN, which is used in
+ *	  WAIT FOR lsn '...'
+ *
+ * Copyright (c) 2024, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ *	  src/backend/access/transam/xlogwait.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include <float.h>
+#include <math.h>
+
+#include "access/xlog.h"
+#include "access/xlogrecovery.h"
+#include "access/xlogwait.h"
+#include "miscadmin.h"
+#include "pgstat.h"
+#include "storage/latch.h"
+#include "storage/proc.h"
+#include "storage/shmem.h"
+#include "utils/fmgrprotos.h"
+#include "utils/pg_lsn.h"
+#include "utils/snapmgr.h"
+
+static int	waitlsn_cmp(const pairingheap_node *a, const pairingheap_node *b,
+						void *arg);
+
+struct WaitLSNState *waitLSNState = NULL;
+
+/* Report the amount of shared memory space needed for WaitLSNState. */
+Size
+WaitLSNShmemSize(void)
+{
+	Size		size;
+
+	size = offsetof(WaitLSNState, procInfos);
+	size = add_size(size, mul_size(MaxBackends, sizeof(WaitLSNProcInfo)));
+	return size;
+}
+
+/* Initialize the WaitLSNState in the shared memory. */
+void
+WaitLSNShmemInit(void)
+{
+	bool		found;
+
+	waitLSNState = (WaitLSNState *) ShmemInitStruct("WaitLSNState",
+													WaitLSNShmemSize(),
+													&found);
+	if (!found)
+	{
+		pg_atomic_init_u64(&waitLSNState->minWaitedLSN, PG_UINT64_MAX);
+		pairingheap_initialize(&waitLSNState->waitersHeap, waitlsn_cmp, NULL);
+		memset(&waitLSNState->procInfos, 0, MaxBackends * sizeof(WaitLSNProcInfo));
+	}
+}
+
+/*
+ * Comparison function for waitLSN->waitersHeap heap.  Waiting processes are
+ * ordered by lsn, so that the waiter with smallest lsn is at the top.
+ */
+static int
+waitlsn_cmp(const pairingheap_node *a, const pairingheap_node *b, void *arg)
+{
+	const WaitLSNProcInfo *aproc = pairingheap_const_container(WaitLSNProcInfo, phNode, a);
+	const WaitLSNProcInfo *bproc = pairingheap_const_container(WaitLSNProcInfo, phNode, b);
+
+	if (aproc->waitLSN < bproc->waitLSN)
+		return 1;
+	else if (aproc->waitLSN > bproc->waitLSN)
+		return -1;
+	else
+		return 0;
+}
+
+/*
+ * Update waitLSN->minWaitedLSN according to the current state of
+ * waitLSN->waitersHeap.
+ */
+static void
+updateMinWaitedLSN(void)
+{
+	XLogRecPtr	minWaitedLSN = PG_UINT64_MAX;
+
+	if (!pairingheap_is_empty(&waitLSNState->waitersHeap))
+	{
+		pairingheap_node *node = pairingheap_first(&waitLSNState->waitersHeap);
+
+		minWaitedLSN = pairingheap_container(WaitLSNProcInfo, phNode, node)->waitLSN;
+	}
+
+	pg_atomic_write_u64(&waitLSNState->minWaitedLSN, minWaitedLSN);
+}
+
+/*
+ * Put the current process into the heap of LSN waiters.
+ */
+static void
+addLSNWaiter(XLogRecPtr lsn)
+{
+	WaitLSNProcInfo *procInfo = &waitLSNState->procInfos[MyProcNumber];
+
+	LWLockAcquire(WaitLSNLock, LW_EXCLUSIVE);
+
+	Assert(!procInfo->inHeap);
+
+	procInfo->procno = MyProcNumber;
+	procInfo->waitLSN = lsn;
+
+	pairingheap_add(&waitLSNState->waitersHeap, &procInfo->phNode);
+	procInfo->inHeap = true;
+	updateMinWaitedLSN();
+
+	LWLockRelease(WaitLSNLock);
+}
+
+/*
+ * Remove the current process from the heap of LSN waiters if it's there.
+ */
+static void
+deleteLSNWaiter(void)
+{
+	WaitLSNProcInfo *procInfo = &waitLSNState->procInfos[MyProcNumber];
+
+	LWLockAcquire(WaitLSNLock, LW_EXCLUSIVE);
+
+	if (!procInfo->inHeap)
+	{
+		LWLockRelease(WaitLSNLock);
+		return;
+	}
+
+	pairingheap_remove(&waitLSNState->waitersHeap, &procInfo->phNode);
+	procInfo->inHeap = false;
+	updateMinWaitedLSN();
+
+	LWLockRelease(WaitLSNLock);
+}
+
+/*
+ * Remove waiters whose LSN has been replayed from the heap and set their
+ * latches.  If InvalidXLogRecPtr is given, remove all waiters from the heap
+ * and set latches for all waiters.
+ */
+void
+WaitLSNWakeup(XLogRecPtr currentLSN)
+{
+	int			i;
+	ProcNumber *wakeUpProcs;
+	int			numWakeUpProcs = 0;
+
+	wakeUpProcs = palloc(sizeof(ProcNumber) * MaxBackends);
+
+	LWLockAcquire(WaitLSNLock, LW_EXCLUSIVE);
+
+	/*
+	 * Iterate the pairing heap of waiting processes till we find LSN not yet
+	 * replayed.  Record the process numbers to wake up, but to avoid holding
+	 * the lock for too long, send the wakeups only after releasing the lock.
+	 */
+	while (!pairingheap_is_empty(&waitLSNState->waitersHeap))
+	{
+		pairingheap_node *node = pairingheap_first(&waitLSNState->waitersHeap);
+		WaitLSNProcInfo *procInfo = pairingheap_container(WaitLSNProcInfo, phNode, node);
+
+		if (!XLogRecPtrIsInvalid(currentLSN) &&
+			procInfo->waitLSN > currentLSN)
+			break;
+
+		wakeUpProcs[numWakeUpProcs++] = procInfo->procno;
+		(void) pairingheap_remove_first(&waitLSNState->waitersHeap);
+		procInfo->inHeap = false;
+	}
+
+	updateMinWaitedLSN();
+
+	LWLockRelease(WaitLSNLock);
+
+	/*
+	 * Set latches for processes, whose waited LSNs are already replayed. As
+	 * the time consuming operations, we do it this outside of WaitLSNLock.
+	 * This is  actually fine because procLatch isn't ever freed, so we just
+	 * can potentially set the wrong process' (or no process') latch.
+	 */
+	for (i = 0; i < numWakeUpProcs; i++)
+	{
+		SetLatch(&GetPGProcByNumber(wakeUpProcs[i])->procLatch);
+	}
+	pfree(wakeUpProcs);
+}
+
+/*
+ * Delete our item from shmem array if any.
+ */
+void
+WaitLSNCleanup(void)
+{
+	/*
+	 * We do a fast-path check of the 'inHeap' flag without the lock.  This
+	 * flag is set to true only by the process itself.  So, it's only possible
+	 * to get a false positive.  But that will be eliminated by a recheck
+	 * inside deleteLSNWaiter().
+	 */
+	if (waitLSNState->procInfos[MyProcNumber].inHeap)
+		deleteLSNWaiter();
+}
+
+/*
+ * Wait using MyLatch till the given LSN is replayed, the postmaster dies or
+ * timeout happens.
+ */
+WaitLSNResult
+WaitForLSNReplay(XLogRecPtr targetLSN, int64 timeout)
+{
+	XLogRecPtr	currentLSN;
+	TimestampTz endtime = 0;
+	int			wake_events = WL_LATCH_SET | WL_POSTMASTER_DEATH;
+
+	/* Shouldn't be called when shmem isn't initialized */
+	Assert(waitLSNState);
+
+	/* Should have a valid proc number */
+	Assert(MyProcNumber >= 0 && MyProcNumber < MaxBackends);
+
+	if (!RecoveryInProgress())
+	{
+		/*
+		 * Recovery is not in progress.  Given that we detected this in the
+		 * very first check, this procedure was mistakenly called on primary.
+		 * However, it's possible that standby was promoted concurrently to
+		 * the procedure call, while target LSN is replayed.  So, we still
+		 * check the last replay LSN before reporting an error.
+		 */
+		if (PromoteIsTriggered() && targetLSN <= GetXLogReplayRecPtr(NULL))
+			return WAIT_LSN_RESULT_SUCCESS;
+		return WAIT_LSN_RESULT_NOT_IN_RECOVERY;
+	}
+	else
+	{
+		/* If target LSN is already replayed, exit immediately */
+		if (targetLSN <= GetXLogReplayRecPtr(NULL))
+			return WAIT_LSN_RESULT_SUCCESS;
+	}
+
+	if (timeout > 0)
+	{
+		endtime = TimestampTzPlusMilliseconds(GetCurrentTimestamp(), timeout);
+		wake_events |= WL_TIMEOUT;
+	}
+
+	/*
+	 * Add our process to the pairing heap of waiters.  It might happen that
+	 * target LSN gets replayed before we do.  Another check at the beginning
+	 * of the loop below prevents the race condition.
+	 */
+	addLSNWaiter(targetLSN);
+
+	for (;;)
+	{
+		int			rc;
+		long		delay_ms = 0;
+
+		/* Recheck that recovery is still in-progress */
+		if (!RecoveryInProgress())
+		{
+			/*
+			 * Recovery was ended, but recheck if target LSN was already
+			 * replayed.  See the comment regarding deleteLSNWaiter() below.
+			 */
+			deleteLSNWaiter();
+			currentLSN = GetXLogReplayRecPtr(NULL);
+			if (PromoteIsTriggered() && targetLSN <= currentLSN)
+				return WAIT_LSN_RESULT_SUCCESS;
+			return WAIT_LSN_RESULT_NOT_IN_RECOVERY;
+		}
+		else
+		{
+			/* Check if the waited LSN has been replayed */
+			currentLSN = GetXLogReplayRecPtr(NULL);
+			if (targetLSN <= currentLSN)
+				break;
+		}
+
+		/*
+		 * If the timeout value is specified, calculate the number of
+		 * milliseconds before the timeout.  Exit if the timeout is already
+		 * reached.
+		 */
+		if (timeout > 0)
+		{
+			delay_ms = TimestampDifferenceMilliseconds(GetCurrentTimestamp(), endtime);
+			if (delay_ms <= 0)
+				break;
+		}
+
+		CHECK_FOR_INTERRUPTS();
+
+		rc = WaitLatch(MyLatch, wake_events, delay_ms,
+					   WAIT_EVENT_WAIT_FOR_WAL_REPLAY);
+
+		/*
+		 * Emergency bailout if postmaster has died.  This is to avoid the
+		 * necessity for manual cleanup of all postmaster children.
+		 */
+		if (rc & WL_POSTMASTER_DEATH)
+			ereport(FATAL,
+					(errcode(ERRCODE_ADMIN_SHUTDOWN),
+					 errmsg("terminating connection due to unexpected postmaster exit"),
+					 errcontext("while waiting for LSN replay")));
+
+		if (rc & WL_LATCH_SET)
+			ResetLatch(MyLatch);
+	}
+
+	/*
+	 * Delete our process from the shared memory pairing heap.  We might
+	 * already be deleted by the startup process.  The 'inHeap' flag prevents
+	 * us from the double deletion.
+	 */
+	deleteLSNWaiter();
+
+	/*
+	 * If we didn't reach the target LSN, we must be exited by timeout.
+	 */
+	if (targetLSN > currentLSN)
+		return WAIT_LSN_RESULT_TIMEOUT;
+
+	return WAIT_LSN_RESULT_SUCCESS;
+}
diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile
index 48f7348f91..d8f6965d8c 100644
--- a/src/backend/commands/Makefile
+++ b/src/backend/commands/Makefile
@@ -61,6 +61,7 @@ OBJS = \
 	vacuum.o \
 	vacuumparallel.o \
 	variable.o \
-	view.o
+	view.o \
+	wait.o
 
 include $(top_srcdir)/src/backend/common.mk
diff --git a/src/backend/commands/meson.build b/src/backend/commands/meson.build
index ef0d407a38..f5db28bbd2 100644
--- a/src/backend/commands/meson.build
+++ b/src/backend/commands/meson.build
@@ -50,4 +50,5 @@ backend_sources += files(
   'vacuumparallel.c',
   'variable.c',
   'view.c',
+  'wait.c',
 )
diff --git a/src/backend/commands/wait.c b/src/backend/commands/wait.c
new file mode 100644
index 0000000000..8351733500
--- /dev/null
+++ b/src/backend/commands/wait.c
@@ -0,0 +1,185 @@
+/*-------------------------------------------------------------------------
+ *
+ * wait.c
+ *	  Implements WAIT FOR, which allows waiting for events such as
+ *	  time passing or LSN having been replayed on replica.
+ *
+ * Portions Copyright (c) 2024, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ *	  src/backend/commands/wait.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/xlogrecovery.h"
+#include "access/xlogwait.h"
+#include "catalog/pg_collation_d.h"
+#include "commands/wait.h"
+#include "executor/executor.h"
+#include "storage/proc.h"
+#include "utils/builtins.h"
+#include "utils/fmgrprotos.h"
+#include "utils/formatting.h"
+#include "utils/pg_lsn.h"
+#include "utils/snapmgr.h"
+
+void
+ExecWaitStmt(WaitStmt *stmt, DestReceiver *dest)
+{
+	XLogRecPtr	lsn = InvalidXLogRecPtr;
+	int64		timeout = 0;
+	WaitLSNResult waitLSNResult;
+	bool		throw = true;
+	TupleDesc	tupdesc;
+	TupOutputState *tstate;
+	char	   *result;
+
+	/*
+	 * Process the list of parameters.
+	 */
+	foreach_node(DefElem, defel, stmt->options)
+	{
+		char	   *name = str_tolower(defel->defname, strlen(defel->defname),
+									   DEFAULT_COLLATION_OID);
+
+		if (strcmp(name, "lsn") == 0)
+		{
+			lsn = DatumGetLSN(DirectFunctionCall1(pg_lsn_in,
+												  CStringGetDatum(strVal(defel->arg))));
+		}
+		else if (strcmp(name, "timeout") == 0)
+		{
+			timeout = pg_strtoint64(strVal(defel->arg));
+		}
+		else if (strcmp(name, "throw") == 0)
+		{
+			throw = DatumGetBool(DirectFunctionCall1(boolin,
+													 CStringGetDatum(strVal(defel->arg))));
+		}
+		else
+		{
+			ereport(ERROR,
+					(errcode(ERRCODE_SYNTAX_ERROR),
+					 errmsg("wrong wait argument: %s",
+							defel->defname)));
+		}
+	}
+
+	if (XLogRecPtrIsInvalid(lsn))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_PARAMETER),
+				 errmsg("\"lsn\" must be specified")));
+
+	if (timeout < 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				 errmsg("\"timeout\" must not be negative")));
+
+	/*
+	 * We are going to wait for the LSN replay.  We should first care that we
+	 * don't hold a snapshot and correspondingly our MyProc->xmin is invalid.
+	 * Otherwise, our snapshot could prevent the replay of WAL records
+	 * implying a kind of self-deadlock.  This is the reason why
+	 * pg_wal_replay_wait() is a procedure, not a function.
+	 *
+	 * At first, we should check there is no active snapshot.  According to
+	 * PlannedStmtRequiresSnapshot(), even in an atomic context, CallStmt is
+	 * processed with a snapshot.  Thankfully, we can pop this snapshot,
+	 * because PortalRunUtility() can tolerate this.
+	 */
+	if (ActiveSnapshotSet())
+		PopActiveSnapshot();
+
+	/*
+	 * At second, invalidate a catalog snapshot if any.  And we should be done
+	 * with the preparation.
+	 */
+	InvalidateCatalogSnapshot();
+
+	/* Give up if there is still an active or registered snapshot. */
+	if (HaveRegisteredOrActiveSnapshot())
+		ereport(ERROR,
+				(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+				 errmsg("WAIT FOR must be only called without an active or registered snapshot"),
+				 errdetail("Make sure WAIT FOR isn't called within a transaction with an isolation level higher than READ COMMITTED, procedure, or a function.")));
+
+	/*
+	 * As the result we should hold no snapshot, and correspondingly our xmin
+	 * should be unset.
+	 */
+	Assert(MyProc->xmin == InvalidTransactionId);
+
+	waitLSNResult = WaitForLSNReplay(lsn, timeout);
+
+	/*
+	 * Process the result of WaitForLSNReplay().  Throw appropriate error if
+	 * needed.
+	 */
+	switch (waitLSNResult)
+	{
+		case WAIT_LSN_RESULT_SUCCESS:
+			/* Nothing to do on success */
+			result = "success";
+			break;
+
+		case WAIT_LSN_RESULT_TIMEOUT:
+			if (throw)
+				ereport(ERROR,
+						(errcode(ERRCODE_QUERY_CANCELED),
+						 errmsg("timed out while waiting for target LSN %X/%X to be replayed; current replay LSN %X/%X",
+								LSN_FORMAT_ARGS(lsn),
+								LSN_FORMAT_ARGS(GetXLogReplayRecPtr(NULL)))));
+			else
+				result = "timeout";
+			break;
+
+		case WAIT_LSN_RESULT_NOT_IN_RECOVERY:
+			if (throw)
+			{
+				if (PromoteIsTriggered())
+				{
+					ereport(ERROR,
+							(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+							 errmsg("recovery is not in progress"),
+							 errdetail("Recovery ended before replaying target LSN %X/%X; last replay LSN %X/%X.",
+									   LSN_FORMAT_ARGS(lsn),
+									   LSN_FORMAT_ARGS(GetXLogReplayRecPtr(NULL)))));
+				}
+				else
+				{
+					ereport(ERROR,
+							(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+							 errmsg("recovery is not in progress"),
+							 errhint("Waiting for the replay LSN can only be executed during recovery.")));
+				}
+			}
+			else
+				result = "not in recovery";
+			break;
+	}
+
+	/* need a tuple descriptor representing a single TEXT column */
+	tupdesc = WaitStmtResultDesc(stmt);
+
+	/* prepare for projection of tuples */
+	tstate = begin_tup_output_tupdesc(dest, tupdesc, &TTSOpsVirtual);
+
+	/* Send it */
+	do_text_output_oneline(tstate, result);
+
+	end_tup_output(tstate);
+}
+
+TupleDesc
+WaitStmtResultDesc(WaitStmt *stmt)
+{
+	TupleDesc	tupdesc;
+
+	/* Need a tuple descriptor representing a single TEXT  column */
+	tupdesc = CreateTemplateTupleDesc(1);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "RESULT STATUS",
+					   TEXTOID, -1, 0);
+	return tupdesc;
+}
diff --git a/src/backend/lib/pairingheap.c b/src/backend/lib/pairingheap.c
index 0aef8a88f1..fa8431f794 100644
--- a/src/backend/lib/pairingheap.c
+++ b/src/backend/lib/pairingheap.c
@@ -44,12 +44,26 @@ pairingheap_allocate(pairingheap_comparator compare, void *arg)
 	pairingheap *heap;
 
 	heap = (pairingheap *) palloc(sizeof(pairingheap));
+	pairingheap_initialize(heap, compare, arg);
+
+	return heap;
+}
+
+/*
+ * pairingheap_initialize
+ *
+ * Same as pairingheap_allocate(), but initializes the pairing heap in-place
+ * rather than allocating a new chunk of memory.  Useful to store the pairing
+ * heap in a shared memory.
+ */
+void
+pairingheap_initialize(pairingheap *heap, pairingheap_comparator compare,
+					   void *arg)
+{
 	heap->ph_compare = compare;
 	heap->ph_arg = arg;
 
 	heap->ph_root = NULL;
-
-	return heap;
 }
 
 /*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d7f9c00c40..67aa9554e2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -303,7 +303,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 		SecLabelStmt SelectStmt TransactionStmt TransactionStmtLegacy TruncateStmt
 		UnlistenStmt UpdateStmt VacuumStmt
 		VariableResetStmt VariableSetStmt VariableShowStmt
-		ViewStmt CheckPointStmt CreateConversionStmt
+		ViewStmt WaitStmt CheckPointStmt CreateConversionStmt
 		DeallocateStmt PrepareStmt ExecuteStmt
 		DropOwnedStmt ReassignOwnedStmt
 		AlterTSConfigurationStmt AlterTSDictionaryStmt
@@ -786,7 +786,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
 	VERBOSE VERSION_P VIEW VIEWS VOLATILE
 
-	WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
+	WAIT WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
 
 	XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLEXISTS XMLFOREST XMLNAMESPACES
 	XMLPARSE XMLPI XMLROOT XMLSERIALIZE XMLTABLE
@@ -1114,6 +1114,7 @@ stmt:
 			| VariableSetStmt
 			| VariableShowStmt
 			| ViewStmt
+			| WaitStmt
 			| /*EMPTY*/
 				{ $$ = NULL; }
 		;
@@ -16334,6 +16335,14 @@ xml_passing_mech:
 			| BY VALUE_P
 		;
 
+WaitStmt:
+			WAIT FOR generic_option_list
+				{
+					WaitStmt *n = makeNode(WaitStmt);
+					n->options = $3;
+					$$ = (Node *)n;
+				}
+			;
 
 /*
  * Aggregate decoration clauses
@@ -17991,6 +18000,7 @@ unreserved_keyword:
 			| VIEW
 			| VIEWS
 			| VOLATILE
+			| WAIT
 			| WHITESPACE_P
 			| WITHIN
 			| WITHOUT
@@ -18646,6 +18656,7 @@ bare_label_keyword:
 			| VIEW
 			| VIEWS
 			| VOLATILE
+			| WAIT
 			| WHEN
 			| WHITESPACE_P
 			| WORK
diff --git a/src/backend/storage/ipc/ipci.c b/src/backend/storage/ipc/ipci.c
index 174eed7036..27b447b7a7 100644
--- a/src/backend/storage/ipc/ipci.c
+++ b/src/backend/storage/ipc/ipci.c
@@ -24,6 +24,7 @@
 #include "access/twophase.h"
 #include "access/xlogprefetcher.h"
 #include "access/xlogrecovery.h"
+#include "access/xlogwait.h"
 #include "commands/async.h"
 #include "miscadmin.h"
 #include "pgstat.h"
@@ -148,6 +149,7 @@ CalculateShmemSize(int *num_semaphores)
 	size = add_size(size, WaitEventCustomShmemSize());
 	size = add_size(size, InjectionPointShmemSize());
 	size = add_size(size, SlotSyncShmemSize());
+	size = add_size(size, WaitLSNShmemSize());
 
 	/* include additional requested shmem from preload libraries */
 	size = add_size(size, total_addin_request);
@@ -340,6 +342,7 @@ CreateOrAttachShmemStructs(void)
 	StatsShmemInit();
 	WaitEventCustomShmemInit();
 	InjectionPointShmemInit();
+	WaitLSNShmemInit();
 }
 
 /*
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index 49204f91a2..dbb613663f 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -36,6 +36,7 @@
 #include "access/transam.h"
 #include "access/twophase.h"
 #include "access/xlogutils.h"
+#include "access/xlogwait.h"
 #include "miscadmin.h"
 #include "pgstat.h"
 #include "postmaster/autovacuum.h"
@@ -896,6 +897,11 @@ ProcKill(int code, Datum arg)
 	 */
 	LWLockReleaseAll();
 
+	/*
+	 * Cleanup waiting for LSN if any.
+	 */
+	WaitLSNCleanup();
+
 	/* Cancel any pending condition variable sleep, too */
 	ConditionVariableCancelSleep();
 
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 6f22496305..661296107c 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -1162,10 +1162,11 @@ PortalRunUtility(Portal portal, PlannedStmt *pstmt,
 	MemoryContextSwitchTo(portal->portalContext);
 
 	/*
-	 * Some utility commands (e.g., VACUUM) pop the ActiveSnapshot stack from
-	 * under us, so don't complain if it's now empty.  Otherwise, our snapshot
-	 * should be the top one; pop it.  Note that this could be a different
-	 * snapshot from the one we made above; see EnsurePortalSnapshotExists.
+	 * Some utility commands (e.g., VACUUM, WAIT FOR) pop the ActiveSnapshot
+	 * stack from under us, so don't complain if it's now empty.  Otherwise,
+	 * our snapshot should be the top one; pop it.  Note that this could be a
+	 * different snapshot from the one we made above; see
+	 * EnsurePortalSnapshotExists.
 	 */
 	if (portal->portalSnapshot != NULL && ActiveSnapshotSet())
 	{
@@ -1751,7 +1752,8 @@ PlannedStmtRequiresSnapshot(PlannedStmt *pstmt)
 		IsA(utilityStmt, ListenStmt) ||
 		IsA(utilityStmt, NotifyStmt) ||
 		IsA(utilityStmt, UnlistenStmt) ||
-		IsA(utilityStmt, CheckPointStmt))
+		IsA(utilityStmt, CheckPointStmt) ||
+		IsA(utilityStmt, WaitStmt))
 		return false;
 
 	return true;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 25fe3d5801..d23ac3b0f0 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -56,6 +56,7 @@
 #include "commands/user.h"
 #include "commands/vacuum.h"
 #include "commands/view.h"
+#include "commands/wait.h"
 #include "miscadmin.h"
 #include "parser/parse_utilcmd.h"
 #include "postmaster/bgwriter.h"
@@ -266,6 +267,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree)
 		case T_PrepareStmt:
 		case T_UnlistenStmt:
 		case T_VariableSetStmt:
+		case T_WaitStmt:
 			{
 				/*
 				 * These modify only backend-local state, so they're OK to run
@@ -1065,6 +1067,12 @@ standard_ProcessUtility(PlannedStmt *pstmt,
 				break;
 			}
 
+		case T_WaitStmt:
+			{
+				ExecWaitStmt((WaitStmt *) parsetree, dest);
+			}
+			break;
+
 		default:
 			/* All other statement types have event trigger support */
 			ProcessUtilitySlow(pstate, pstmt, queryString,
@@ -2067,6 +2075,9 @@ UtilityReturnsTuples(Node *parsetree)
 		case T_VariableShowStmt:
 			return true;
 
+		case T_WaitStmt:
+			return true;
+
 		default:
 			return false;
 	}
@@ -2122,6 +2133,9 @@ UtilityTupleDescriptor(Node *parsetree)
 				return GetPGVariableResultDesc(n->name);
 			}
 
+		case T_WaitStmt:
+			return WaitStmtResultDesc((WaitStmt *) parsetree);
+
 		default:
 			return NULL;
 	}
@@ -3099,6 +3113,10 @@ CreateCommandTag(Node *parsetree)
 			}
 			break;
 
+		case T_WaitStmt:
+			tag = CMDTAG_WAIT;
+			break;
+
 			/* already-planned queries */
 		case T_PlannedStmt:
 			{
@@ -3697,6 +3715,10 @@ GetCommandLogLevel(Node *parsetree)
 			lev = LOGSTMT_DDL;
 			break;
 
+		case T_WaitStmt:
+			lev = LOGSTMT_ALL;
+			break;
+
 			/* already-planned queries */
 		case T_PlannedStmt:
 			{
diff --git a/src/backend/utils/activity/wait_event_names.txt b/src/backend/utils/activity/wait_event_names.txt
index e199f07162..3b282043ec 100644
--- a/src/backend/utils/activity/wait_event_names.txt
+++ b/src/backend/utils/activity/wait_event_names.txt
@@ -88,6 +88,7 @@ LIBPQWALRECEIVER_CONNECT	"Waiting in WAL receiver to establish connection to rem
 LIBPQWALRECEIVER_RECEIVE	"Waiting in WAL receiver to receive data from remote server."
 SSL_OPEN_SERVER	"Waiting for SSL while attempting connection."
 WAIT_FOR_STANDBY_CONFIRMATION	"Waiting for WAL to be received and flushed by the physical standby."
+WAIT_FOR_WAL_REPLAY	"Waiting for a replay of the particular WAL position on the physical standby."
 WAL_SENDER_WAIT_FOR_WAL	"Waiting for WAL to be flushed in WAL sender process."
 WAL_SENDER_WRITE_DATA	"Waiting for any activity when processing replies from WAL receiver in WAL sender process."
 
@@ -346,6 +347,7 @@ WALSummarizer	"Waiting to read or update WAL summarization state."
 DSMRegistry	"Waiting to read or update the dynamic shared memory registry."
 InjectionPoint	"Waiting to read or update information related to injection points."
 SerialControl	"Waiting to read or update shared <filename>pg_serial</filename> state."
+WaitLSN	"Waiting to read or update shared Wait-for-LSN state."
 
 #
 # END OF PREDEFINED LWLOCKS (DO NOT CHANGE THIS LINE)
diff --git a/src/include/access/xlogwait.h b/src/include/access/xlogwait.h
new file mode 100644
index 0000000000..41234f6b96
--- /dev/null
+++ b/src/include/access/xlogwait.h
@@ -0,0 +1,89 @@
+/*-------------------------------------------------------------------------
+ *
+ * xlogwait.h
+ *	  Declarations for LSN replay waiting routines.
+ *
+ * Copyright (c) 2024, PostgreSQL Global Development Group
+ *
+ * src/include/access/xlogwait.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef XLOG_WAIT_H
+#define XLOG_WAIT_H
+
+#include "lib/pairingheap.h"
+#include "port/atomics.h"
+#include "postgres.h"
+#include "storage/procnumber.h"
+#include "storage/spin.h"
+#include "tcop/dest.h"
+
+/*
+ * WaitLSNProcInfo - the shared memory structure representing information
+ * about the single process, which may wait for LSN replay.  An item of
+ * waitLSN->procInfos array.
+ */
+typedef struct WaitLSNProcInfo
+{
+	/* LSN, which this process is waiting for */
+	XLogRecPtr	waitLSN;
+
+	/* Process to wake up once the waitLSN is replayed */
+	ProcNumber	procno;
+
+	/* A pairing heap node for participation in waitLSNState->waitersHeap */
+	pairingheap_node phNode;
+
+	/*
+	 * A flag indicating that this item is present in
+	 * waitLSNState->waitersHeap
+	 */
+	bool		inHeap;
+} WaitLSNProcInfo;
+
+/*
+ * WaitLSNState - the shared memory state for the replay LSN waiting facility.
+ */
+typedef struct WaitLSNState
+{
+	/*
+	 * The minimum LSN value some process is waiting for.  Used for the
+	 * fast-path checking if we need to wake up any waiters after replaying a
+	 * WAL record.  Could be read lock-less.  Update protected by WaitLSNLock.
+	 */
+	pg_atomic_uint64 minWaitedLSN;
+
+	/*
+	 * A pairing heap of waiting processes order by LSN values (least LSN is
+	 * on top).  Protected by WaitLSNLock.
+	 */
+	pairingheap waitersHeap;
+
+	/*
+	 * An array with per-process information, indexed by the process number.
+	 * Protected by WaitLSNLock.
+	 */
+	WaitLSNProcInfo procInfos[FLEXIBLE_ARRAY_MEMBER];
+} WaitLSNState;
+
+/*
+ * Result statuses for WaitForLSNReplay().
+ */
+typedef enum
+{
+	WAIT_LSN_RESULT_SUCCESS,	/* Target LSN is reached */
+	WAIT_LSN_RESULT_TIMEOUT,	/* Timeout occurred */
+	WAIT_LSN_RESULT_NOT_IN_RECOVERY,	/* Recovery ended before or during our
+										 * wait */
+} WaitLSNResult;
+
+extern PGDLLIMPORT WaitLSNState *waitLSNState;
+
+extern Size WaitLSNShmemSize(void);
+extern void WaitLSNShmemInit(void);
+extern void WaitLSNWakeup(XLogRecPtr currentLSN);
+extern void WaitLSNCleanup(void);
+extern WaitLSNResult WaitForLSNReplay(XLogRecPtr targetLSN, int64 timeout);
+
+#endif							/* XLOG_WAIT_H */
diff --git a/src/include/commands/wait.h b/src/include/commands/wait.h
new file mode 100644
index 0000000000..a7fa00ed41
--- /dev/null
+++ b/src/include/commands/wait.h
@@ -0,0 +1,21 @@
+/*-------------------------------------------------------------------------
+ *
+ * wait.h
+ *	  prototypes for commands/wait.c
+ *
+ * Portions Copyright (c) 2024, PostgreSQL Global Development Group
+ *
+ * src/include/commands/wait.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef WAIT_H
+#define WAIT_H
+
+#include "nodes/parsenodes.h"
+#include "tcop/dest.h"
+
+extern void ExecWaitStmt(WaitStmt *stmt, DestReceiver *dest);
+extern TupleDesc WaitStmtResultDesc(WaitStmt *stmt);
+
+#endif							/* WAIT_H */
diff --git a/src/include/lib/pairingheap.h b/src/include/lib/pairingheap.h
index 3c57d3fda1..567586f2ec 100644
--- a/src/include/lib/pairingheap.h
+++ b/src/include/lib/pairingheap.h
@@ -77,6 +77,9 @@ typedef struct pairingheap
 
 extern pairingheap *pairingheap_allocate(pairingheap_comparator compare,
 										 void *arg);
+extern void pairingheap_initialize(pairingheap *heap,
+								   pairingheap_comparator compare,
+								   void *arg);
 extern void pairingheap_free(pairingheap *heap);
 extern void pairingheap_add(pairingheap *heap, pairingheap_node *node);
 extern pairingheap_node *pairingheap_first(pairingheap *heap);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ffe155ee20..3dc1c1a56f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4305,4 +4305,11 @@ typedef struct DropSubscriptionStmt
 	DropBehavior behavior;		/* RESTRICT or CASCADE behavior */
 } DropSubscriptionStmt;
 
+typedef struct WaitStmt
+{
+	NodeTag		type;
+	List	   *options;
+} WaitStmt;
+
+
 #endif							/* PARSENODES_H */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index cf2917ad07..0d0d8f4ab4 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -492,6 +492,7 @@ PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("wait", WAIT, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("when", WHEN, RESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("where", WHERE, RESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("whitespace", WHITESPACE_P, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/storage/lwlocklist.h b/src/include/storage/lwlocklist.h
index cf56545238..a3f6607128 100644
--- a/src/include/storage/lwlocklist.h
+++ b/src/include/storage/lwlocklist.h
@@ -83,3 +83,4 @@ PG_LWLOCK(49, WALSummarizer)
 PG_LWLOCK(50, DSMRegistry)
 PG_LWLOCK(51, InjectionPoint)
 PG_LWLOCK(52, SerialControl)
+PG_LWLOCK(53, WaitLSN)
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index d250a714d5..c4606d6504 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -217,3 +217,4 @@ PG_CMDTAG(CMDTAG_TRUNCATE_TABLE, "TRUNCATE TABLE", false, false, false)
 PG_CMDTAG(CMDTAG_UNLISTEN, "UNLISTEN", false, false, false)
 PG_CMDTAG(CMDTAG_UPDATE, "UPDATE", false, false, true)
 PG_CMDTAG(CMDTAG_VACUUM, "VACUUM", false, false, false)
+PG_CMDTAG(CMDTAG_WAIT, "WAIT", false, false, false)
diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build
index 0428704dbf..c1328b1e16 100644
--- a/src/test/recovery/meson.build
+++ b/src/test/recovery/meson.build
@@ -52,6 +52,7 @@ tests += {
       't/041_checkpoint_at_promote.pl',
       't/042_low_level_backup.pl',
       't/043_no_contrecord_switch.pl',
+	  't/044_wait_for_lsn.pl',
     ],
   },
 }
diff --git a/src/test/recovery/t/044_wait_for_lsn.pl b/src/test/recovery/t/044_wait_for_lsn.pl
new file mode 100644
index 0000000000..79c2c49b9c
--- /dev/null
+++ b/src/test/recovery/t/044_wait_for_lsn.pl
@@ -0,0 +1,217 @@
+# Checks waiting for the lsn replay on standby using
+# WAIT FOR procedure.
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize primary node
+my $node_primary = PostgreSQL::Test::Cluster->new('primary');
+$node_primary->init(allows_streaming => 1);
+$node_primary->start;
+
+# And some content and take a backup
+$node_primary->safe_psql('postgres',
+	"CREATE TABLE wait_test AS SELECT generate_series(1,10) AS a");
+my $backup_name = 'my_backup';
+$node_primary->backup($backup_name);
+
+# Create a streaming standby with a 1 second delay from the backup
+my $node_standby = PostgreSQL::Test::Cluster->new('standby');
+my $delay = 1;
+$node_standby->init_from_backup($node_primary, $backup_name,
+	has_streaming => 1);
+$node_standby->append_conf(
+	'postgresql.conf', qq[
+	recovery_min_apply_delay = '${delay}s'
+]);
+$node_standby->start;
+
+# 1. Make sure that WAIT FOR works: add new content to
+# primary and memorize primary's insert LSN, then wait for that LSN to be
+# replayed on standby.
+$node_primary->safe_psql('postgres',
+	"INSERT INTO wait_test VALUES (generate_series(11, 20))");
+my $lsn1 =
+  $node_primary->safe_psql('postgres', "SELECT pg_current_wal_insert_lsn()");
+my $output = $node_standby->safe_psql(
+	'postgres', qq[
+	WAIT FOR LSN '${lsn1}', TIMEOUT '1000000';
+	SELECT pg_lsn_cmp(pg_last_wal_replay_lsn(), '${lsn1}'::pg_lsn);
+]);
+
+# Make sure the current LSN on standby is at least as big as the LSN we
+# observed on primary's before.
+ok((split("\n", $output))[-1] >= 0,
+	"standby reached the same LSN as primary after WAIT FOR");
+
+# 2. Check that new data is visible after calling WAIT FOR
+$node_primary->safe_psql('postgres',
+	"INSERT INTO wait_test VALUES (generate_series(21, 30))");
+my $lsn2 =
+  $node_primary->safe_psql('postgres', "SELECT pg_current_wal_insert_lsn()");
+$output = $node_standby->safe_psql(
+	'postgres', qq[
+	WAIT FOR LSN '${lsn2}';
+	SELECT count(*) FROM wait_test;
+]);
+
+# Make sure the count(*) on standby reflects the recent changes on primary
+ok((split("\n", $output))[-1] eq 30,
+	"standby reached the same LSN as primary");
+
+# 3. Check that waiting for unreachable LSN triggers the timeout.  The
+# unreachable LSN must be well in advance.  So WAL records issued by
+# the concurrent autovacuum could not affect that.
+my $lsn3 =
+  $node_primary->safe_psql('postgres',
+	"SELECT pg_current_wal_insert_lsn() + 10000000000");
+my $stderr;
+$node_standby->safe_psql('postgres', "WAIT FOR LSN '${lsn2}', TIMEOUT '10';");
+$node_standby->psql(
+	'postgres',
+	"WAIT FOR LSN '${lsn3}', TIMEOUT '1000';",
+	stderr => \$stderr);
+ok( $stderr =~ /timed out while waiting for target LSN/,
+	"get timeout on waiting for unreachable LSN");
+
+$output = $node_standby->safe_psql(
+	'postgres', qq[
+	WAIT FOR LSN '${lsn2}', TIMEOUT '10', THROW 'false';]);
+ok($output eq "success",
+	"WAIT FOR returns correct status after successful waiting");
+$output = $node_standby->safe_psql(
+	'postgres', qq[
+	WAIT FOR LSN '${lsn3}', TIMEOUT '10', THROW 'false';]);
+ok($output eq "timeout", "WAIT FOR returns correct status after timeout");
+
+# 4. Check that WAIT FOR triggers an error if called on primary,
+# within another function, or inside a transaction with an isolation level
+# higher than READ COMMITTED.
+
+$node_primary->psql('postgres', "WAIT FOR LSN '${lsn3}';",
+	stderr => \$stderr);
+ok( $stderr =~ /recovery is not in progress/,
+	"get an error when running on the primary");
+
+$node_standby->psql(
+	'postgres',
+	"BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT 1; WAIT FOR LSN '${lsn3}';",
+	stderr => \$stderr);
+ok( $stderr =~
+	  /WAIT FOR must be only called without an active or registered snapshot/,
+	"get an error when running in a transaction with an isolation level higher than REPEATABLE READ"
+);
+
+$node_primary->safe_psql(
+	'postgres', qq[
+CREATE FUNCTION pg_wal_replay_wait_wrap(target_lsn pg_lsn) RETURNS void AS \$\$
+  BEGIN
+    EXECUTE format('WAIT FOR LSN %L;', target_lsn);
+  END
+\$\$
+LANGUAGE plpgsql;
+]);
+
+$node_primary->wait_for_catchup($node_standby);
+$node_standby->psql(
+	'postgres',
+	"SELECT pg_wal_replay_wait_wrap('${lsn3}');",
+	stderr => \$stderr);
+ok( $stderr =~
+	  /WAIT FOR must be only called without an active or registered snapshot/,
+	"get an error when running within another function");
+
+# 5. Also, check the scenario of multiple LSN waiters.  We make 5 background
+# psql sessions each waiting for a corresponding insertion.  When waiting is
+# finished, stored procedures logs if there are visible as many rows as
+# should be.
+$node_primary->safe_psql(
+	'postgres', qq[
+CREATE FUNCTION log_count(i int) RETURNS void AS \$\$
+  DECLARE
+    count int;
+  BEGIN
+    SELECT count(*) FROM wait_test INTO count;
+    IF count >= 31 + i THEN
+      RAISE LOG 'count %', i;
+    END IF;
+  END
+\$\$
+LANGUAGE plpgsql;
+]);
+$node_standby->safe_psql('postgres', "SELECT pg_wal_replay_pause();");
+my @psql_sessions;
+for (my $i = 0; $i < 5; $i++)
+{
+	$node_primary->safe_psql('postgres',
+		"INSERT INTO wait_test VALUES (${i});");
+	my $lsn =
+	  $node_primary->safe_psql('postgres',
+		"SELECT pg_current_wal_insert_lsn()");
+	$psql_sessions[$i] = $node_standby->background_psql('postgres');
+	$psql_sessions[$i]->query_until(
+		qr/start/, qq[
+		\\echo start
+		WAIT FOR lsn '${lsn}';
+		SELECT log_count(${i});
+	]);
+}
+my $log_offset = -s $node_standby->logfile;
+$node_standby->safe_psql('postgres', "SELECT pg_wal_replay_resume();");
+for (my $i = 0; $i < 5; $i++)
+{
+	$node_standby->wait_for_log("count ${i}", $log_offset);
+	$psql_sessions[$i]->quit;
+}
+
+ok(1, 'multiple LSN waiters reported consistent data');
+
+# 6. Check that the standby promotion terminates the wait on LSN.  Start
+# waiting for an unreachable LSN then promote.  Check the log for the relevant
+# error message.  Also, check that waiting for already replayed LSN doesn't
+# cause an error even after promotion.
+my $lsn4 =
+  $node_primary->safe_psql('postgres',
+	"SELECT pg_current_wal_insert_lsn() + 10000000000");
+my $lsn5 =
+  $node_primary->safe_psql('postgres', "SELECT pg_current_wal_insert_lsn()");
+my $psql_session = $node_standby->background_psql('postgres');
+$psql_session->query_until(
+	qr/start/, qq[
+	\\echo start
+	WAIT FOR LSN '${lsn4}';
+]);
+
+# Make sure standby will be promoted at least at the primary insert LSN we
+# have just observed.  Use pg_switch_wal() to force the insert LSN to be
+# written then wait for standby to catchup.
+$node_primary->safe_psql('postgres', 'SELECT pg_switch_wal();');
+$node_primary->wait_for_catchup($node_standby);
+
+$log_offset = -s $node_standby->logfile;
+$node_standby->promote;
+$node_standby->wait_for_log('recovery is not in progress', $log_offset);
+
+ok(1, 'got error after standby promote');
+
+$node_standby->safe_psql('postgres', "WAIT FOR LSN '${lsn5}';");
+
+ok(1, 'wait for already replayed LSN exits immediately even after promotion');
+
+$output = $node_standby->safe_psql(
+	'postgres', qq[
+	WAIT FOR LSN '${lsn4}', TIMEOUT '10', THROW 'false';]);
+ok($output eq "not in recovery",
+	"WAIT FOR returns correct status after standby promotion");
+
+$node_standby->stop;
+$node_primary->stop;
+
+# If we send \q with $psql_session->quit the command can be sent to the session
+# already closed. So \q is in initial script, here we only finish IPC::Run.
+$psql_session->{run}->finish;
+
+done_testing();
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 9a3bee93de..1e0be9f4f6 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3149,7 +3149,11 @@ WaitEventIO
 WaitEventIPC
 WaitEventSet
 WaitEventTimeout
+WaitLSNProcInfo
+WaitLSNResult
+WaitLSNState
 WaitPMResult
+WaitStmt
 WalCloseMethod
 WalCompression
 WalInsertClass
-- 
2.39.5

---

From: Yura Sokolov <[email-masked]>
Date: Thu, 6 Feb 2025 11:31:28 +0300

> inaccurate error messages has been previously spotted in [5].

Good day, Alexander.

I briefly looked into patch and have couple of minor remarks:

1. I don't like `palloc` in the `WaitLSNWakeup`. I believe it wont issue
problems, but still don't like it. I'd prefer to see local fixed array, say
of 16 elements, and loop around remaining function body acting in batch of
16 wakeups. Doubtfully there will be more than 16 waiting clients often,
and even then it wont be much heavier than fetching all at once.

2. I'd move `inHeap` field between `procno` and `phNode` to fill the gap
between fields on 64bit platforms.
Well, I believe, it would be better to tweak `pairingheap_node` to make it
clear if it is in heap or not. But such change would be unrelated to
current patch's sense. So lets stick with `inHeap`, but move it a bit.

Non-code question: do you imagine for `WAIT` command reuse for other cases?
Is syntax rule in gram.y convenient enough for such reuse? I believe, `LSN`
is not part of syntax to not introduce new keyword. But is it correct way?
I have no answer or strong opinion.

Otherwise, the patch looks quite strong to me.

-------
regards
Yura Sokolov

---

From: Alexander Korotkov <[email-masked]>
Date: Sun, 16 Feb 2025 23:27:43 +0200

Hi, Yura!

> and even then it wont be much heavier than fetching all at once.

OK, I've refactored this to use static array of 16 size.  palloc() is
used only if we don't fit static array.

> current patch's sense. So lets stick with `inHeap`, but move it a bit.

Ok, `inHeap` is moved.

> I have no answer or strong opinion.

This is conscious decision.  New rules and new keywords causes extra
states for parser state machine.  There could be raised a question
whether feature is valuable enough to justify the slowdown of parser.
This is why I tried to make this feature as less invasive as possible
in terms of parser.  And yes, there potentially could be other things
to wait.  For instance, instead of waiting for lsn replay we could be
waiting for finishing replay of given xid.

> Otherwise, the patch looks quite strong to me.

Great, thank you!

------
Regards,
Alexander Korotkov
Supabase

---

From: Yura Sokolov <[email-masked]>
Date: Fri, 28 Feb 2025 16:03:33 +0300

> used only if we don't fit static array.

I've rebased patch and:
- fixed compiler warning in wait.c ("maybe uninitialized 'result'").
- made a loop without call to palloc in WaitLSNWakeup. It is with "goto" to
keep indentation, perhaps `do {} while` would be better?

-------
regards
Yura Sokolov aka funny-falcon

From fa107e15eab3ec2493f0663f03b563d49979e0b5 Mon Sep 17 00:00:00 2001
From: Yura Sokolov <[email-masked]>
Date: Fri, 28 Feb 2025 15:40:18 +0300
Subject: [PATCH v3] Implement WAIT FOR command

 src/backend/access/transam/xlogwait.c         | 347 ++++++++++++++++++
 src/backend/parser/gram.y                     |  14 +-
 src/test/recovery/t/045_wait_for_lsn.pl       | 217 +++++++++++
 28 files changed, 977 insertions(+), 11 deletions(-)
 create mode 100644 src/test/recovery/t/045_wait_for_lsn.pl

index f5be638867a..8b585cba751 100644
 
index ff85ace83fc..bd14ec00d2d 100644
 
  </reference>
 
index 661c55a9db7..a32f473e0a2 100644

index e8ae9b13c8e..74a62ab3eab 100644
 
index 1b4f21a88d3..e617ae8ead5 100644
 
+	/*
index 799fc739e18..b9abb696a5e 100644
@@ -6219,6 +6220,12 @@ StartupXLOG(void)
 
+	/*
index 52f53fa12e0..b03a39b510d 100644
@@ -1831,6 +1832,16 @@ PerformWalRecovery(void)
 				break;
 			}
 
+			/*
index 00000000000..a0f0e480a48
@@ -0,0 +1,347 @@
+ * Size of a static array of procs to wakeup by WaitLSNWakeup() allocated
+ * on the stack.  It should be enough to take single iteration for most cases.
+#define	WAKEUP_PROC_STATIC_ARRAY_SIZE (16)
+	ProcNumber	wakeUpProcs[WAKEUP_PROC_STATIC_ARRAY_SIZE];
+	int			numWakeUpProcs;
+resume:
+	numWakeUpProcs = 0;
+		Assert(numWakeUpProcs < MaxBackends);
+		if (numWakeUpProcs == WAKEUP_PROC_STATIC_ARRAY_SIZE)
+	/* Need to recheck if there were more waiters than static array size. */
+	if (numWakeUpProcs == WAKEUP_PROC_STATIC_ARRAY_SIZE)
+		goto resume;
index 85cfea6fd71..12459111f7c 100644
@@ -63,6 +63,7 @@ OBJS = \
 	vacuum.o \
 	vacuumparallel.o \
 	variable.o \
-	view.o
+	view.o \
+	wait.o
 
index ce8d1ab8bac..b1c60f60ea7 100644
@@ -52,4 +52,5 @@ backend_sources += files(
index 00000000000..a5f44de1303
+	const char *result = "<unset>";
index 0aef8a88f1b..fa8431f7946 100644

 	heap->ph_root = NULL;
-
-	return heap;
 }
 
 /*
index 7d99c9355c6..11265ae3383 100644
 	VERBOSE VERSION_P VIEW VIEWS VIRTUAL VOLATILE

 	XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLEXISTS XMLFOREST XMLNAMESPACES
 	XMLPARSE XMLPI XMLROOT XMLSERIALIZE XMLTABLE
@@ -1114,6 +1114,7 @@ stmt:
 			| VariableSetStmt
 			| VariableShowStmt
 			| ViewStmt
+			| WaitStmt
 			| /*EMPTY*/
 				{ $$ = NULL; }
 		;
@@ -16341,6 +16342,14 @@ xml_passing_mech:
 			| BY VALUE_P
 		;
 
+WaitStmt:
 
 /*
@@ -17999,6 +18008,7 @@ unreserved_keyword:
 			| VIRTUAL
index 174eed70367..27b447b7a7a 100644

 /*
index 49204f91a20..dbb613663fa 100644
 
+	/*
 
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index dea24453a6c..61cf02c9527 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -1194,10 +1194,11 @@ PortalRunUtility(Portal portal, PlannedStmt *pstmt,
 	MemoryContextSwitchTo(portal->portalContext);
 
 	/*
@@ -1792,7 +1793,8 @@ PlannedStmtRequiresSnapshot(PlannedStmt *pstmt)
 
 	return true;
index 25fe3d58016..d23ac3b0f0b 100644
 
+		case T_WaitStmt:
+			{
 
+		case T_WaitStmt:
+			return true;
+
 		default:
 			return false;
 	}
 
+		case T_WaitStmt:
 
+		case T_WaitStmt:
+			tag = CMDTAG_WAIT;
+			break;
+
 
+		case T_WaitStmt:
+			lev = LOGSTMT_ALL;
+			break;
+
index e199f071628..3b282043eca 100644

 #
index 00000000000..0acc61eba5f
index 00000000000..a7fa00ed41e
index 3c57d3fda1b..567586f2ecf 100644
 
index 0b208f51bdd..1c3baac08a9 100644
@@ -4317,4 +4317,11 @@ typedef struct DropSubscriptionStmt
 
+typedef struct WaitStmt
+{
+	NodeTag		type;
+	List	   *options;
+} WaitStmt;
+
+
index 40cf090ce61..6d834f25d2d 100644
@@ -493,6 +493,7 @@ PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("virtual", VIRTUAL, UNRESERVED_KEYWORD, BARE_LABEL)
index cf565452382..a3f66071288 100644
index d250a714d59..c4606d65043 100644
index 057bcde1434..8a8f1f6c427 100644
@@ -53,6 +53,7 @@ tests += {
       't/044_invalidate_inactive_slots.pl',
+      't/045_wait_for_lsn.pl',
diff --git a/src/test/recovery/t/045_wait_for_lsn.pl b/src/test/recovery/t/045_wait_for_lsn.pl
index 00000000000..79c2c49b9ce
+++ b/src/test/recovery/t/045_wait_for_lsn.pl
index fcb968e1ffe..7b6c30c8d4f 100644
@@ -3169,7 +3169,11 @@ WaitEventIO
 WaitEventIPC
 WaitEventSet
 WaitEventTimeout
+WaitLSNProcInfo
+WaitLSNResult
+WaitLSNState
 WaitPMResult
+WaitStmt
 WalCloseMethod
 WalCompression
 WalInsertClass
-- 
2.43.0

---

From: Yura Sokolov <[email-masked]>
Date: Fri, 28 Feb 2025 16:55:21 +0300

> keep indentation, perhaps `do {} while` would be better?

And fixed:
   'WAIT' is marked as BARE_LABEL in kwlist.h, but it is missing from
gram.y's bare_label_keyword rule

-------
regards

From d9c44427a4cbecd6dd27edae48ea42d933756ff9 Mon Sep 17 00:00:00 2001
From: Yura Sokolov <[email-masked]>
Date: Fri, 28 Feb 2025 15:40:18 +0300
Subject: [PATCH v4] Implement WAIT FOR command

 28 files changed, 978 insertions(+), 11 deletions(-)

 
  </reference>

 

+	/*
 
+	/*
 
+			/*

 
 	heap->ph_root = NULL;
-
-	return heap;
 }
 
 /*
index 7d99c9355c6..3034573648f 100644

 
+WaitStmt:
 
 /*
@@ -18655,6 +18665,7 @@ bare_label_keyword:

 /*
 
+	/*

 	/*
 
 	return true;
 
+		case T_WaitStmt:
+			{
 
+		case T_WaitStmt:
+			return true;
+
 		default:
 			return false;
 	}
 
+		case T_WaitStmt:
 
+		case T_WaitStmt:
+			tag = CMDTAG_WAIT;
+			break;
+
 
+		case T_WaitStmt:
+			lev = LOGSTMT_ALL;
+			break;
+

 #

+typedef struct WaitStmt
+{
+	NodeTag		type;
+	List	   *options;
+} WaitStmt;
+
+

---

From: Alexander Korotkov <[email-masked]>
Date: Mon, 10 Mar 2025 13:30:31 +0200

> gram.y's bare_label_keyword rule

Thank you, Yura.  I've further revised the patch.  Mostly added the
documentation including SQL command reference and few paragraphs in
the high availability chapter explaining the read-your-writes
consistency concept.

------
Regards,
Alexander Korotkov
Supabase

---

From: Yura Sokolov <[email-masked]>
Date: Wed, 12 Mar 2025 17:44:28 +0300

Good day, Alexander.

Looking "for the last time" to the patch I found there remains
`pg_wal_replay_wait` function in documentation and one comment.
So I fixed it in documentation, and removed sentence from comment.

Otherwise v6 is just rebased v5.

-------
regards

From 80b4cb8c0ac75168ab1fce55feccc4f08f32ce34 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email-masked]>
Date: Mon, 10 Mar 2025 12:59:38 +0200
Subject: [PATCH v6] Implement WAIT FOR command

Discussion: https://postgr.es/m/eb12f9b03851bb2583adab5df9579b4b%40postgrespro.ru
Author: Kartyshov Ivan <[email-masked]>
Author: Alexander Korotkov <[email-masked]>
Reviewed-by: Michael Paquier <[email-masked]>
Reviewed-by: Peter Eisentraut <[email-masked]>
Reviewed-by: Dilip Kumar <[email-masked]>
Reviewed-by: Amit Kapila <[email-masked]>
Reviewed-by: Alexander Lakhin <[email-masked]>
Reviewed-by: Bharath Rupireddy <[email-masked]>
Reviewed-by: Euler Taveira <[email-masked]>
Reviewed-by: Heikki Linnakangas <[email-masked]>
Reviewed-by: Kyotaro Horiguchi <[email-masked]>
Reviewed-by: Yura Sokolov <[email-masked]>
---
 doc/src/sgml/high-availability.sgml           |  54 +++
 doc/src/sgml/ref/allfiles.sgml                |   1 +
 doc/src/sgml/ref/wait_for.sgml                | 216 +++++++++++
 doc/src/sgml/reference.sgml                   |   1 +
 src/backend/access/transam/Makefile           |   3 +-
 src/backend/access/transam/meson.build        |   1 +
 src/backend/access/transam/xact.c             |   6 +
 src/backend/access/transam/xlog.c             |   7 +
 src/backend/access/transam/xlogrecovery.c     |  11 +
 src/backend/access/transam/xlogwait.c         | 347 ++++++++++++++++++
 src/backend/commands/Makefile                 |   3 +-
 src/backend/commands/meson.build              |   1 +
 src/backend/commands/wait.c                   | 184 ++++++++++
 src/backend/lib/pairingheap.c                 |  18 +-
 src/backend/parser/gram.y                     |  15 +-
 src/backend/storage/ipc/ipci.c                |   3 +
 src/backend/storage/lmgr/proc.c               |   6 +
 src/backend/tcop/pquery.c                     |  12 +-
 src/backend/tcop/utility.c                    |  22 ++
 .../utils/activity/wait_event_names.txt       |   2 +
 src/include/access/xlogwait.h                 |  89 +++++
 src/include/commands/wait.h                   |  21 ++
 src/include/lib/pairingheap.h                 |   3 +
 src/include/nodes/parsenodes.h                |   7 +
 src/include/parser/kwlist.h                   |   1 +
 src/include/storage/lwlocklist.h              |   1 +
 src/include/tcop/cmdtaglist.h                 |   1 +
 src/test/recovery/meson.build                 |   1 +
 src/test/recovery/t/045_wait_for_lsn.pl       | 217 +++++++++++
 src/tools/pgindent/typedefs.list              |   4 +
 30 files changed, 1247 insertions(+), 11 deletions(-)
 create mode 100644 doc/src/sgml/ref/wait_for.sgml
 create mode 100644 src/backend/access/transam/xlogwait.c
 create mode 100644 src/backend/commands/wait.c
 create mode 100644 src/include/access/xlogwait.h
 create mode 100644 src/include/commands/wait.h
 create mode 100644 src/test/recovery/t/045_wait_for_lsn.pl

diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index acf3ac0601d..ae316b5a0c9 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -1376,6 +1376,60 @@ synchronous_standby_names = 'ANY 2 (s1, s2, s3)'
    </sect3>
   </sect2>
 
+  <sect2 id="read-your-writes-consistency">
+   <title>Read-Your-Writes Consistency</title>
+
+   <para>
+    In asynchronous replication, there is always a short window where changes
+    on the primary may not yet be visible on the standby due to replication
+    lag. This can lead to inconsistencies when an application writes data on
+    the primary and then immediately issues a read query on the standby.
+    However, it possible to address this without switching to the synchronous
+    replication
+   </para>
+
+   <para>
+    To address this, PostgreSQL offers a mechanism for read-your-writes
+    consistency. The key idea is to ensure that a client sees its own writes
+    by synchronizing the WAL replay on the standby with the known point of
+    change on the primary.
+   </para>
+
+   <para>
+    This is achieved by the following steps.  After performing write
+    operations, the application retrieves the current WAL location using a
+    function call like this.
+
+    <programlisting>
+postgres=# SELECT pg_current_wal_insert_lsn();
+pg_current_wal_insert_lsn
+--------------------
+0/306EE20
+(1 row)
+    </programlisting>
+   </para>
+
+   <para>
+    The <acronym>LSN</acronym> obtained from the primary is then communicated
+    to the standby server. This can be managed at the application level or
+    via the connection pooler.  On the standby, the application issues the
+    <xref linkend="sql-wait-for"/> command to block further processing until
+    the standby's WAL replay process reaches (or exceeds) the specified
+    <acronym>LSN</acronym>.
+
+    <programlisting>
+postgres=# WAIT FOR LSN '0/306EE20';
+ RESULT STATUS
+---------------
+ success
+(1 row)
+    </programlisting>
+    Once the command returns a status of success, it guarantees that all
+    changes up to the provided <acronym>LSN</acronym> have been applied,
+    ensuring that subsequent read queries will reflect the latest updates.
+   </para>
+  </sect2>
+
   <sect2 id="continuous-archiving-in-standby">
    <title>Continuous Archiving in Standby</title>
 
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index f5be638867a..e167406c744 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -188,6 +188,7 @@ Complete list of usable sgml source files in this directory.
 <!ENTITY update             SYSTEM "update.sgml">
 <!ENTITY vacuum             SYSTEM "vacuum.sgml">
 <!ENTITY values             SYSTEM "values.sgml">
+<!ENTITY waitFor            SYSTEM "wait_for.sgml">
 
 <!-- applications and utilities -->
 <!ENTITY clusterdb          SYSTEM "clusterdb.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..2352ae9493f
--- /dev/null
+++ b/doc/src/sgml/ref/wait_for.sgml
@@ -0,0 +1,216 @@
+<!--
+doc/src/sgml/ref/wait_for.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-wait-for">
+ <indexterm zone="sql-wait-for">
+  <primary>AFTER</primary>
+ </indexterm>
+
+ <refmeta>
+  <refentrytitle>WAIT FOR</refentrytitle>
+  <manvolnum>7</manvolnum>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>WAIT FOR</refname>
+  <refpurpose>wait target <acronym>LSN</acronym> to be replayed within the specified timeout</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+WAIT FOR ( <replaceable class="parameter">parameter</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+    Waits until recovery replays <parameter>lsn</parameter>.
+    If no <parameter>timeout</parameter> is specified or it is set to
+    zero, this command waits indefinitely for the
+    <parameter>lsn</parameter>.
+    On timeout, or if the server is promoted before
+    <parameter>lsn</parameter> is reached, an error is emitted,
+    as soon as <parameter>throw</parameter> is not specified or set to true.
+    If <parameter>throw</parameter> is set to false, then the command
+    doesn't throw errors.
+  </para>
+
+  <para>
+    The possible return values are <literal>success</literal>,
+    <literal>timeout</literal>, and <literal>not in recovery</literal>.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Parameters</title>
+
+  <variablelist>
+   <varlistentry>
+    <term><replaceable class="parameter">lsn</replaceable></term>
+    <listitem>
+     <para>
+      The target log sequence number to wait for.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">timeout</replaceable></term>
+    <listitem>
+     <para>
+      When specified and greater than zero, the command waits until
+      <parameter>lsn</parameter> is reached or the specified
+      <parameter>timeout</parameter> has elapsed.  Must be a non-negative
+      integer, the default is zero.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">throw</replaceable></term>
+    <listitem>
+     <para>
+      Specify whether to throw an error in the case of timeout or
+      running on the primary.  The valid values are <literal>true</literal>
+      and <literal>false</literal>.  The default is <literal>true</literal>.
+      When set to <literal>false</literal> the status can be get from the
+      return `value.
+     </para>
+    </listitem>
+   </varlistentry>
+  </variablelist>
+ </refsect1>
+
+ <refsect1>
+  <title>Return values</title>
+
+  <variablelist>
+   <varlistentry>
+    <term><replaceable class="parameter">success</replaceable></term>
+    <listitem>
+     <para>
+      This return value denotes that we have successfully reached
+      the target <parameter>lsn</parameter>.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">timeout</replaceable></term>
+    <listitem>
+     <para>
+      This return value denotes that the timeout happened before reaching
+      the target <parameter>lsn</parameter>.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">not in recovery</replaceable></term>
+    <listitem>
+     <para>
+      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 <parameter>lsn</parameter>.
+     </para>
+    </listitem>
+   </varlistentry>
+  </variablelist>
+ </refsect1>
+
+ <refsect1>
+  <title>Notes</title>
+
+  <para>
+    <command>WAIT FOR</command> command waits till
+    <parameter>lsn</parameter> to be replayed on standby.
+    That is, after this function execution, the value returned by
+    <function>pg_last_wal_replay_lsn</function> should be greater or equal
+    to the <parameter>lsn</parameter> value.  This is useful to achieve
+    read-your-writes-consistency, while using async replica for reads and
+    primary for writes.  In that case, the <acronym>lsn</acronym> of the last
+    modification should be stored on the client application side or the
+    connection pooler side.
+  </para>
+
+  <para>
+    <command>WAIT FOR</command> command should be called on standby.
+    If a user runs <command>WAIT FOR</command> on primary, it
+    will error out as soon as <parameter>throw</parameter> is true.
+    However, if <command>WAIT FOR</command> is
+    called on primary promoted from standby and <literal>lsn</literal>
+    was already replayed, then the <command>WAIT FOR</command> command just
+    exits immediately.
+  </para>
+
+</refsect1>
+
+ <refsect1>
+  <title>Examples</title>
+
+  <para>
+    You can use <command>WAIT FOR</command> command to wait for
+    the <type>pg_lsn</type> value.  For example, an application could update
+    the <literal>movie</literal> table and get the <acronym>lsn</acronym> after
+    changes just made.  This example uses <function>pg_current_wal_insert_lsn</function>
+    on primary server to get the <acronym>lsn</acronym> given that
+    <varname>synchronous_commit</varname> could be set to
+    <literal>off</literal>.
+
+   <programlisting>
+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)
+   </programlisting>
+
+   Then an application could run <command>WAIT FOR</command>
+   with the <parameter>lsn</parameter> obtained from primary.  After that the
+   changes made on primary should be guaranteed to be visible on replica.
+
+   <programlisting>
+postgres=# WAIT FOR LSN '0/306EE20';
+ RESULT STATUS
+---------------
+ success
+(1 row)
+postgres=# SELECT * FROM movie WHERE genre = 'Drama';
+ genre
+-------
+(0 rows)
+   </programlisting>
+  </para>
+
+  <para>
+    It may also happen that target <parameter>lsn</parameter> is not reached
+    within the timeout.  In that case the error is thrown.
+
+   <programlisting>
+postgres=# WAIT FOR LSN '0/306EE20', TIMEOUT '100';
+ERROR:  timed out while waiting for target LSN 0/306EE20 to be replayed; current replay LSN 0/306EA60
+   </programlisting>
+  </para>
+
+  <para>
+   The same example uses <command>WAIT FOR</command> with
+   <parameter>throw</parameter> set to false.
+
+   <programlisting>
+postgres=# WAIT FOR LSN '0/306EE20', TIMEOUT '100', THROW 'false';
+ RESULT STATUS
+---------------
+ timeout
+(1 row)
+   </programlisting>
+  </para>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index ff85ace83fc..2cf02c37b17 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -216,6 +216,7 @@
    &update;
    &vacuum;
    &values;
+   &waitFor;
 
  </reference>

 

+	/*
 
+	/*
index a829a055a97..1beb3999769 100644
 
+			/*
 
index 00000000000..d95782ddaf8
@@ -0,0 +1,184 @@
+	 * implying a kind of self-deadlock.

 	heap->ph_root = NULL;
-
-	return heap;
 }
 
 /*
index 271ae26cbaf..e4916148d02 100644

 	XML_P XMLATTRIBUTES XMLCONCAT XMLELEMENT XMLEXISTS XMLFOREST XMLNAMESPACES
 	XMLPARSE XMLPI XMLROOT XMLSERIALIZE XMLTABLE
@@ -1114,6 +1114,7 @@ stmt:
 			| VariableSetStmt
 			| VariableShowStmt
 			| ViewStmt
+			| WaitStmt
 			| /*EMPTY*/
 				{ $$ = NULL; }
 		;
@@ -16369,6 +16370,14 @@ xml_passing_mech:
 			| BY VALUE_P
 		;
 
+WaitStmt:
 
 /*
@@ -18027,6 +18036,7 @@ unreserved_keyword:
@@ -18683,6 +18693,7 @@ bare_label_keyword:

 /*
index 749a79d48ef..1a99e98f55b 100644
 
+	/*

 	/*
 
 	return true;
 
+		case T_WaitStmt:
+			{
 
+		case T_WaitStmt:
+			return true;
+
 		default:
 			return false;
 	}
 
+		case T_WaitStmt:
 
+		case T_WaitStmt:
+			tag = CMDTAG_WAIT;
+			break;
+
 
+		case T_WaitStmt:
+			lev = LOGSTMT_ALL;
+			break;
+
index 3c594415bfd..5849967882e 100644

 #
 
index 23c9e3c5abf..dffa714e2c8 100644
@@ -4319,4 +4319,11 @@ typedef struct DropSubscriptionStmt
 
+typedef struct WaitStmt
+{
+	NodeTag		type;
+	List	   *options;
+} WaitStmt;
+
+
index dfe2690bdd3..5377d6208e1 100644
@@ -3177,7 +3177,11 @@ WaitEventIO
 WaitEventIPC
 WaitEventSet
 WaitEventTimeout
+WaitLSNProcInfo
+WaitLSNResult
+WaitLSNState
 WaitPMResult
+WaitStmt
 WalCloseMethod
 WalCompression
 WalInsertClass
-- 
2.43.0

---

From: Tomas Vondra <[email-masked]>
Date: Thu, 13 Mar 2025 15:15:01 +0100

Hi,

I did a quick look at this patch. I haven't found any correctness
issues, but I have some general review comments and questions about the
grammar / syntax.

1) The sgml docs don't really show the syntax very nicely, it only shows
this at the beginning of wait_for.sgml:

   WAIT FOR ( <replaceable class="parameter">parameter</replaceable>
'<replaceable class="parameter">value</replaceable>' [, ... ] ) ]

I kinda understand this comes from using the generic option list (I'll
get to that shortly), but I think it'd be much better to actually show
the "full" syntax here, instead of leaving the "parameters" to later.

2) The syntax description suggests "(" and ")" are required, but that
does not seem to be the case - in fact, it's not even optional, and when
I try using that, I get syntax error.

3) I have my doubts about using the generic_option_list for this. Yes, I
understand this allows using fewer reserved keywords, but it leads to
some weirdness and I'm not sure it's worth it. Not sure what the right
trade off is here.

Anyway, some examples of the weird stuff implied by this approach:

- it forces "," between the options, which is a clear difference from
what we do for every other command

- it forces everything to be a string, i.e. you can' say "TIMEOUT 10",
it has to be "TIMEOUT '10'"

I don't have a very strong opinion on this, but the result seems a bit
strange to me.

4) I'm not sure I understand the motivation of the "throw false" mode,
and I'm not sure I understand this description in the sgml docs:

    On timeout, or if the server is promoted before
    <parameter>lsn</parameter> is reached, an error is emitted,
    as soon as <parameter>throw</parameter> is not specified or set to
    true.
    If <parameter>throw</parameter> is set to false, then the command
    doesn't throw errors.

I find it a bit confusing. What is the use case for this mode?

5) One place in the docs says:

      The target log sequence number to wait for.

   Thie is literally the only place using "log sequence number" in our
   code base, I'd just use "LSN" just like every other place.

6) The docs for the TIMEOUT parameter say this:

   <varlistentry>
    <term><replaceable class="parameter">timeout</replaceable></term>
    <listitem>
     <para>
      When specified and greater than zero, the command waits until
      <parameter>lsn</parameter> is reached or the specified
      <parameter>timeout</parameter> has elapsed.  Must be a non-
      negative integer, the default is zero.
     </para>
    </listitem>
   </varlistentry>

   That doesn't say what unit does the option use. Is is seconds,
   milliseconds or what?

   In fact, it'd be nice to let users specify that in the value, similar
   to other options (e.g. SET statement_timeout = '10s').

7) One place in the docs says this:

    That is, after this function execution, the value returned by
    <function>pg_last_wal_replay_lsn</function> should be greater ...

  I think the reference to "function execution" is obsolete?

8) I find this confusing:

    However, if <command>WAIT FOR</command> is
    called on primary promoted from standby and <literal>lsn</literal>
    was already replayed, then the <command>WAIT FOR</command> command
    just exits immediately.

  Does this mean running the WAIT command on a primary (after it was
  already promoted) will exit immediately? Why does it matter that it
  was promoted from a standby? Shouldn't it exit immediately even for
  a standalone instance?

9) xlogwait.c

I think this should start with a basic "design" description of how the
wait is implemented, in a comment at the top of the file. That is, what
we keep in the shared memory, what happens during a wait, how it uses
the pairing heap, etc. After reading this comment I should understand
how it all fits together.

10) WaitForLSNReplay / WaitLSNWakeup

I think the function comment should document the important stuff (e.g.
return values for various situations, how it groups waiters into chunks
of 16 elements during wakeup, ...).

11) WaitLSNProcInfo / WaitLSNState

Does this need to be exposed in xlogwait.h? These structs seem private
to xlogwait.c, so maybe declare it there?

regards

-- 
Tomas Vondra

---

From: vignesh C <[email-masked]>
Date: Sun, 16 Mar 2025 19:02:11 +0530

> Otherwise v6 is just rebased v5.

I noticed that Tomas's comments from [1] are not yet addressed, I have
changed the commitfest status to Waiting on Author, please address
them and update it to Needs review.
[1] - https://www.postgresql.org/message-id/[email-masked]

Regards,
Vignesh

---

From: Alexander Korotkov <[email-masked]>
Date: Tue, 29 Apr 2025 14:27:25 +0300

Hi, Tomas.

Thank you so much for your review!  Please find the revised patchset.

> the "full" syntax here, instead of leaving the "parameters" to later.

Sounds reasonable, changed to show the full syntax in the synopsis.

> I try using that, I get syntax error.

Good catch, fixed.

> I don't have a very strong opinion on this, but the result seems a bit

I've improved the syntax.  I still tried to keep the number of new
keywords and grammar rules minimal.  That leads to moving some parser
login into wait.c.  This is probably a bit awkward, but saves our
grammar from bloat.  Let me know what do you think about this
approach.

> I find it a bit confusing. What is the use case for this mode?

The idea here is that application could do some handling of these
errors without having to parse the error messages (parsing error
messages is inconvenient because of localization etc).

>    code base, I'd just use "LSN" just like every other place.

OK fixed.

>    to other options (e.g. SET statement_timeout = '10s').

The default unit of milliseconds is specified.  Also, an alternative
way to specify timeout is now supported.  Timeout might be a string
literal consisting of numeric and unit specifier.

>   I think the reference to "function execution" is obsolete?

Actually, this is just the function, which reports current replay LSN,
not function introduced by previous version of this patch.  We refer
it to just express the constraint that LSN must be replayed after
execution of the command.

>   was promoted from a standby? Shouldn't it exit immediately even for

I think the previous sentence should give an idea that otherwise error
gets thrown.  That also happens immediately for sure.

> how it all fits together.

OK, I've added the header comment.

> of 16 elements during wakeup, ...).

Revised header comments for those functions too.

> to xlogwait.c, so maybe declare it there?

Hmm, I don't remember why I moved them to xlogwait.h.  OK, moved them
back to xlogwait.c.

------
Regards,
Alexander Korotkov
Supabase

---

From: Álvaro Herrera <[email-masked]>
Date: Tue, 5 Aug 2025 15:47:07 +0200

> Hmm, I don't remember why I moved them to xlogwait.h.  OK, moved them

This change made the code no longer compile, because
WaitLSNState->minWaitedLSN is used in xlogrecovery.c which no longer has
access to the field definition.  A rebased version with that change
reverted is attached.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
Thou shalt study thy libraries and strive not to reinvent them without
cause, that thy code may be short and readable and thy days pleasant
and productive. (7th Commandment for C Programmers)

---

From: Xuneng Zhou <[email-masked]>
Date: Thu, 7 Aug 2025 23:00:50 +0800

Hi,

Thanks for working on this.

I’ve just come across this thread and haven’t had a chance to dig into
the patch yet, but I’m keen to review it soon. In the meantime, I have
a quick question: is WAIT FOR REPLY intended mainly for user-defined
functions, or can internal code invoke it as well?

During a recent performance run [1] I noticed heavy polling in
read_local_xlog_page_guts(). Heikki’s comment from a few months ago
also hints that we could replace this check–sleep–repeat loop with the
condition-variable (CV) infrastructure used by walsender:

/*
 * Loop waiting for xlog to be available if necessary
 *
 * TODO: The walsender has its own version of this function, which uses a
 * condition variable to wake up whenever WAL is flushed. We could use the
 * same infrastructure here, instead of the check/sleep/repeat style of
 * loop.
 */

Because read_local_xlog_page_guts() waits for a specific flush or
replay LSN, polling becomes inefficient when the wait is long. I built
a POC patch that swaps polling for CVs, but a single global CV (or
even separate “flush” and “replay” CVs) isn’t ideal:

The wake-up routines don’t know which LSN each waiter cares about, so
they’d have to broadcast on every flush/replay. Caching the minimum
outstanding LSN could reduce spuriously awakened waiters, yet wouldn’t
eliminate them—multiple backends might wait for different LSNs
simultaneously. A more precise solution would require a request queue
that maps waiters to target LSNs and issues targeted wake-ups, adding
complexity.

Walsender accepts the potential broadcast overhead by using two cvs
for different waiters, so it might be acceptable for
read_local_xlog_page_guts() as well. However, if WAIT FOR REPLY
becomes available to backend code, we might leverage it to eliminate
the polling for waiting replay in read_local_xlog_page_guts() without
introducing a bespoke dispatcher. I’d appreciate any thoughts on
whether that use case is in scope.

Best,
Xuneng

[1] https://www.postgresql.org/message-id/[email-masked]

---

From: Alexander Korotkov <[email-masked]>
Date: Fri, 8 Aug 2025 09:54:18 +0300

Hello, Álvaro!

> access to the field definition.  A rebased version with that change

Thank you!  The rebased version looks correct for me.

------
Regards,
Alexander Korotkov
Supabase

---

From: Alexander Korotkov <[email-masked]>
Date: Fri, 8 Aug 2025 10:08:49 +0300

Hi, Xuneng Zhou!

On Thu, Aug 7, 2025 at 6:01 PM Xuneng Zhou <[email-masked]> wrote:
> the patch yet, but I’m keen to review it soon.

Great.  Thank you for your attention to this patch.  I appreciate your
intention to review it.

> In the meantime, I have

Currently, WaitForLSNReplay() is assumed to only be called from
backend, as corresponding shmem is allocated only per-backend.  But
there is absolutely no problem to tweak the patch to allocate shmem
for every Postgres process.  This would enable to call
WaitForLSNReplay() wherever it is needed.  There is only no problem to
extend this approach to support other kinds of LSNs not just replay
LSN.

> whether that use case is in scope.

This looks like a great new use-case for facilities developed in this
patch!  I'll remove the restriction to use WaitForLSNReplay() only in
backend.  I think you can write a patch with additional pairing heap
for flush LSN and include that into thread about
read_local_xlog_page_guts() optimization.  Let me know if you need any
assistance.

------
Regards,
Alexander Korotkov
Supabase

---

From: Xuneng Zhou <[email-masked]>
Date: Sat, 9 Aug 2025 18:52:37 +0800

Hi Alexander!

> extend this approach to support other kinds of LSNs not just replay

Thanks for extending the functionality of the Wait For Replay patch!

> read_local_xlog_page_guts() optimization.  Let me know if you need any

This could be a more elegant approach which would solve the polling
issue well. I'll prepare a follow-up patch for it.

Best,
Xuneng

---

From: Xuneng Zhou <[email-masked]>
Date: Sat, 9 Aug 2025 19:27:25 +0800

Hi,

> Great.  Thank you for your attention to this patch.  I appreciate your

I did a quick pass over v7. There are a few thoughts to share—mostly
around documentation, build, and tests, plus some minor nits. The core
logic looks solid to me. I’ll take a deeper look as I work on a
follow‑up patch to add waiting for flush LSNs. And the patch seems to
need rebase; it can't be applied to HEAD cleanly for now.

Build
1) Consider adding a comma in `src/test/recovery/meson.build` after
`'t/048_vacuum_horizon_floor.pl'` so the list remains valid.

Core code
2) It may be safer for `WaitLSNWakeup()` to assert against the stack array size:
) Perhaps `Assert(numWakeUpProcs < WAKEUP_PROC_STATIC_ARRAY_SIZE);`
rather than `MaxBackends`.
For option parsing UX in `wait.c`, we might prefer:
3) Using `ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
errmsg(...)))` instead of `elog(ERROR, ...)` for consistency and
translatability.
4) Explicitly rejecting duplicate `LSN`/`TIMEOUT` options with a syntax error.
5) The result column label could align better with other utility
outputs if shortened to `status` (lowercase, no space).
6) After `parse_real()`, it could help to validate/clamp the timeout
to avoid overflow when converting to `int64` and when passing a `long`
to `WaitLatch()`.
7) If `nodes/print.h` in `src/backend/commands/wait.c` isn’t used, we
might drop the include.
8) A couple of comment nits: “do it this outside” → “do this outside”.

Tests
9) We might consider adding cases for:
- Negative `TIMEOUT` (to exercise the error path).
- Syntax errors (unknown option; duplicate `LSN`/`TIMEOUT`; missing `LSN`).

Documentation
`doc/src/sgml/ref/wait_for.sgml`
10) The index term could be updated to `<primary>WAIT FOR</primary>`.
11) The synopsis might read more clearly as:
- WAIT FOR LSN '<lsn>' [ TIMEOUT <milliseconds |
'duration-with-units'> ] [ NO_THROW ]
12) The purpose line might be smoother as “wait for a target LSN to be
replayed, optionally with a timeout”.
13) Return values might use `<literal>` for `success`, `timeout`, `not
in recovery`.
14) Consistently calling this a “command” (rather than
function/procedure) could reduce confusion.
15) The example text might read more cleanly as “If the target LSN is
not reached before the timeout …”.

`doc/src/sgml/high-availability.sgml`
16) The sentence could read “However, it is possible to address this
without switching to synchronous replication.”

`src/backend/utils/activity/wait_event_names.txt`
17) The description for `WAIT_FOR_WAL_REPLAY` might be clearer as
“Waiting for WAL replay to reach a target LSN on a standby.”

Best,
Xuneng

---

From: Xuneng Zhou <[email-masked]>
Date: Wed, 27 Aug 2025 23:54:25 +0800

Hi all,

I did a rebase for the patch to v8 and incorporated a few changes:

1) Updated documentation, added new tests, and applied minor code
adjustments based on prior review comments.
2) Tweaked the initialization of waitReplayLSNState so that
non-backend processes can call wait for replay.

Started a new thread [1] and attached a patch addressing the polling
issue in the function
read_local_xlog_page_guts built on the infra of patch v8.

[1] https://www.postgresql.org/message-id/[email-masked]

Feedbacks welcome.

Best,
Xuneng

---

From: Alexander Korotkov <[email-masked]>
Date: Sat, 13 Sep 2025 22:31:32 +0300

Hi, Xuneng!

> [1] https://www.postgresql.org/message-id/[email-masked]

Thank you for your reviewing and revising this patch.

I see you've integrated most of your points expressed in [1].  I went
though them and I've integrated the rest of them.  Except this one.

> - WAIT FOR LSN '<lsn>' [ TIMEOUT <milliseconds | 'duration-with-units'> ] [ NO_THROW ]

I didn't find examples on how we do the similar things on other places
of docs.  This is why I decided to leave this place as it currently
is.

Also, I found some mess up with typedefs.list.  I've returned the
changes to typdefs.list back and re-indented the sources.

I'd like to ask your opinion of the way this feature is implemented in
terms of grammar: generic parsing implemented in gram.y and the rest
is done in wait.c.  I think this approach should minimize additional
keywords and states for parsing code.  This comes at the price of more
complex code in wait.c, but I think this is a fair price.

Links.
1. https://www.postgresql.org/message-id/CABPTF7VsoGDMBq34MpLrMSZyxNZvVbgH6-zxtJOg5AwOoYURbw%40mail.gmail.com

------
Regards,
Alexander Korotkov
Supabase

---

From: Xuneng Zhou <[email-masked]>
Date: Sun, 14 Sep 2025 21:51:21 +0800

Hi Alexander,

> of docs.  This is why I decided to leave this place as it currently

+1. I re-check other commands with similar parameter patterns, and
they follow the approach in v9.

> changes to typdefs.list back and re-indented the sources.

 Thanks for catching and fixing that.

> complex code in wait.c, but I think this is a fair price.

It's LGTM. The same pattern is observed in VACUUM, EXPLAIN, and CREATE
PUBLICATION - all use minimal grammar rules that produce generic
option lists, with the actual interpretation done in their respective
implementation files. The moderate complexity in wait.c seems
acceptable.

Best,
Xuneng

---

From: Alexander Korotkov <[email-masked]>
Date: Mon, 15 Sep 2025 21:59:42 +0300

Hi, Xuneng!

> implementation files. The moderate complexity in wait.c seems

The attached revision of patch contains fix of the typo in the comment
you reported off-list.

------
Regards,
Alexander Korotkov
Supabase

---

From: Álvaro Herrera <[email-masked]>
Date: Mon, 15 Sep 2025 22:24:05 +0200

> > implementation files. The moderate complexity in wait.c seems

Actually I find the code in ExecWaitStmt pretty unusual.  We tend to use
lists of DefElem (a name optionally followed by a value) instead of
individual scattered elements that must later be matched up.  Why not
use utility_option_list instead and then loop on the list of DefElems?
It'd be a lot simpler.

Also, we've found that failing to surround the options by parens leads
to pain down the road, so maybe add that.  Given that the LSN seems to
be mandatory, maybe make it something like

WAIT FOR LSN 'xy/zzy' [ WITH ( utility_option_list ) ]

This requires that you make LSN a keyword, albeit unreserved.  Or you
could make it
WAIT FOR Ident [the rest]
and then ensure in C that the identifier matches the word LSN, such as
we do for "permissive" and "restrictive" in
RowSecurityDefaultPermissive.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/

---

From: Xuneng Zhou <[email-masked]>
Date: Fri, 26 Sep 2025 19:22:42 +0800

Hi Álvaro,

Thanks for your review.

> use utility_option_list instead and then loop on the list of DefElems?

I took a look at commands like VACUUM and EXPLAIN and they do follow
this pattern. v11 will make use of utility_option_list.

> RowSecurityDefaultPermissive.

Shall make LSN an unreserved keyword as well.

Best,
Xuneng

---

From: Xuneng Zhou <[email-masked]>
Date: Sun, 28 Sep 2025 17:02:43 +0800

Hi,

> Shall make LSN an unreserved keyword as well.

Here's the updated v11.  Many thanks Jian for off-list discussions and review.

Best,
Xuneng

---

From: Xuneng Zhou <[email-masked]>
Date: Sat, 4 Oct 2025 09:35:32 +0800

Hi,

> Here's the updated v11.  Many thanks Jian for off-list discussions and review.

v12 removed unused
+WaitStmt
+WaitStmtParam in pgindent/typedefs.list.

Best,
Xuneng

---

From: Xuneng Zhou <[email-masked]>
Date: Tue, 14 Oct 2025 21:03:30 +0800

Hi,

> +WaitStmtParam in pgindent/typedefs.list.

Hi, I’ve split the patch into multiple patch sets for easier review,
per Michael’s advice [1].

[1] https://www.postgresql.org/message-id/aOMsv9TszlB1n-W7%40paquier.xyz

Best,
Xuneng

---

From: Xuneng Zhou <[email-masked]>
Date: Wed, 15 Oct 2025 08:23:09 +0800

Hi,

> [1] https://www.postgresql.org/message-id/aOMsv9TszlB1n-W7%40paquier.xyz

Patch 2 in v13 is corrupted and patch 3 has an error. Sorry for the
noise. Here's v14.

Best,
Xuneng

---

From: Xuneng Zhou <[email-masked]>
Date: Wed, 15 Oct 2025 16:40:03 +0800

Hi,

> Patch 2 in v13 is corrupted and patch 3 has an error. Sorry for the

Made minor changes to #include of xlogwait.h in patch2 to calm CF-bots down.

Best,
Xuneng

---

From: Álvaro Herrera <[email-masked]>
Date: Wed, 15 Oct 2025 10:51:42 +0200

I didn't review the patch other than look at the grammar, but I disagree
with using opt_with in it.  I think WITH should be a mandatory word, or
just not be there at all.  The current formulation lets you do one of:

1. WAIT FOR LSN '123/456' WITH (opt = val);
2. WAIT FOR LSN '123/456' (opt = val);
3. WAIT FOR LSN '123/456';

and I don't see why you need two ways to specify an option list.

So one option is to remove opt_wait_with_clause and just use
opt_utility_option_list, which would remove the WITH keyword from there
(ie. only keep 2 and 3 from the above list).  But I think that's worse:
just look at the REPACK grammar[1], where we have to have additional
productions for the optional parenthesized option list.

So why not do just

+opt_wait_with_clause:
+           WITH '(' utility_option_list ')'        { $$ = $3; }
+           | /*EMPTY*/                             { $$ = NIL; }
+           ;

which keeps options 1 and 3 of the list above.

Note: you don't need to worry about WITH_LA, because that's only going
to show up when the user writes WITH TIME or WITH ORDINALITY (see
parser.c), and that's a syntax error anyway.

[1] https://postgr.es/m/[email-masked]

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"La virtud es el justo medio entre dos defectos" (Aristóteles)

---

From: Xuneng Zhou <[email-masked]>
Date: Wed, 15 Oct 2025 20:48:29 +0800

Hi,

Thank you for the grammar review and the clear recommendation.

> and I don't see why you need two ways to specify an option list.

I agree with this as unnecessary choices are confusing.

> which keeps options 1 and 3 of the list above.

Your suggested approach of making WITH mandatory when options are
present looks better.
I've implemented the change as you recommended. Please see patch 3 in v16.

> parser.c), and that's a syntax error anyway.

Yeah, we require '(' immediately after WITH in our grammar, the
lookahead mechanism will keep it as regular WITH, and any attempt to
write "WITH TIME" or "WITH ORDINALITY" would be a syntax error anyway,
which is expected.

Best,
Xuneng

---

From: Xuneng Zhou <[email-masked]>
Date: Thu, 16 Oct 2025 15:11:58 +0800

Hi,

> write "WITH TIME" or "WITH ORDINALITY" would be a syntax error anyway,

The filename of patch 1 is incorrect due to coping. Just correct it.

Best,
Xuneng

---

From: Alexander Korotkov <[email-masked]>
Date: Thu, 23 Oct 2025 13:46:27 +0300

Hi!

> The filename of patch 1 is incorrect due to coping. Just correct it.

Thank you for rebasing the patch.

I've revised it.  The significant changes has been made to 0002, where
I reduced the code duplication.  Also, I run pgindent and pgperltidy
and made other small improvements.
Please, check.

------
Regards,
Alexander Korotkov
Supabase

---

From: Xuneng Zhou <[email-masked]>
Date: Thu, 23 Oct 2025 20:58:46 +0800

Hi,

> and made other small improvements.

Thanks for updating the patch set!
Patch 2 looks more elegant after the revision. I’ll review them soon.

Best,
Xuneng

---

From: Xuneng Zhou <[email-masked]>
Date: Sun, 2 Nov 2025 14:24:48 +0800

Hi, Alexander!

> Patch 2 looks more elegant after the revision. I’ll review them soon.

I’ve made a few minor updates to the comments and docs in patches 2
and 3. The patch set LGTM now.

Best,
Xuneng

---

From: Xuneng Zhou <[email-masked]>
Date: Mon, 3 Nov 2025 10:20:28 +0800

Hi,

> and 3. The patch set LGTM now.

Fix an minor issue in v18: WaitStmt was mistakenly added to
pgindent/typedefs.list in patch 2, but it should belong to patch 3.

Best,
Xuneng

---

From: Alexander Korotkov <[email-masked]>
Date: Mon, 3 Nov 2025 13:46:40 +0200

Hello, Xuneng!

> pgindent/typedefs.list in patch 2, but it should belong to patch 3.

Thank you.  I also made some minor changes to 0002 renaming
"operation" => "lsnType".

I'd like to give this subject another chance for pg19.  I'm going to
push this if no objections.

------
Regards,
Alexander Korotkov
Supabase

---

From: Álvaro Herrera <[email-masked]>
Date: Mon, 3 Nov 2025 16:06:58 +0100

> push this if no objections.

Sure.  I don't understand why patches 0002 and 0003 are separate though.

---

From: Andres Freund <[email-masked]>
Date: Mon, 3 Nov 2025 10:13:02 -0500

Hi,

> Sure.  I don't understand why patches 0002 and 0003 are separate though.

FWIW, I appreciate such splits. Even if the functionality isn't usable
independently, it's still different type of code that's affected. And the
patches are each big enough to make that worthwhile for easier review.

One thing that'd be nice to do once we have WAIT FOR is to make the common
case of wait_for_catchup() use this facility, instead of polling...

Greetings,

Andres Freund

---

From: Alexander Korotkov <[email-masked]>
Date: Wed, 5 Nov 2025 11:51:18 +0200

Hi!

> patches are each big enough to make that worthwhile for easier review.

Thank you for the feedback, pushed.

> case of wait_for_catchup() use this facility, instead of polling...

The draft patch for that is attached.  WAIT FOR doesn't handle all the
possible use cases of wait_for_catchup(), but I've added usage when
it's appropriate.

------
Regards,
Alexander Korotkov
Supabase

---

From: Xuneng Zhou <[email-masked]>
Date: Wed, 5 Nov 2025 22:03:25 +0800

Hi,

> Thank you for the feedback, pushed.

Thanks for pushing them!

> possible use cases of wait_for_catchup(), but I've added usage when

Interesting, could this approach be extended to the flush and other
modes as well? I might need to spend some time to understand it before
I can provide a meaningful review.

Best,
Xuneng

---

From: Alexander Korotkov <[email-masked]>
Date: Sat, 8 Nov 2025 00:02:36 +0200

> I can provide a meaningful review.

I think we might end up extending WaitLSNType enum.  However, I hate
inHeap and heapNode arrays growing in WaitLSNProcInfo as they are
allocated per process.  I found that we could optimize WaitLSNProcInfo
struct turning them into simple variables because a single process can
wait only for a single LSN at a time.  Please, check the attached
patch.

------
Regards,
Alexander Korotkov
Supabase

---

From: Xuneng Zhou <[email-masked]>
Date: Wed, 12 Nov 2025 15:19:53 +0800

Hi,

> possible use cases of wait_for_catchup(), but I've added usage when

I tested the patch using make check-world, and it worked well. I also
made a few adjustments:

- Added an unconditional chomp($isrecovery) after querying
pg_is_in_recovery() to prevent newline mismatches when $target_lsn is
accidently defined.
- Added chomp($output) to normalize the result from WAIT FOR LSN
before comparison.

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.

--
Best,
Xuneng

---

From: Tomas Vondra <[email-masked]>
Date: Thu, 13 Nov 2025 21:32:31 +0100

Hi,

The new TAP test 049_wait_for_lsn.pl introduced by this commit, because
it takes a long time - about 65 seconds on my laptop. That's about 25%
of the whole src/test/recovery, more than any other test.

And most of the time there's nothing happening - these are the two log
messages showing the 60-second wait:

2025-11-13 21:12:39.949 CET checkpointer[562597] LOG:  checkpoint
complete: wrote 9 buffers (7.0%), wrote 3 SLRU buffers; 0 WAL file(s)
added, 0 removed, 2 recycled; write=0.906 s, sync=0.001 s, total=0.907
s; sync files=0, longest=0.000 s, average=0.000 s; distance=32768 kB,
estimate=32768 kB; lsn=0/040000B8, redo lsn=0/04000060

2025-11-13 21:13:38.994 CET client backend[562727] 049_wait_for_lsn.pl
ERROR:  recovery is not in progress

So there's a checkpoint, 60 seconds of nothing, and then a failure. I
haven't looked into why it waits for 1 minute exactly, but adding 60
seconds to check-world is somewhat annoying.

While at it, I noticed a couple comments refer to WaitForLSNReplay, but
but I think that got renamed simply to WaitForLSN.

regards

-- 
Tomas Vondra

---

From: Xuneng Zhou <[email-masked]>
Date: Fri, 14 Nov 2025 09:49:51 +0800

Hi Tomas,

> seconds to check-world is somewhat annoying.

Thanks for looking into this!

I did a quick analysis for this prolonged waiting:

In WaitLSNWakeup() (xlogwait.c:267), the fast-path check incorrectly
handled InvalidXLogRecPtr:
/* Fast path check */
if (pg_atomic_read_u64(&waitLSNState->minWaitedLSN[i]) > currentLSN)
    return;  // Issue: Returns early when currentLSN = 0

When currentLSN = InvalidXLogRecPtr (0), meaning "wake all waiters",
the check compared:
- minWaitedLSN (e.g., 0x570CC048) > 0 → TRUE
- Result: function returned early without waking anyone

When It Happened
During standby promotion, xlog.c:6246 calls:

WaitLSNWakeup(WAIT_LSN_TYPE_REPLAY, InvalidXLogRecPtr);

This should wake all LSN waiters, but the bug prevented it. WAIT FOR
LSN commands could wait indefinitely. Test 049_wait_for_lsn.pl took 68
seconds instead of ~9 seconds.

if the above analysis is sound, the fix could be like:

Proposed fix:
Added a validity check before the comparison:
/*
 * Fast path check.  Skip if currentLSN is InvalidXLogRecPtr, which means
 * "wake all waiters" (e.g., during promotion when recovery ends).
 */
if (XLogRecPtrIsValid(currentLSN) &&
    pg_atomic_read_u64(&waitLSNState->minWaitedLSN[i]) > currentLSN)
    return;

Result:
Test time: 68s → 9s
WAIT FOR LSN exits immediately on promotion (62ms vs 60s)

> but I think that got renamed simply to WaitForLSN.

Please check the attached patch for replacing them.

-- 
Best,
Xuneng