Horton

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

Ivan Kartyshov <i.kartyshov@postgrespro.ru>

View thread View on postgr.es
From: Kartyshov Ivan <i.kartyshov@postgrespro.ru>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Date: 2024-03-19T17:38:55Z
Lists: pgsql-hackers

Attachments

Intro
==========
The main purpose of the feature is to achieve
read-your-writes-consistency, while using async replica for reads and
primary for writes. In that case lsn of last modification is stored
inside application. We cannot store this lsn inside database, since
reads are distributed across all replicas and primary.


Procedure style implementation
==========
https://www.postgresql.org/message-id/27171.1586439221%40sss.pgh.pa.us
https://www.postgresql.org/message-id/20210121.173009.235021120161403875.horikyota.ntt%40gmail.com

CALL pg_wait_lsn(‘LSN’, timeout);

Examples
==========

primary         standby
-------         --------
              postgresql.conf
              recovery_min_apply_delay = 10s


CREATE TABLE tbl AS SELECT generate_series(1,10) AS a;
INSERT INTO tbl VALUES (generate_series(11, 20));
SELECT pg_current_wal_lsn();


              CALL pg_wait_lsn('0/3002AE8', 10000);
              BEGIN;
              SELECT * FROM tbl; // read fresh insertions
              COMMIT;

Fixed and ready to review.

-- 
Ivan Kartyshov
Postgres Professional: www.postgrespro.com

Commits

  1. Ensure standby promotion point in 043_wal_replay_wait.pl

  2. Minor cleanup related to pg_wal_replay_wait() procedure

  3. Adjust pg_wal_replay_wait() procedure behavior on promoted standby

  4. pg_wal_replay_wait(): Fix typo in the doc

  5. Implement pg_wal_replay_wait() stored procedure

  6. Revert: Implement pg_wal_replay_wait() stored procedure

  7. Call WaitLSNCleanup() in AbortTransaction()

  8. Clarify what is protected by WaitLSNLock

  9. Use an LWLock instead of a spinlock in waitlsn.c

  10. Use the pairing heap instead of a flat array for LSN replay waiters

  11. Minor improvements for waitlsn.c

  12. Make the order of the header file includes consistent