Re: [HACKERS] make async slave to wait for lsn to be replayed

On 2020-04-04 03:14, Alexander Korotkov wrote:
> I think that now we would be fine with single LSN and single TIMEOUT.
> In future we may add multiple LSNs/TIMEOUTs or/and support for
> expressions as LSNs/TIMEOUTs if we figure out it's necessary.
> 
> I also think it's good to couple waiting for lsn with beginning of
> transaction is good idea.  Separate WAIT FOR LSN statement called in
> the middle of transaction looks problematic for me. Imagine we have RR
> isolation and already acquired the snapshot.  Then out snapshot can
> block applying wal records, which we are waiting for.  That would be
> implicit deadlock.  It would be nice to evade such deadlocks by
> design.
Ok, here is a new version of patch with single LSN and TIMEOUT.

Synopsis
==========
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ] [WAIT FOR LSN 
'lsn' [ TIMEOUT 'value']]
and
START TRANSACTION [ transaction_mode [, ...] ] [WAIT FOR LSN 'lsn' [ 
TIMEOUT 'value']]
      where lsn is result of pg_current_wal_flush_lsn on master.
      and value is uint time interval in milliseconds.
Description
==========
BEGIN/START...WAIT FOR - pause the start of transaction until a 
specified LSN has
been replayed. (Don’t open transaction if lsn is not reached on 
timeout).

How to use it
==========
WAIT FOR LSN ‘LSN’ [, timeout in ms];

# Before starting transaction, wait until LSN 0/84832E8 is replayed. 
Wait time is
not limited here because a timeout was not specified
BEGIN WAIT FOR LSN '0/84832E8';

# Before starting transaction, wait until LSN 0/84832E8 is replayed. 
Limit the wait
time with 10 seconds, and if LSN is not reached by then, don't start the 
transaction.
START TRANSACTION WAIT FOR LSN '0/8DFFB88' TIMEOUT 10000;

# Same as previous, but with transaction isolation level = REPEATABLE 
READ
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ WAIT FOR LSN 
'0/815C0F1' TIMEOUT 10000;

Notice: WAIT FOR will release on PostmasterDeath or Interruption events
if they come earlier than LSN or timeout.

Testing the implementation
======================
The implementation was tested with src/test/recovery/t/020_begin_wait.pl

-- 
Ivan Kartyshov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company