Re: [BUG?] check_exclusion_or_unique_constraint false negative

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

In response to

Responses

Browse pgsql-hackers by date

  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