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

On Thu, 3 Sept 2026 at 14:31, Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
>
> Adding Dean Rasheed to the thread, since he committed this feature. Please
> note that this is marked as an open item for v19.
>

I started looking at this, and it looks to me like another
manifestation of the pre-existing bug with ON CONFLICT DO NOTHING / DO
UPDATE reported in [1].

I tried out the ON CONFLICT DO NOTHING case, using the test case added
in [1], which defines a table as follows, initially containing 2 rows:

CREATE TABLE noc (k int PRIMARY KEY, v int);
INSERT INTO noc VALUES (1, 0), (2, 0);

and then 2 transactions are executed concurrently in 2 sessions:

S1: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
S2: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
S1: INSERT INTO noc(k, v) VALUES (1, 99) ON CONFLICT (k) DO NOTHING;
S2: SELECT v FROM noc WHERE k = 2;
S1: UPDATE noc SET v = 1 WHERE k = 2;
S2: DELETE FROM noc WHERE k = 1;
S1: COMMIT;
S2: COMMIT;

Running this on HEAD, the SELECT returns 0, both transactions commit
successfully, and the table ends up containing just 1 row, with k = 2
and v = 1. That result isn't consistent with either ordering of those
2 transactions, so I concur that this is a genuine SSI bug -- one of
the transactions should have been aborted.

Given that, I think the best approach would be to fix this closer to
the underlying cause, which I think is the absence of an SIREAD lock
when probing the arbiter index, rather than adopting the fix suggested
in this thread, which only fixes the ON CONFLICT DO SELECT case.

I tested the fix from [1], and can confirm that it fixes the ON
CONFLICT DO SELECT bug as well as the ON CONFLICT DO NOTHING / DO
UPDATE bugs. However, that fix seems to have the same kind of layering
violation that Andres complained about for the first version of the
patch on this thread -- predicate locking should be in heapam code,
not in execIndexing.c.

So perhaps what we need to do (borrowing elements from both patches)
is re-fetch the existing tuple using table_tuple_fetch_row_version()
with estate->es_snapshot in check_exclusion_or_unique_constraint(), so
that we take an SIREAD lock regardless of what conflict action is
executed.

That's my initial take, anyway. I haven't tried that yet.

Regards,
Dean

[1] https://www.postgresql.org/message-id/787936C5-4155-4CF9-939D-39DC0EC1C892%40yandex-team.ru