RE: Conflict Detection and Resolution

Hi,

I researched about how to detect the resolve update_deleted and thought
about one idea: which is to maintain the xmin in logical slot to preserve
the dead row and support latest_timestamp_xmin resolution for
update_deleted to maintain data consistency.

Here are details of the xmin idea and resolution of update_deleted:

1. how to preserve the dead row so that we can detect update_delete
conflict correctly. (In the following explanation, let's assume there is a
a multimeter setup with node A, B).

To preserve the dead row on node A, I think we could maintain the "xmin"
in the logical replication slot on Node A to prevent the VACCUM from
removing the dead row in user table. The walsender that acquires the slot
is responsible to advance the xmin. (Node that I am trying to explore
xmin idea as it could be more efficient than using commit_timestamp, and the
logic could be simpler as we are already maintaining catalog_xmin in
logical slot and xmin in physical slot)

- Strategy for advancing xmin:

The xmin can be advanced if a) a transaction (xid:1000) has been flushed
to the remote node (Node B in this case). *AND* b) On Node B, the local
transactions that happened before applying the remote
transaction(xid:1000) were also sent and flushed to the Node A.

- The implementation:

condition a) can be achieved with existing codes, the walsender can
advance the xmin similar to the catalog_xmin.

For condition b), we can add a subscription option (say 'feedback_slot').
The feedback_slot indicates the replication slot that will send changes to
the origin (On Node B, the slot should be subBA). The apply worker will
check the status(confirmed flush lsn) of the 'feedback slot' and send
feedback to the walsender about the WAL position that has been sent and
flushed via the feedback_slot.

For example, on Node B, we specify the replication slot (subBA) that is
sending changes to Node A. The apply worker on Node B will send
feedback(WAL position that has been sent to the Node A) to Node A
regularly. Then the Node A can use the position to advance the xmin.
(Similar to the hot_standby_feedback).

2. The resolution for update_delete

The current design doesn't support 'last_timestamp_win'. But this could be
a problem if update_deleted is detected due to some very old dead row.
Assume the update has the latest timestamp, and if we skip the update due
to these very old dead rows, the data would be inconsistent because the
latest update data is missing.

The ideal resolution should compare the timestamp of the UPDATE and the
timestamp of the transaction that produced these dead rows. If the UPDATE
is newer, the convert the UDPATE to INSERT, otherwise, skip the UPDATE.

Best Regards,
Hou zj