Re: [BUG?] check_exclusion_or_unique_constraint false negative

Amit Kapila <amit.kapila16@gmail.com>:


> What if the new insert happens in a page prior to the current page? I
> mean that the scan won't encounter the page where Insert happens.

Hmm.... Yes - if the TID lands to the page left of the current
position, we’ll miss it as well.
A lock‑based solution (version in the v10) would require keeping all
pages with the same key under a read lock, which feels too expensive.

> BTW, do we know the reason behind using SnapshotDirty in the first
> place? I don't see any comments in the nearby code unless I am missing
> something.

I think this is simply an attempt to lock the newest version of the
logical tuple, including INSERT cases.
For an existing tuple, the same can be achieved using MVCC snapshot + retry.
However, in the case of a not-yet-committed INSERT, a different type
of snapshot is required.

But I'm not sure if it provides any advantages.