Horton

RE: Conflict detection for update_deleted in logical replication

Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>

View thread View on postgr.es
From: "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>, vignesh C <vignesh21@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Nisha Moond <nisha.moond412@gmail.com>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2025-07-04T11:18:38Z
Lists: pgsql-hackers

Attachments

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

Commits

  1. Fix intermittent BF failures in 035_conflicts.

  2. Resume conflict-relevant data retention automatically.

  3. Fix intermittent test failure introduced in 6456c6e2c4.

  4. Fix Coverity issue reported in commit a850be2fe.

  5. Add test to prevent premature removal of conflict-relevant data.

  6. Post-commit review fixes for 228c370868.

  7. Add max_retention_duration option to subscriptions.

  8. Detect and report update_deleted conflicts.

  9. Preserve conflict-relevant data during logical replication.

  10. Integrate FullTransactionIds deeper into two-phase code

  11. Improve checks for GUC recovery_target_timeline

  12. Prevent excessive delays before launching new logrep workers.

  13. Keep WAL segments by slot's last saved restart LSN

  14. Rework some code handling pg_subscription data in psql and pg_dump

  15. Use generateClonedIndexStmt to propagate CREATE INDEX to partitions.