Hi, If an update of partition key involves tuple movement from one partition to another partition then there will be a separate delete on one partition and insert on the other partition made.
In the logical replication if an update performed on the master and standby at the same moment, then replication worker tries to replicate delete + insert operation on standby. While replying master changes on standby for the delete operation worker will log "concurrent update, retrying" message (because the update on standby has already deleted) and move forward to reply the next insert operation. Standby update also did the same delete+insert is as part of the update of partition key in a result there will be two records inserted on standby. Here is the quick demonstration: == NODE 1 == postgres=# insert into foo values(1, 'initial insert'); INSERT 0 1 postgres=# select tableoid::regclass, * from foo; tableoid | a | b ----------+---+---------------- foo1 | 1 | initial insert (1 row) == NODE 2 == postgres=# select tableoid::regclass, * from foo; tableoid | a | b ----------+---+---------------- foo1 | 1 | initial insert (1 row) -- Now attach GDB to the replication worker & break on heap_lock_tuple() call == NODE 1 == postgres=# update foo set a=2, b='node1_update' where a=1; UPDATE 1 <---- replication worker hits break point on heap_lock_tuple() ---> == NODE 2 == postgres=# update foo set a=2, b='node2_update' where a=1; UPDATE 1 <---- continue replication worker ---> postgres=# 2018-02-07 13:32:46.307 IST [81613] LOG: concurrent update, retrying == NODE 1 == postgres=# select tableoid::regclass, * from foo; tableoid | a | b ----------+---+-------------- foo2 | 2 | node1_update (1 row) == NODE 2 == postgres=# select tableoid::regclass, * from foo; tableoid | a | b ----------+---+-------------- foo2 | 2 | node2_update foo2 | 2 | node1_update (2 rows) === Script to create partitioned table, publication & subscription == -- node1 CREATE TABLE foo (a int2, b text) PARTITION BY LIST (a); CREATE TABLE foo1 PARTITION OF foo FOR VALUES IN (1); CREATE TABLE foo2 PARTITION OF foo FOR VALUES IN (2); CREATE PUBLICATION update_row_mov_pub FOR ALL TABLES; ALTER TABLE foo REPLICA IDENTITY FULL; ALTER TABLE foo1 REPLICA IDENTITY FULL; ALTER TABLE foo2 REPLICA IDENTITY FULL; -- node2 CREATE TABLE foo (a int2, b text) PARTITION BY LIST (a); CREATE TABLE foo1 PARTITION OF foo FOR VALUES IN (1); CREATE TABLE foo2 PARTITION OF foo FOR VALUES IN (2); CREATE SUBSCRIPTION update_row_mov_sub CONNECTION 'host=localhost dbname=postgres' PUBLICATION update_row_mov_pub; == END == Here is a link of previous discussion : https://postgr.es/m/caaj_b97w_ggv-k4erxwtpz5sagfj4auymx0khfysvanmwrz...@mail.gmail.com Regards, Amul Sul
