Horton

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

Amit Kapila <amit.kapila16@gmail.com>

View thread View on postgr.es
From: Amit Kapila <amit.kapila16@gmail.com>
To: Kartyshov Ivan <i.kartyshov@postgrespro.ru>
Cc: dilipbalaut@gmail.com, smithpb2250@gmail.com, pgsql-hackers@lists.postgresql.org
Date: 2024-03-07T12:24:55Z
Lists: pgsql-hackers 
On Thu, Mar 7, 2024 at 5:14 PM Kartyshov Ivan
<i.kartyshov@postgrespro.ru> wrote:
>
> 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.
>
>
> Two implementations of one feature
> ==========
> We left two different solutions. Help me please to choose the best.
>
>
> 1) Classic (wait_classic_v7.patch)
> https://www.postgresql.org/message-id/3cc883048264c2e9af022033925ff8db%40postgrespro.ru
> Synopsis
> ==========
> advantages: multiple wait events, separate WAIT FOR statement
> disadvantages: new words in grammar
>
>
>
> WAIT FOR  [ANY | ALL] event [, ...]
> BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]
>      [ WAIT FOR [ANY | ALL] event [, ...]]
> event:
> LSN value
> TIMEOUT number_of_milliseconds
> timestamp
>
>
>
> 2) After style: Kyotaro and Freund (wait_after_within_v6.patch)
> https://www.postgresql.org/message-id/d3ff2e363af60b345f82396992595a03%40postgrespro.ru
> Synopsis
> ==========
> advantages: no new words in grammar
> disadvantages: a little harder to understand, fewer events to wait
>
>
>
> AFTER lsn_event [ WITHIN delay_milliseconds ] [, ...]
> BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]
>      [ AFTER lsn_event [ WITHIN delay_milliseconds ]]
> START [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]
>      [ AFTER lsn_event [ WITHIN delay_milliseconds ]]
>

+1 for the second one not only because it avoids new words in grammar
but also sounds to convey the meaning. I think you can explain in docs
how this feature can be used basically how will one get the correct
LSN value to specify.

As suggested previously also pick one of the approaches (I would
advocate the second one) and keep an option for the second one by
mentioning it in the commit message. I hope to see more
reviews/discussions or usage like how will users get the LSN value to
be specified on the core logic of the feature at this stage. IF
possible, state, how real-world applications could leverage this
feature.

-- 
With Regards,
Amit Kapila.

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