Re: [BUG?] check_exclusion_or_unique_constraint false negative

On Wed, Mar 12, 2025 at 6:36 AM Mihail Nikalayeu
<michail(dot)nikolaev(at)gmail(dot)com> wrote:
>
> 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
>

IIUC, the problem you are worried about can happen with DELETE+INSERT
in the same transaction on the subscriber, right? If so, this should
happen with DELETE and INSERT in a separate transaction as well. If
that happens then we anyway may not be able to detect such an INSERT
if it happens on a page earlier than the current page.

BTW, as the update (or DELETE+INSERT) happens at a later time than the
publisher's update/delete, so once we have the last_write_win
resolution strategy implemented, it is the subscriber operation that
will win. So, the current behavior shouldn't cause any problem.

--
With Regards,
Amit Kapila.