| From: | Mihail Nikalayeu <michail(dot)nikolaev(at)gmail(dot)com> |
|---|---|
| To: | "Zhijie Hou (Fujitsu)" <houzj(dot)fnst(at)fujitsu(dot)com>, Peter Geoghegan <pg(at)bowt(dot)ie> |
| Cc: | Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Andres Freund <andres(at)anarazel(dot)de> |
| Subject: | Re: [BUG?] check_exclusion_or_unique_constraint false negative |
| Date: | 2025-03-12 01:05:50 |
| Message-ID: | CADzfLwWuXh8KO=OZvB71pZnQ8nH0NYXfuGbFU6FBiVZUbmuFGg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hello, everyone and Peter!
Peter, I have added you because you may be interested in (or already know
about) this btree-related issue.
Short description of the problem:
I noticed a concurrency issue in btree index scans that affects
SnapshotDirty and SnapshotSelf scan types.
When using these non-MVCC snapshot types, a scan could miss tuples if
concurrent transactions delete existing tuples and insert new one with
different TIDs on the same page.
The problem occurs because:
1. The scan reads a page and caches its tuples in backend-local storage
2. A concurrent transaction deletes a tuple and inserts a new one with a
different TID
3. The scan misses the new tuple because it was already deleted by a
committed transaction and does not pass visibility check
4. But new version on the page is missed, because not in cached tuples
This may cause issues with:
- logical replication (RelationFindReplTupleByIndex fail) - invalid
conflict message (MISSING instead of ORIGIN_DIFFERS), probably other issues
with upcoming conflict resolution for logical replication
- check_exclusion_or_unique_constraint false negative (but currently it
does not cause any real issues as far as I can see)
The fix implemented in this version of the patch:
- Retains the read lock on a page for SnapshotDirty and SnapshotSelf
scans until we're completely done with all tuples from that page
- Introduces a new 'extra_unlock' field in BTScanPos to track when a lock
is being held longer than usual
- Updates documentation to explain this special locking behavior
Yes, it may cause some degradation in performance because of that
additional lock.
Another possible idea is to use a fresh MVCC snapshot for such cases (but I
think it is still better to fix or at least document that issue anyway).
Best regards,
Mikhail.
>
| Attachment | Content-Type | Size |
|---|---|---|
| v5-0002-Fix-btree-index-scan-concurrency-issues-with-dirt.patch | application/octet-stream | 9.7 KB |
| v5-0001-Add-isolation-test-to-reproduce-dirty-snapshot-sc.patch | application/octet-stream | 6.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Noah Misch | 2025-03-12 01:23:15 | Re: Back-patch of: avoid multiple hard links to same WAL file after a crash |
| Previous Message | Peter Smith | 2025-03-12 01:02:35 | Re: Conflict detection for multiple_unique_conflicts in logical replication |