Re: SSI: ON CONFLICT DO SELECT takes no predicate lock on the returned row

On Sat, 12 Sept 2026 at 19:01, Andrey Borodin <x4mmm(at)yandex-team(dot)ru> wrote:
>
> On 12 Sep 2026, Dean Rasheed wrote:
> > possible to fix this without a potentially expensive re-fetch.
>
> Unlike v4, v5 also takes SIREAD locks on index pages, which can cause
> extra false positives. The additional retries could outweigh saving
> the fetch.
>

Yes, I've been wondering about that. My first thought was that this is
necessary to prevent phantom reads, but after thinking about it some
more, I'm pretty sure that it's not necessary, and it's sufficient to
just lock the heap tuple, because of the way IOC works.

> Also, violationOK includes preliminary checks of deferred exclusion
> constraints, so v5 adds predicate locking there at SERIALIZABLE too.
> Is that needed? Could we restrict the new snapshot to arbiter probes?
>

Yeah, I dont' think it's needed. v4 also takes a predicate lock for
deferred exclusion constraints, but obviously only in the case where a
potential conflict is found, whereas v5 is doing it even in cases
where there is no conflict.

So unless anyone thinks otherwise, v4 is probably better than v5, and
I'll push that.

Regards,
Dean