Horton

Re: Conflict detection for update_deleted in logical replication

Amit Kapila <amit.kapila16@gmail.com>

View thread View on postgr.es
From: Amit Kapila <amit.kapila16@gmail.com>
To: "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.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-15T12:10:58Z
Lists: pgsql-hackers 
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_conflicts.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.


[1]: https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=kestrel&dt=2025-09-15%2009%3A08%3A07&stg=subscription-check

-- 
With Regards,
Amit Kapila.

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.