RE: Conflict detection for update_deleted in logical replication

On Wed, Jul 2, 2025 at 3:28 PM Hou, Zhijie wrote:
> Kindly use the latest patch set for performance testing.

During testing, we observed a limitation in cascading logical replication
setups, such as (A -> B -> C). When retain_conflict_info is enabled on Node C,
it may not retain information necessary for conflict detection when applying
changes originally replicated from Node A. This happens because Node C only
waits for locally originated changes on Node B to be applied before advancing
the non-removable transaction ID.

For example, Consider a logical replication setup as mentioned above : A -> B -> C.
- All three nodes have a table t1 with two tuples (1,1) (2,2).
- Node B subscribed to all changes of t1 from Node A
- Node-C subscribed to all changes from Node B.
- Subscriptions use the default origin=ANY, as this is not a bidirectional
setup.

Now, consider two concurrent operations:
- @9:00 Node A - UPDATE (1,1) -> (1,11)

- @9:02 Node C - DELETE (1,1)

Assume a slight delay at Node B before it applies the update from Node A.

@9:03 Node C - advances the non-removable XID because it sees no concurrent
transactions from Node B. It is unaware of Node A’s concurrent update.

@9:04 Node B - receives Node A's UPDATE and applies (1,1) -> (1,11)
t1 has tuples : (1,11), (2,2)

@9:05 Node C - receives the UPDATE (1,1) -> (1,11)
- As conflict slot’s xmin is advanced, the deleted tuple may already have
been removed.
- Conflict resolution fails to detect update_deleted and instead raises
update_missing.

Note that, as per decoding logic Node C sees the commit timestamp of the update
as 9:00 (origin commit_ts from Node A), not 9:04 (commit time on Node B). In
this case, since the UPDATE's timestamp is earlier than the DELETE, Node C
should ideally detect an update_deleted conflict. However, it cannot, because
it no longer retains the deleted tuple.

Even if Node C attempts to retrieve the latest WAL position from Node A, Node C
doesn't maintain any LSN which we could use to compare with it.

This scenario is similar to another restriction in the patch where
retain_conflict_info is not supported if the publisher is also a physical
standby, as the required transaction information from the original primary is
unavailable. Moreover, this limitation is relevant only when the subscription
origin option is set to ANY, as only in that case changes from other origins
can be replicated. Since retain_conflict_info is primarily useful for conflict
detection in bidirectional clusters where the origin option is set to NONE,
this limitation appears acceptable.

Given these findings, to help users avoid unintended configurations, we plan to
issue a warning in scenarios where replicated changes may include origins other
than the direct publisher, similar to the existing checks in the
check_publications_origin() function.

Here is the latest patch that implements the warning and documents
this case. Only 0001 is modified for this.

A big thanks to Nisha for invaluable assistance in identifying this
case and preparing the analysis for it.

Best Regards,
Hou zj