<para>

The procedure shown in <xref linkend="recovery-synchronization-procedure-table"/>

can be executed only during recovery.

</para>

<table id="recovery-synchronization-procedure-table">

<title>Recovery Synchronization Procedure and Function</title>

<tgroup cols="1">

<thead>

<row>

<entry role="func_table_entry"><para role="func_signature">

Procedure or Function

</para>

<para>

Type

</para>

<para>

Description

</para></entry>

</row>

</thead>

<tbody>

<row>

<entry role="func_table_entry"><para role="func_signature">

<indexterm>

<primary>pg_wal_replay_wait</primary>

</indexterm>

<function>pg_wal_replay_wait</function> (

<parameter>target_lsn</parameter> <type>pg_lsn</type>,

<parameter>timeout</parameter> <type>bigint</type> <literal>DEFAULT</literal> <literal>0</literal>,

<parameter>no_error</parameter> <type>bool</type> <literal>DEFAULT</literal> <literal>false</literal>)

</para>

<para>

Procedure

</para>

<para>

Waits until recovery replays <literal>target_lsn</literal>.

If no <parameter>timeout</parameter> is specified or it is set to

zero, this procedure waits indefinitely for the

<literal>target_lsn</literal>. If the <parameter>timeout</parameter>

is specified (in milliseconds) and is greater than zero, the

procedure waits until <literal>target_lsn</literal> is reached or

the specified <parameter>timeout</parameter> has elapsed.

On timeout, or if the server is promoted before

<literal>target_lsn</literal> is reached, an error is emitted,

as soon as <parameter>no_error</parameter> is false.

If <parameter>no_error</parameter> is set to true, then the procedure

doesn't throw errors. The last result status could be read

with <function>pg_wal_replay_wait_status</function>.

</para></entry>

</row>

<row>

<entry role="func_table_entry"><para role="func_signature">

<indexterm>

<primary>pg_wal_replay_wait_status</primary>

</indexterm>

<function>pg_wal_replay_wait_status</function> ()

<returnvalue>text</returnvalue>

</para>

<para>

Function

</para>

<para>

Returns the last result status for

<function>pg_wal_replay_wait</function> procedure. The possible

values are <literal>success</literal>, <literal>timeout</literal>,

and <literal>not in recovery</literal>.

</para></entry>

</row>

</tbody>

</tgroup>

</table>

<para>

<function>pg_wal_replay_wait</function> waits till

<parameter>target_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>target_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 <acronym>lsn</acronym> of the last

modification should be stored on the client application side or the

connection pooler side.

</para>

<para>

<function>pg_wal_replay_wait</function> should be called on standby.

If a user calls <function>pg_wal_replay_wait</function> on primary, it

will error out as soon as <parameter>no_error</parameter> is false.

However, if <function>pg_wal_replay_wait</function> is

called on primary promoted from standby and <literal>target_lsn</literal>

was already replayed, then <function>pg_wal_replay_wait</function> just

exits immediately.

</para>

<para>

You can use <function>pg_wal_replay_wait</function> 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 <function>pg_wal_replay_wait</function>

with the <acronym>lsn</acronym> obtained from primary. After that the

changes made on primary should be guaranteed to be visible on replica.

<programlisting>

postgres=# CALL pg_wal_replay_wait('0/306EE20');

CALL

postgres=# SELECT * FROM movie WHERE genre = 'Drama';

genre

-------

(0 rows)

</programlisting>

It may also happen that target <acronym>lsn</acronym> is not reached

within the timeout. In that case the error is thrown.

<programlisting>

postgres=# CALL pg_wal_replay_wait('0/306EE20', 100);

ERROR: timed out while waiting for target LSN 0/306EE20 to be replayed; current replay LSN 0/306EA60

</programlisting>

The same example uses <function>pg_wal_replay_wait</function> with

<parameter>no_error</parameter> set to true. In this case, the result

status must be read with <function>pg_wal_replay_wait_status</function>.

<programlisting>

postgres=# CALL pg_wal_replay_wait('0/306EE20', 100, true);

CALL

postgres=# SELECT pg_wal_replay_wait_status();

pg_wal_replay_wait_status

---------------------------

timeout

(1 row)

</programlisting>

</para>

<para>

<function>pg_wal_replay_wait</function> can't be used within

a transaction with an isolation level higher than

<literal>READ COMMITTED</literal>, another procedure, or a function.

All the cases above imply holding a snapshot, which could prevent

WAL records from replaying (see <xref linkend="hot-standby-conflict"/>)

and cause an indirect deadlock.

<programlisting>

postgres=# BEGIN;

BEGIN

postgres=*# CALL pg_wal_replay_wait('0/306EE20');

ERROR: pg_wal_replay_wait() must be only called without an active or registered snapshot

DETAIL: Make sure pg_wal_replay_wait() isn't called within a transaction with an isolation level higher than READ COMMITTED, another procedure, or a function.

</programlisting>

</para>

xlogutils.o \

xlogwait.o

'xlogwait.c',

#include "access/xlogwait.h"

/*

WaitLSNCleanup();

#include "access/xlogwait.h"

/*

WaitLSNWakeup(InvalidXLogRecPtr);

#include "access/xlogwait.h"

#include "storage/proc.h"

#include "utils/snapmgr.h"

static WaitLSNResult lastWaitLSNResult = WAIT_LSN_RESULT_SUCCESS;

pg_wal_replay_wait(PG_FUNCTION_ARGS)

{

XLogRecPtr target_lsn = PG_GETARG_LSN(0);

int64 timeout = PG_GETARG_INT64(1);

bool no_error = PG_GETARG_BOOL(2);

if (timeout < 0)

ereport(ERROR,

(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),

errmsg("\"timeout\" must not be negative")));

/*

if (ActiveSnapshotSet())

PopActiveSnapshot();

/*

InvalidateCatalogSnapshot();

/* Give up if there is still an active or registered snapshot. */

if (GetOldestSnapshot())

ereport(ERROR,

(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),

errmsg("pg_wal_replay_wait() must be only called without an active or registered snapshot"),

errdetail("Make sure pg_wal_replay_wait() isn't called within a transaction with an isolation level higher than READ COMMITTED, another procedure, or a function.")));

/*

Assert(MyProc->xmin == InvalidTransactionId);

lastWaitLSNResult = WaitForLSNReplay(target_lsn, timeout);

if (no_error)

PG_RETURN_VOID();

/*

switch (lastWaitLSNResult)

{

case WAIT_LSN_RESULT_SUCCESS:

/* Nothing to do on success */

break;

case WAIT_LSN_RESULT_TIMEOUT:

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(target_lsn),

LSN_FORMAT_ARGS(GetXLogReplayRecPtr(NULL)))));

break;

case WAIT_LSN_RESULT_NOT_IN_RECOVERY:

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(target_lsn),

LSN_FORMAT_ARGS(GetXLogReplayRecPtr(NULL)))));

break;

}

PG_RETURN_VOID();

}

pg_wal_replay_wait_status(PG_FUNCTION_ARGS)

{

const char *result_string = "";

/* Process the result of WaitForLSNReplay(). */

switch (lastWaitLSNResult)

{

case WAIT_LSN_RESULT_SUCCESS:

result_string = "success";

break;

case WAIT_LSN_RESULT_TIMEOUT:

result_string = "timeout";

break;

case WAIT_LSN_RESULT_NOT_IN_RECOVERY:

result_string = "not in recovery";

break;

}

PG_RETURN_TEXT_P(cstring_to_text(result_string));

}

#include "access/xlogwait.h"

/*

if (waitLSNState &&

(XLogRecoveryCtl->lastReplayedEndRecPtr >=

pg_atomic_read_u64(&waitLSNState->minWaitedLSN)))

WaitLSNWakeup(XLogRecoveryCtl->lastReplayedEndRecPtr);