Horton

Thread

Commits

  1. Wake LSN waiters before recovery target stop

  2. Implement WAIT FOR command

  1. pgsql: Implement WAIT FOR command

    Alexander Korotkov <akorotkov@postgresql.org> — 2025-11-05T09:44:46Z

    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.

WAIT FOR needs to wait without any snapshot held.  Otherwise, the snapshot
could prevent the replay of WAL records, implying a kind of self-deadlock.
This is why separate utility command seems appears to be the most robust
way to implement this functionality.  It's not possible to implement this as
a function.  Previous experience shows that stored procedures also have
limitation in this aspect.

Discussion: https://www.postgresql.org/message-id/flat/CAPpHfdsjtZLVzxjGT8rJHCYbM0D5dwkO+BBjcirozJ6nYbOW8Q@mail.gmail.com
Discussion: https://www.postgresql.org/message-id/flat/CABPTF7UNft368x-RgOXkfj475OwEbp%2BVVO-wEXz7StgjD_%3D6sw%40mail.gmail.com
Author: Kartyshov Ivan <i.kartyshov@postgrespro.ru>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Author: Xuneng Zhou <xunengzhou@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Reviewed-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Reviewed-by: Dilip Kumar <dilipbalaut@gmail.com>
Reviewed-by: Amit Kapila <amit.kapila16@gmail.com>
Reviewed-by: Alexander Lakhin <exclusion@gmail.com>
Reviewed-by: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Reviewed-by: Euler Taveira <euler@eulerto.com>
Reviewed-by: Heikki Linnakangas <hlinnaka@iki.fi>
Reviewed-by: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Reviewed-by: jian he <jian.universality@gmail.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Reviewed-by: Xuneng Zhou <xunengzhou@gmail.com>

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/447aae13b0305780e87cac7b0dd669db6fab3d9d

Modified Files
--------------
doc/src/sgml/high-availability.sgml       |  54 ++++++
doc/src/sgml/ref/allfiles.sgml            |   1 +
doc/src/sgml/ref/wait_for.sgml            | 234 +++++++++++++++++++++++
doc/src/sgml/reference.sgml               |   1 +
src/backend/access/transam/xact.c         |   6 +
src/backend/access/transam/xlog.c         |   7 +
src/backend/access/transam/xlogrecovery.c |  11 ++
src/backend/commands/Makefile             |   3 +-
src/backend/commands/meson.build          |   1 +
src/backend/commands/wait.c               | 212 +++++++++++++++++++++
src/backend/parser/gram.y                 |  33 +++-
src/backend/storage/lmgr/proc.c           |   6 +
src/backend/tcop/pquery.c                 |  12 +-
src/backend/tcop/utility.c                |  22 +++
src/include/commands/wait.h               |  22 +++
src/include/nodes/parsenodes.h            |   8 +
src/include/parser/kwlist.h               |   2 +
src/include/tcop/cmdtaglist.h             |   1 +
src/test/recovery/meson.build             |   3 +-
src/test/recovery/t/049_wait_for_lsn.pl   | 302 ++++++++++++++++++++++++++++++
src/tools/pgindent/typedefs.list          |   1 +
21 files changed, 931 insertions(+), 11 deletions(-)

  2. Re: pgsql: Implement WAIT FOR command

    Heikki Linnakangas <hlinnaka@iki.fi> — 2026-01-26T23:33:50Z

    On 05/11/2025 11:44, Alexander Korotkov wrote:
> Implement WAIT FOR command

Happened to just spot this:

> @@ -1831,20 +1832,30 @@ PerformWalRecovery(void)
>  			 */
>  			ApplyWalRecord(xlogreader, record, &replayTLI);
>  
>  			/* Exit loop if we reached inclusive recovery target */
>  			if (recoveryStopsAfter(xlogreader))
>  			{
>  				reachedRecoveryTarget = true;
>  				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[WAIT_LSN_TYPE_REPLAY])))
> +				WaitLSNWakeup(WAIT_LSN_TYPE_REPLAY, XLogRecoveryCtl->lastReplayedEndRecPtr);
> +
>  			/* Else, try to fetch the next WAL record */
>  			record = ReadRecord(xlogprefetcher, LOG, false, replayTLI);
>  		} while (record != NULL);
>  
>  		/*
>  		 * end of main redo apply loop
>  		 */
>  
>  		if (reachedRecoveryTarget)
>  		{

I think that WaitLSNWakeup() call is a little misplaced. It's after the 
recoveryStopsAfter() call, so if recovery is paused right after the 
record, the waiters won't be woken up until recovery is continued. And I 
suppose if recovery is stopped and the server is promoted, the waiters 
will never be woken up, although I didn't verify that.

To fix, that should be moved to between the ApplyWalRecord() and 
recoveryStopsHere() calls.

- Heikki