Re: Implement waiting for wal lsn replay: reloaded

On Wed, 27 Nov 2024 at 09:09, Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
>
> Hi!
>
> Introduction
>
> The simple way to wait for a given lsn to replay on standby appears to
> be useful because it provides a way to achieve read-your-writes
> consistency while working with both replication leader and standby.
> And it's both handy and cheaper to have built-in functionality for
> that instead of polling pg_last_wal_replay_lsn().
>
> Key problem
>
> While this feature generally looks trivial, there is a surprisingly
> hard problem. While waiting for an LSN to replay, you should hold any
> snapshots. If you hold a snapshot on standby, that snapshot could
> prevent the replay of WAL records. In turn, that could prevent the
> wait to finish, causing a kind of deadlock. Therefore, waiting for
> LSN to replay couldn't be implemented as a function. My last attempt
> implements this functionality as a stored procedure [1]. This
> approach generally works but has a couple of serious limitations.
> 1) Given that a CALL statement has to lookup a catalog for the stored
> procedure, we can't work inside a transaction of REPEATABLE READ or a
> higher isolation level (even if nothing has been done before in that
> transaction). It is especially unpleasant that this limitation covers
> the case of the implicit transaction when
> default_transaction_isolation = 'repeatable read' [2]. I had a
> workaround for that [3], but it looks a bit awkward.
> 2) Using output parameters for a stored procedure causes an extra
> snapshot to be held. And that snapshot is difficult (unsafe?) to
> release [3].
>
> Present solution
>
> The present patch implements a new utility command WAIT FOR LSN
> 'target_lsn' [, TIMEOUT 'timeout'][, THROW 'throw']. Unlike previous
> attempts to implement custom syntax, it uses only one extra unreserved
> keyword. The parameters are implemented as generic_option_list.
>
> Custom syntax eliminates the problem of running within an empty
> transaction of REPEATABLE READ level or higher. We don't need to
> lookup a system catalog. Thus, we have to set a transaction snapshot.
>
> Also, revising PlannedStmtRequiresSnapshot() allows us to avoid
> holding a snapshot to return a value. Therefore, the WAIT command in
> the attached patch returns its result status.
>
> Also, the attached patch explicitly checks if the standby has been
> promoted to throw the most relevant form of an error. The issue of
> inaccurate error messages has been previously spotted in [5].
>
> Any comments?
>
> Links.
> 1. https://www.postgresql.org/message-id/E1sZwuz-002NPQ-Lc%40gemulon.postgresql.org
> 2. https://www.postgresql.org/message-id/14de8671-e328-4c3e-b136-664f6f13a39f%40iki.fi
> 3. https://www.postgresql.org/message-id/CAPpHfdvRmTzGJw5rQdSMkTxUPZkjwtbQ%3DLJE2u9Jqh9gFXHpmg%40mail.gmail.com
> 4. https://www.postgresql.org/message-id/4953563546cb8c8851f84c7debf723ef%40postgrespro.ru
> 5. https://www.postgresql.org/message-id/ab0eddce-06d4-4db2-87ce-46fa2427806c%40iki.fi
>
> ------
> Regards,
> Alexander Korotkov
> Supabase
Hi!

What's the current status of
https://commitfest.postgresql.org/50/5167/ ? Should we close it or
reattach to this thread?

--
Best regards,
Kirill Reshke