In logical replication concurrent update of partition key creates aduplicate record on standby. - Mailing list pgsql-hackers

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-k4ErxWTpz5sAgFJ4aUYMx0khfySvANmWRzsag@mail.gmail.com

Regards,
Amul  Sul