PostgreSQL 15 preview - 支持 ALTER SUBSCRIPTION ... SKIP (lsn = XXX) 语法, 跳过逻辑复制发送冲突的事务

作者

digoal

日期

2022-03-22

标签

PostgreSQL , 逻辑订阅 , alter subscription skip , 约束错误 , 跳过逻辑错误

《PostgreSQL 15 preview - 逻辑复制、逻辑订阅增加错误信息errcontext(包含LSN). 结合pg_replication_origin_advance跳过冲突的wal回放》

现在支持ALTER SUBSCRIPTION ... SKIP语法直接跳过LSN了.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=208c5d65bbd60e33e272964578cb74182ac726a8

Add ALTER SUBSCRIPTION ... SKIP.  
author  Amit Kapila <akapila@postgresql.org>      
Tue, 22 Mar 2022 01:41:19 +0000 (07:11 +0530)  
committer   Amit Kapila <akapila@postgresql.org>      
Tue, 22 Mar 2022 01:41:19 +0000 (07:11 +0530)  
commit  208c5d65bbd60e33e272964578cb74182ac726a8  
tree    5f3a99783f4c7be35c16237c5b10ebc711b37293    tree  
parent  315ae75e9b6da72456eaa44e55ace9ab1b95ef74    commit | diff  
Add ALTER SUBSCRIPTION ... SKIP.  
This feature allows skipping the transaction on subscriber nodes.  
If incoming change violates any constraint, logical replication stops  
until it's resolved. Currently, users need to either manually resolve the  
conflict by updating a subscriber-side database or by using function  
pg_replication_origin_advance() to skip the conflicting transaction. This  
commit introduces a simpler way to skip the conflicting transactions.  
The user can specify LSN by ALTER SUBSCRIPTION ... SKIP (lsn = XXX),  
which allows the apply worker to skip the transaction finished at  
specified LSN. The apply worker skips all data modification changes within  
the transaction.  
Author: Masahiko Sawada  
Reviewed-by: Takamichi Osumi, Hou Zhijie, Peter Eisentraut, Amit Kapila, Shi Yu, Vignesh C, Greg Nancarrow, Haiying Tang, Euler Taveira  
Discussion: https://postgr.es/m/CAD21AoDeScrsHhLyEPYqN3sydg6PxAPVBboK=30xJfUVihNZDA@mail.gmail.com  
复制

参考手册：
https://www.postgresql.org/docs/devel/sql-altersubscription.html

SKIP ( skip_option = value )  
Skips applying all changes of the remote transaction. If incoming data violates any constraints, logical replication will stop until it is resolved. By using ALTER SUBSCRIPTION ... SKIP command, the logical replication worker skips all data modification changes within the transaction. This option has no effect on the transactions that are already prepared by enabling two_phase on subscriber. After logical replication worker successfully skips the transaction or finishes a transaction, LSN (stored in pg_subscription.subskiplsn) is cleared. See Section 31.3 for the details of logical replication conflicts. Using this command requires superuser privilege.  
skip_option specifies options for this operation. The supported option is:  
lsn (pg_lsn)  
Specifies the finish LSN of the remote transaction whose changes are to be skipped by the logical replication worker. The finish LSN is the LSN at which the transaction is either committed or prepared. Skipping individual subtransaction is not supported. Setting NONE resets the LSN.  
复制

https://www.postgresql.org/docs/devel/logical-replication-conflicts.html

ERROR:  duplicate key value violates unique constraint "test_pkey"  
DETAIL:  Key (c)=(1) already exists.  
CONTEXT:  processing remote data for replication origin "pg_16395" during "INSERT" for replication target relation "public.test" in transaction 725 finished at 0/14C0378  
The LSN of the transaction that contains the change violating the constraint and the replication origin name can be found from the server log (LSN 0/14C0378 and replication origin pg_16395 in the above case). The transaction that produces conflict can be skipped by using ALTER SUBSCRIPTION ... SKIP with the finish LSN (i.e., LSN 0/14C0378). The finish LSN could be an LSN at which the transaction is committed or prepared on the publisher. Alternatively, the transaction can also be skipped by calling the pg_replication_origin_advance() function transaction. Before using this function, the subscription needs to be disabled temporarily either by ALTER SUBSCRIPTION ... DISABLE or, the subscription can be used with the disable_on_error option. Then, you can use pg_replication_origin_advance() function with the node_name (i.e., pg_16395) and the next LSN of the finish LSN (i.e., 0/14C0379). The current position of origins can be seen in the pg_replication_origin_status system view. Please note that skipping the whole transaction include skipping changes that might not violate any constraint. This can easily make the subscriber inconsistent.  
复制

注意事项:
- 跳过整个事务, 包括事务中之前已经apply的未引发冲突的SQL.

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.