doc/src/sgml/ref/wait_for.sgml - master - githubmirror/postgres

doc/src/sgml/ref/wait_for.sgml
383 строки13 KB
Alexander Korotkov
Document that WAIT FOR LSN is timeline-blind
03 май 2026, 16:22
03 май 2026, 16:2221d2901
<!--
doc/src/sgml/ref/wait_for.sgml
PostgreSQL documentation
-->

<refentry id="sql-wait-for">
 <indexterm zone="sql-wait-for">
  <primary>WAIT FOR</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>WAIT FOR</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>WAIT FOR</refname>
  <refpurpose>wait for WAL to reach a target <acronym>LSN</acronym></refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
WAIT FOR LSN '<replaceable class="parameter">lsn</replaceable>'
    [ WITH ( <replaceable class="parameter">option</replaceable> [, ...] ) ]

<phrase>where <replaceable class="parameter">option</replaceable> can be:</phrase>

    MODE '<replaceable class="parameter">mode</replaceable>'
    TIMEOUT '<replaceable class="parameter">timeout</replaceable>'
    NO_THROW

<phrase>and <replaceable class="parameter">mode</replaceable> can be:</phrase>

    standby_replay | standby_write | standby_flush | primary_flush
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   Waits until the specified <parameter>lsn</parameter> is reached
   according to the specified <parameter>mode</parameter>,
   which determines whether to wait for WAL to be written, flushed, or replayed.
   If no <parameter>timeout</parameter> is specified or it is set to
   zero, this command waits indefinitely for the
   <parameter>lsn</parameter>.
  </para>

  <para>
   On timeout, an error is emitted unless <literal>NO_THROW</literal>
   is specified in the WITH clause. For standby modes
   (<literal>standby_replay</literal>, <literal>standby_write</literal>,
   <literal>standby_flush</literal>), an error is also emitted if the
   server is promoted before the <parameter>lsn</parameter> is reached.
   If <parameter>NO_THROW</parameter> is specified, the command returns
   a status string instead of throwing 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>
      Specifies the target <acronym>LSN</acronym> to wait for.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>WITH ( <replaceable class="parameter">option</replaceable> [, ...] )</literal></term>
    <listitem>
     <para>
      This clause specifies optional parameters for the wait operation.
      The following parameters are supported:

      <variablelist>
       <varlistentry>
        <term><literal>MODE</literal> '<replaceable class="parameter">mode</replaceable>'</term>
        <listitem>
         <para>
          Specifies the type of LSN processing to wait for. If not specified,
          the default is <literal>standby_replay</literal>. The valid modes are:
         </para>
         <itemizedlist>
          <listitem>
           <para>
            <literal>standby_replay</literal>: Wait for the LSN to be replayed
            (applied to the database) on a standby server. After successful
            completion, <function>pg_last_wal_replay_lsn()</function> will
            return a value greater than or equal to the target LSN. This mode
            can only be used during recovery.
           </para>
          </listitem>
          <listitem>
           <para>
            <literal>standby_write</literal>: Wait for the WAL containing the
            LSN to be written to disk on a standby server, but not yet
            necessarily flushed.  This is faster than
            <literal>standby_flush</literal> but provides weaker durability
            guarantees since the data may still be in operating system
            buffers.  This is satisfied by WAL already present on the
            standby from a base backup, archive restore, or prior
            streaming, as well as WAL newly received from the primary.
            This mode can only be used during recovery.
           </para>
          </listitem>
          <listitem>
           <para>
            <literal>standby_flush</literal>: Wait for the WAL containing the
            LSN to be flushed to disk on a standby server.  This provides
            a durability guarantee without waiting for the WAL to be
            applied.  This is satisfied by WAL already present on the
            standby from a base backup, archive restore, or prior
            streaming, as well as WAL newly received from the primary.
            This mode can only be used during recovery.
           </para>
          </listitem>
          <listitem>
           <para>
            <literal>primary_flush</literal>: Wait for the WAL containing the
            LSN to be flushed to disk on a primary server. After successful
            completion, <function>pg_current_wal_flush_lsn()</function> will
            return a value greater than or equal to the target LSN. This mode
            can only be used on a primary server (not during recovery).
           </para>
          </listitem>
         </itemizedlist>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><literal>TIMEOUT</literal> '<replaceable class="parameter">timeout</replaceable>'</term>
        <listitem>
         <para>
          When specified and <parameter>timeout</parameter> is greater than zero,
          the command waits until <parameter>lsn</parameter> is reached or
          the specified <parameter>timeout</parameter> has elapsed.
         </para>
         <para>
          The <parameter>timeout</parameter> might be given as integer number of
          milliseconds.  Also it might be given as string literal with
          integer number of milliseconds or a number with unit
          (see <xref linkend="config-setting-names-values"/>).
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><literal>NO_THROW</literal></term>
        <listitem>
         <para>
          Specify to not throw an error in the case of timeout or
          running on the primary.  In this case the result status can be get from
          the return value.
         </para>
        </listitem>
       </varlistentry>
      </variablelist>
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Outputs</title>

  <variablelist>
   <varlistentry>
    <term><literal>success</literal></term>
    <listitem>
     <para>
      This return value denotes that we have successfully reached
      the target <parameter>lsn</parameter>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>timeout</literal></term>
    <listitem>
     <para>
      This return value denotes that the timeout happened before reaching
      the target <parameter>lsn</parameter>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>not in recovery</literal></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 (i.e., executed on a primary),
      or it was promoted before reaching the target <parameter>lsn</parameter>.
      In the promotion case, this status indicates a timeline change occurred,
      and the application should re-evaluate whether the target LSN is still
      relevant.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Notes</title>
  <para>
   <command>WAIT FOR</command> must be executed as a top-level command.
   It cannot be executed from a function, procedure, or
   <command>DO</command> block. It also requires that no active or
   registered snapshot be held, and therefore cannot be used in contexts
   where such a snapshot must remain active, including transactions running
   at isolation levels higher than <literal>READ COMMITTED</literal>.
  </para>

  <para>
   <command>WAIT FOR</command> waits until the specified
   <parameter>lsn</parameter> is reached according to the specified
   <parameter>mode</parameter>. The <literal>standby_replay</literal> mode
   waits for the LSN to be replayed (applied to the database), which is
   useful to achieve read-your-writes consistency while using an async
   replica for reads and the primary for writes. The
   <literal>standby_flush</literal> mode waits for the WAL to be flushed
   to durable storage on the replica, or to have already been replayed
   from WAL present on the standby. The <literal>standby_write</literal> mode
   waits for the WAL to be written to the operating system, or to have
   already been replayed, which is faster than flush for newly received
   WAL but provides weaker durability guarantees. The
   <literal>primary_flush</literal> mode waits for WAL to be flushed on
   a primary server. In all cases, the <acronym>LSN</acronym> of the last
   modification should be stored on the client application side or the
   connection pooler side.
  </para>

  <para>
   The standby modes (<literal>standby_replay</literal>,
   <literal>standby_write</literal>, <literal>standby_flush</literal>)
   can only be used during recovery, and <literal>primary_flush</literal>
   can only be used on a primary server. Using the wrong mode for the
   current server state will result in an error. If a standby is promoted
   while waiting with a standby mode, the command will return
   <literal>not in recovery</literal> (or throw an error if
   <literal>NO_THROW</literal> is not specified). Promotion creates a new
   timeline, and the LSN being waited for may refer to WAL from the old
   timeline.
  </para>

  <para>
   <command>WAIT FOR</command> compares only the numeric
   <acronym>LSN</acronym>; it has no notion of which timeline a WAL
   record belongs to.  This matters when a standby continues recovery
   across an upstream timeline switch &mdash; for example, a cascading
   standby whose upstream gets promoted.  In that case
   <command>WAIT FOR</command> will return <literal>success</literal>
   as soon as the position used by the selected wait mode reaches or
   passes the numeric <acronym>LSN</acronym>, regardless of which
   timeline that <acronym>LSN</acronym> belongs to.  Applications that need to
   confirm the target refers to the expected timeline must validate
   the timeline themselves.
  </para>

  <para>
   On a standby server, <command>WAIT FOR</command> sessions may be
   interrupted by recovery conflicts.  Some recovery conflicts are
   unavoidable: for example, replaying a tablespace drop resolves
   conflicts by terminating all backends, regardless of what they are
   doing.  Applications using <command>WAIT FOR</command> on a standby
   should be prepared to handle such interruptions, for example by
   retrying the command or falling back to an alternative mechanism.
  </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';
 status
---------
 success
(1 row)
postgres=# SELECT * FROM movie WHERE genre = 'Drama';
 genre
-------
(0 rows)
</programlisting>
  </para>

  <para>
   Wait for flush (data durable on replica):

<programlisting>
postgres=# WAIT FOR LSN '0/306EE20' WITH (MODE 'standby_flush');
 status
---------
 success
(1 row)
</programlisting>
  </para>

  <para>
   Wait for write with timeout:

<programlisting>
postgres=# WAIT FOR LSN '0/306EE20' WITH (MODE 'standby_write', TIMEOUT '100ms', NO_THROW);
 status
---------
 success
(1 row)
</programlisting>
  </para>

  <para>
   Wait for flush on primary:

<programlisting>
postgres=# WAIT FOR LSN '0/306EE20' WITH (MODE 'primary_flush');
 status
---------
 success
(1 row)
</programlisting>
  </para>

  <para>
   If the target LSN is not reached before the timeout, an error is thrown:

<programlisting>
postgres=# WAIT FOR LSN '0/306EE20' WITH (TIMEOUT '0.1s');
ERROR:  timed out while waiting for target LSN 0/306EE20 to be replayed; current replay LSN 0/306EA60
</programlisting>
  </para>

  <para>
   The same example uses <command>WAIT FOR</command> with
   <parameter>NO_THROW</parameter> option:

<programlisting>
postgres=# WAIT FOR LSN '0/306EE20' WITH (TIMEOUT '100ms', NO_THROW);
 status
---------
 timeout
(1 row)
</programlisting>
  </para>
 </refsect1>
</refentry>