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
v1-0001-Implement-WAIT-FOR-command.patch
Description: Binary data
