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

On Thu, 10 Sept 2026 at 11:24, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>
> I'll take
> another couple of days to think it over more thoroughly, before
> pushing it (probably at the weekend).
>

I've been thinking about this some more, and I think that it's
possible to fix this without a potentially expensive re-fetch.

The attached v5 patch modifies the predicate locking code so that it
takes predicate locks during the arbiter index probe, even though it's
still using a dirty snapshot, so the read is recorded during the
probe, as it would be for an MVCC index scan.

It does this using a new special snapshot, SnapshotDirtySerializable,
which is a dirty snapshot, obeying the same tuple visibility rules,
but is special-cased for predicate locking. Technically, this is not
properly re-entrant safe, since it's a global datastructure, but I
think if user-defined operators or index expressions are recursively
calling INSERT ... ON CONFLICT here, then there are bigger problems
than getting incorrect serialization results.

This approach eliminates any window between probe and re-fetch (there
is no re-fetch), so there's no need to worry about that gap, which
makes the patch simpler, while still passing all the other tests.

Regards,
Dean