RE: Conflict detection for update_deleted in logical replication

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

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

To: shveta malik <shveta.malik@gmail.com>

Cc: Amit Kapila <amit.kapila16@gmail.com>, Nisha Moond <nisha.moond412@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, vignesh C <vignesh21@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>

Date: 2025-09-01T04:45:26Z

Lists: pgsql-hackers

Attachments

v69-0003-Add-a-dead_tuple_retention_active-column-in-pg_s.patch (application/octet-stream) patch v69-0003
v69-0001-Add-max_retention_duration-option-to-subscriptio.patch (application/octet-stream) patch v69-0001
v69-0002-Resume-retaining-the-information-for-conflict-de.patch (application/octet-stream) patch v69-0002

On Friday, August 29, 2025 6:28 PM shveta malik <shveta.malik@gmail.com>:
> 
> On Fri, Aug 29, 2025 at 11:49 AM Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
> wrote:
> >
> > Here is the new version patch set which also addressed Shveta's
> comments[1].
> >
> 
> Thanks for the patch.
> 
> On 001 alone, I’m observing a behavior where, if sub1 has stopped retention,
> and I then create a new subscription sub2, the worker for
> sub2 fails to start successfully. It repeatedly starts and exits, logging the
> following message:
> 
> LOG:  logical replication worker for subscription "sub2" will restart because
> the option retain_dead_tuples was enabled during startup
> 
> Same things happen when I disable and re-enable 'retain_dead_tuple' of any
> sub once the slot has invalid xmin.

I think this behavior is because slot.xmin is set to an invalid number, and 0001
patch has no slot recovery logic, so even if retentionactive is true, newly
created subscriptions cannot have a valid oldest_nonremovable_xid.

After thinking more, I decided to add slot recovery functionality to 0001 as
well, thus avoiding the need for additional checks here. I also adjusted
the documents accordingly.

Here is the V69 patch set which addressed above comments and the
latest comment from Nisha[1].

[1] https://www.postgresql.org/message-id/CABdArM7GBa8kXCdOQw4U--tKgapj5j0hAVzL%3D%3DB3-fkg8Gzmdg%40mail.gmail.com

Best Regards,
Hou zj

Commits

Fix intermittent BF failures in 035_conflicts.
- 0f42206531b3 19 (unreleased) landed
Resume conflict-relevant data retention automatically.
- 0d48d393d465 19 (unreleased) landed
Fix intermittent test failure introduced in 6456c6e2c4.
- 01d793698f59 19 (unreleased) landed
Fix Coverity issue reported in commit a850be2fe.
- 5ac3c1ac22cb 19 (unreleased) landed
Add test to prevent premature removal of conflict-relevant data.
- 6456c6e2c4ad 19 (unreleased) landed
Post-commit review fixes for 228c370868.
- 1f7e9ba3ac4e 19 (unreleased) landed
Add max_retention_duration option to subscriptions.
- a850be2fe653 19 (unreleased) landed
Detect and report update_deleted conflicts.
- fd5a1a0c3e56 19 (unreleased) landed
Preserve conflict-relevant data during logical replication.
- 228c37086855 19 (unreleased) landed
Integrate FullTransactionIds deeper into two-phase code
- 62a17a92833d 19 (unreleased) cited
Improve checks for GUC recovery_target_timeline
- fd7d7b719137 19 (unreleased) cited
Prevent excessive delays before launching new logrep workers.
- fd519419c948 18.0 cited
Keep WAL segments by slot's last saved restart LSN
- ca307d5cec90 18.0 cited
Rework some code handling pg_subscription data in psql and pg_dump
- 08691ea958c2 18.0 cited
Use generateClonedIndexStmt to propagate CREATE INDEX to partitions.
- 68dfecbef210 18.0 cited