diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/func.sgml | 117 |
1 files changed, 117 insertions, 0 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index b39f97dc8de..3cf896b22fa 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -28911,6 +28911,123 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset the pause, the rate of WAL generation and available disk space. </para> + <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</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="func_table_entry"><para role="func_signature"> + Procedure + </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>) + <returnvalue>void</returnvalue> + </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. + </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> + 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 of 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 achieved + 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> + + </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> </sect2> <sect2 id="functions-snapshot-synchronization"> |