逻辑复制中没有主键或非空+唯一约束存在的问题

背景

当我们在使用PostgreSQL的logical decoding时， 更新和删除数据涉及到行的定位，如果没有方法定位到唯一行，可能导致接收端重做时出问题。

PostgreSQL提供了几种区分OLD TUPLE的方式，使用unique index + not null , 或primary key都没有歧义。

但是使用full其实是有歧义的，目标端可能无法根据这条记录匹配到唯一的行。

http://www.postgresql.org/docs/9.5/static/sql-altertable.html

REPLICA IDENTITY  
    This form changes the information which is written to the write-ahead log to identify rows which are updated or deleted.   
    This option has no effect except when logical replication is in use.   
    DEFAULT (the default for non-system tables) records the old values of the columns of the primary key, if any.   
    USING INDEX records the old values of the columns covered by the named index, which must be unique, not partial, not deferrable, and include only columns marked NOT NULL.   
    FULL records the old values of all columns in the row.   
    NOTHING records no information about the old row. (This is the default for system tables.)   
    In all cases, no old values are logged unless at least one of the columns that would be logged differs between the old and new versions of the row.  

例子，

我们插入3条一样的记录，但是我们可以使用ctid来更新其中一条，这就造成了歧义。

postgres=# SELECT * FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');  
    slot_name    | xlog_position   
-----------------+---------------  
 regression_slot | 0/4C5AE48  
(1 row)  
postgres=# create table t1(id int, info text);  
CREATE TABLE  
postgres=# insert into t1 values (1,'test');  
INSERT 0 1  
postgres=# insert into t1 values (1,'test');  
INSERT 0 1  
postgres=# insert into t1 values (1,'test');  
INSERT 0 1  

默认是nothing，不记录OLD TUPLE，这肯定是不行的。

postgres=# update t1 set info='abc' where ctid='(0,1)';  
UPDATE 1  
postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);  
 location  | xid  |                           data                             
-----------+------+----------------------------------------------------------  
 0/4C5AEB0 | 9726 | BEGIN 9726  
 0/4C72D08 | 9726 | COMMIT 9726  
 0/4C72D08 | 9727 | BEGIN 9727  
 0/4C72D08 | 9727 | table public.t1: INSERT: id[integer]:1 info[text]:'test'  
 0/4C72D90 | 9727 | COMMIT 9727  
 0/4C72DC8 | 9728 | BEGIN 9728  
 0/4C72DC8 | 9728 | table public.t1: INSERT: id[integer]:1 info[text]:'test'  
 0/4C72E50 | 9728 | COMMIT 9728  
 0/4C72E50 | 9729 | BEGIN 9729  
 0/4C72E50 | 9729 | table public.t1: INSERT: id[integer]:1 info[text]:'test'  
 0/4C72ED8 | 9729 | COMMIT 9729  
 0/4C72F10 | 9730 | BEGIN 9730  
 0/4C72F10 | 9730 | table public.t1: UPDATE: id[integer]:1 info[text]:'abc'  -- 没有OLD TUPLE  
 0/4C72FA8 | 9730 | COMMIT 9730  
(14 rows)  

修改为FULL，但是依旧有歧义

postgres=# alter table t1 REPLICA IDENTITY full;  
ALTER TABLE  

我还是更新一条记录

postgres=# update t1 set info='abc' where ctid='(0,2)';  
UPDATE 1  

获取到了OLD TUPLE，但是拿这个去回放这条SQL的话，会对所有的重复记录做更新，而实际上在上游我根据CTID只更新了一条记录。

postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);  
 location  | xid  |                                                    data                                                       
-----------+------+-------------------------------------------------------------------------------------------------------------  
 0/4C73050 | 9731 | BEGIN 9731  
 0/4C731B0 | 9731 | COMMIT 9731  
 0/4C731E8 | 9732 | BEGIN 9732  
 0/4C731E8 | 9732 | table public.t1: UPDATE: old-key: id[integer]:1 info[text]:'test' new-tuple: id[integer]:1 info[text]:'abc'  
 0/4C73290 | 9732 | COMMIT 9732  
(5 rows)  
  
postgres=# select * from t1;  
 id | info   
----+------  
  1 | test  
  1 | abc  
  1 | abc  
(3 rows)  

我们在使用逻辑复制时，最好默认加上主键或者唯一值+非空的列。

MySQL隐含主键就是干这个的，基于ROW的复制。

digoal’s 大量PostgreSQL文章入口