digoal
2024-04-05
PostgreSQL , PolarDB , DuckDB , 读写一致性 , pg_wal_replay_wait
通过pg_wal_replay_wait()支持读写分离pool实现跨实例的读写一致性
假设一个场景:
- 客户端通过读写分离pool连接rw,ro节点.
- 主节点写入数据并提交, 随即发起的查询路由到了只读节点, 如果要读到主节点产生的写操作, 需要等待只读节点apply完写节点产生的wal.
PostgreSQL 17提供了一个存储过程pg_wal_replay_wait ( target_lsn pg_lsn, timeout bigint DEFAULT 0) → void 用于等待一个LSN.
Implement pg_wal_replay_wait() stored procedure
author Alexander Korotkov <akorotkov@postgresql.org>
Tue, 2 Apr 2024 19:48:03 +0000 (22:48 +0300)
committer Alexander Korotkov <akorotkov@postgresql.org>
Tue, 2 Apr 2024 19:48:03 +0000 (22:48 +0300)
commit 06c418e163e913966e17cb2d3fb1c5f8a8d58308
tree 10945dd3132f4b262d416ce55c88b7955dc67537 tree
parent 6faca9ae2878c8f642a2e5748d2dbb2b91341bec commit | diff
Implement pg_wal_replay_wait() stored procedure
pg_wal_replay_wait() is to be used on standby and specifies waiting for
the specific WAL location to be replayed before starting the transaction.
This option is useful when the user makes some data changes on primary and
needs a guarantee to see these changes on standby.
The queue of waiters is stored in the shared memory array sorted by LSN.
During replay of WAL waiters whose LSNs are already replayed are deleted from
the shared memory array and woken up by setting of their latches.
pg_wal_replay_wait() needs to wait without any snapshot held. Otherwise,
the snapshot could prevent the replay of WAL records implying a kind of
self-deadlock. This is why it is only possible to implement
pg_wal_replay_wait() as a procedure working in a non-atomic context,
not a function.
Catversion is bumped.
Discussion: https://postgr.es/m/eb12f9b03851bb2583adab5df9579b4b%40postgrespro.ru
Author: Kartyshov Ivan, Alexander Korotkov
Reviewed-by: Michael Paquier, Peter Eisentraut, Dilip Kumar, Amit Kapila
Reviewed-by: Alexander Lakhin, Bharath Rupireddy, Euler Taveira
+CREATE OR REPLACE PROCEDURE pg_wal_replay_wait(target_lsn pg_lsn, timeout int8 DEFAULT 0)
+ LANGUAGE internal AS 'pg_wal_replay_wait';
接口如下
pg_wal_replay_wait ( target_lsn pg_lsn, timeout bigint DEFAULT 0) → void
If timeout is not specified or zero, this procedure returns once WAL is replayed upto target_lsn. If the timeout is specified (in milliseconds) and greater than zero, the procedure waits until the server actually replays the WAL upto target_lsn or until the given time has passed. On timeout, an error is emitted.
手册位置
pg_wal_replay_wait waits till target_lsn to be replayed on standby. That is, after this function execution, the value returned by pg_last_wal_replay_lsn should be greater or equal to the target_lsn value. This is useful to achieve read-your-writes-consistency, while using async replica for reads and primary for writes. In that case lsn of the last modification should be stored on the client application side or the connection pooler side.
You can use pg_wal_replay_wait to wait for the pg_lsn value. For example, an application could update the movie table and get the lsn after changes just made. This example uses pg_current_wal_insert_lsn on primary server to get the lsn given that synchronous_commit could be set to off.
postgres=# UPDATE movie SET genre = 'Dramatic' WHERE genre = 'Drama';
UPDATE 100
postgres=# SELECT pg_current_wal_insert_lsn();
pg_current_wal_insert_lsn
--------------------
0/306EE20
(1 row)
Then an application could run pg_wal_replay_wait with the lsn obtained from primary. After that the changes made of primary should be guaranteed to be visible on replica.
postgres=# CALL pg_wal_replay_wait('0/306EE20');
CALL
postgres=# SELECT * FROM movie WHERE genre = 'Drama';
genre
-------
(0 rows)
It may also happen that target lsn is not achieved within the timeout. In that case the error is thrown.
postgres=# CALL pg_wal_replay_wait('0/306EE20', 100);
ERROR: timed out while waiting for target LSN 0/306EE20 to be replayed; current replay LSN 0/306EA60
pg_wal_replay_wait can't be used within the transaction, another procedure or function. Any of them holds a snapshot, which could prevent the replay of WAL records.
postgres=# BEGIN;
BEGIN
postgres=*# CALL pg_wal_replay_wait('0/306EE20');
ERROR: pg_wal_replay_wait() must be only called in non-atomic context
DETAIL: Make sure pg_wal_replay_wait() isn't called within a transaction, another procedure, or a function.
