RE: Conflict Detection and Resolution

On Monday, July 8, 2024 12:32 PM Zhijie Hou (Fujitsu) <houzj(dot)fnst(at)fujitsu(dot)com> wrote:
>
> 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.

The above are some initial thoughts of how to preserve the dead row for
update_deleted conflict detection.

After thinking more, I have identified a few additional cases that I
missed to analyze regarding the design. One aspect that needs more
thoughts is the possibility of multiple slots on each node. In this
scenario, the 'feedback_slot' subscription option would need to be
structured as a list. However, requiring users to specify all the slots
may not be user-friendly. I will explore if this process can be
automated.

In addition, I will think more about the potential impact of re-using the
existing 'xmin' of the slot which may affect existing logic that relies on
'xmin'.

I will analyze more and reply about these points.

Best Regards,
Hou zj