Horton

Implement waiting for wal lsn replay: reloaded

Alexander Korotkov <aekorotkov@gmail.com>

View thread
From: Alexander Korotkov <aekorotkov@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-11-27T04:08:51Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Clean up 019_replslot_limit.pl comments

  2. Stabilize 019_replslot_limit.pl: wait on slot restart_lsn

  3. Use WAIT FOR LSN in PostgreSQL::Test::Cluster::wait_for_catchup()

  4. Add tab completion for the WAIT FOR LSN MODE option

  5. Add the MODE option to the WAIT FOR LSN command

  6. Extend xlogwait infrastructure with write and flush wait types

  7. Unify error messages

  8. Optimize shared memory usage for WaitLSNProcInfo

  9. Fix WaitLSNWakeup() fast-path check for InvalidXLogRecPtr

  10. Fix incorrect function name in comments

  11. Add infrastructure for efficient LSN waiting

  12. Add pairingheap_initialize() for shared memory usage

  13. Implement WAIT FOR command

Attachments

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