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>, 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>, vignesh C <vignesh21@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2025-09-16T03:53:38Z
Lists: pgsql-hackers

Attachments

On Monday, September 15, 2025 8:11 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> 
> On Mon, Sep 15, 2025 at 1:07 PM Zhijie Hou (Fujitsu)
> <houzj.fnst@fujitsu.com> wrote:
> >
> > Thanks, the changes look good to me. I have merged them in V75 patch.
> >
> 
> Pushed. I see a BF which is not related with this commit but a previous commit
> for the work in this thread.
> 
> See LOGs [1]:
> regress_log_035_conflicts
> -----------------------------------
> [11:16:47.604](0.015s) not ok 24 - the deleted column is removed
> [11:16:47.605](0.002s) #   Failed test 'the deleted column is removed'
> #   at
> /home/bf/bf-build/kestrel/HEAD/pgsql/src/test/subscription/t/035_conflict
> s.pl
> line 562.
> 
> Then the corresponding subscriber LOG:
> 
> 025-09-15 11:16:47.600 CEST [1888170][client backend][1/13:0] INFO:
> vacuuming "postgres.public.tab"
> 2025-09-15 11:16:47.600 CEST [1888170][client backend][1/13:0] INFO:
> finished vacuuming "postgres.public.tab": index scans: 0
> pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
> tuples: 0 removed, 1 remain, 0 are dead but not yet removable tuples missed: 1
> dead from 1 pages not removed due to cleanup lock contention removable
> cutoff: 787, which was 0 XIDs old when operation ended ...
> 
> This indicates that the Vacuum is not able to remove the row even after the slot
> is advanced because some other background process holds a lock/pin on the
> page. I think that is possible because the page was dirty due to apply of update
> operation and bgwriter/checkpointer could try to write such a page.
> 
> I'll analyze more tomorrow and share if I have any new findings.

I agree with the analysis. I attempted to delete a tuple from a table and, while
executing VACUUM(verbose) on this table, I executed a checkpoint concurrently.
Using the debugger, I stoped in SyncOneBuffer() after acquiring the page block.
This allowed me to reproduce the same log where the deleted row could not be
removed:

--
tuples: 0 removed, 1 remain, 0 are dead but not yet removable
tuples missed: 1 dead from 1 pages not removed due to cleanup lock contention
--

I think we can remove the VACUUM for removing the deleted column. We have
already confirmed that the replication slot.xmin has advanced, which should be
sufficient to prove that the feature works correctly.

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.