On Thu, 3 Sept 2026 at 14:31, Nathan Bossart <[email protected]> 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
