PostgreSQL 10.0 preview 功能增强 - slave支持WAITLSN ‘lsn’, time;用于设置安全replay栅栏
背景
PostgreSQL 的流复制是非常灵活的，在主库端，可以将事务的级别设置为同步，异步，本地同步，远程同步刷盘，远程同步刷OS缓存，远程数据库apply等多种级别。
《PostgreSQL 9.6 同步多副本 与 remote_apply事务同步级别》
但是在备库，还没有一个可以设置安全栅栏的手段，比如说，我们要让备库apply到某个位点后，再执行某个QUERY。
10.0提供了这样的接口，来完成这个功能。
waitlsn ‘lsn’, time;语法
例子：
session a:
连接到主节点，执行了某个QUERY，并提交，产生的LSN为lsn1位置。
session b:
连接到备节点，它如果要等待一致性的结果，需要等待lsn1在备节点已经apply，怎么做呢？
waitlsn 'lsn1';即可
详情
Hi hackers,
Few days earlier I've finished my work on WAITLSN statement utility, so
I’d like to share it.
Introduction
============
Our clients who deal with 9.5 and use asynchronous master-slave
replication, asked to make the wait-mechanism on the slave side to
prevent the situation when slave handles query which needs data (LSN)
that was received, flushed, but still not replayed.
Problem description
===================
The implementation:
Must handle the wait-mechanism using pg_sleep() in order not to load system
Must avoid race conditions if different backend want to wait for
different LSN
Must not take snapshot of DB, to avoid troubles with sudden minXID change
Must have optional timeout parameter if LSN traffic has stalled.
Must release on postmaster’s death or interrupts.
Implementation
==============
To avoid troubles with snapshots, WAITLSN was implemented as a utility
statement, this allows us to circumvent the snapshot-taking mechanism.
We tried different variants and the most effective way was to use Latches.
To handle interprocess interaction all Latches are stored in shared
memory and to cope with race conditions, each Latch is protected by a
Spinlock.
Timeout was made optional parameter, it is set in milliseconds.
What works
==========
Actually, it works well even with significant timeout or wait period
values, but of course there might be things I've overlooked.
How to use it
==========
WAITLSN ‘LSN’ [, timeout in ms];
#Wait until LSN 0/303EC60 will be replayed, or 10 second passed.
WAITLSN ‘0/303EC60’, 10000;
#Or same without timeout.
WAITLSN ‘0/303EC60’;
Notice: WAITLSN will release on PostmasterDeath or Interruption events
if they come earlier then LSN or timeout.
Testing the implementation
======================
The implementation was tested with testgres and unittest python modules.
How to test this implementation:
Start master server
Make table test, insert tuple 1
Make asynchronous slave replication (9.5 wal_level = standby, 9.6 or
higher wal_level = replica)
Slave: START TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
SELECT * FROM test;
Master: delete tuple + make vacuum + get new LSN
Slave: WAITLSN ‘newLSN’, 60000;
Waitlsn finished with FALSE “LSN doesn`t reached”
Slave: COMMIT;
WAITLSN ‘newLSN’, 60000;
Waitlsn finished with success (without NOTICE message)
The WAITLSN as expected wait LSN, and interrupts on PostmasterDeath,
interrupts or timeout.
Your feedback is welcome!
---
Ivan Kartyshov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
这个patch的讨论，详见邮件组，本文末尾URL。
PostgreSQL社区的作风非常严谨，一个patch可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，patch合并到master已经非常成熟，所以PostgreSQL的稳定性也是远近闻名的。
参考
https://commitfest.postgresql.org/13/772/
https://www.postgresql.org/message-id/flat/0240c26c-9f84-30ea-fca9-93ab2df5f305@postgrespro.ru#0240c26c-9f84-30ea-fca9-93ab2df5f305@postgrespro.ru