Fix missing SIREAD lock on the row found by ON CONFLICT. INSERT ... ON CONFLICT decides what to do based on the conflicting row found by the arbiter index probe, but SSI never saw that read: the probe runs with a dirty snapshot, which predicate locking ignores, and the later fetch of the row uses SnapshotAny. When the statement then writes nothing, as with DO NOTHING, DO UPDATE with a WHERE clause rejecting the row, or DO SELECT, nothing records the read at all. A concurrent writer of that row went unnoticed and write skew could commit at SERIALIZABLE, even though the same schedule with a plain SELECT of the row fails with a serialization error.
To fix, read the conflicting tuple again with the query snapshot, right where the probe finds it. The table AM takes the SIREAD lock and checks for a concurrent writer of the tuple as part of that read, both under the buffer lock, so a writer either sees the lock or is seen. A predicate lock by itself acquired separately after the probe could not offer that: a writer passing its conflict check in between would be missed. Doing this in the probe covers every conflict action, including rows that the WHERE clause of DO UPDATE or DO SELECT then rejects. The DO NOTHING and DO UPDATE cases have been broken since ON CONFLICT was added in 9.5; DO SELECT is new in v19. Backpatch to all supported branches. Author: Zsolt Parragi <[email protected]> Author: Andrey Borodin <[email protected]> Reported-by: Andrey Borodin <[email protected]> Reported-by: Zsolt Parragi <[email protected]> Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/can4czfm1gkhjkpmeo4g5rxtacvsfekcjyiik9e9akx1e9vy...@mail.gmail.com Backpatch-through: 14 Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/5b6b83cb787f0c7ef9379d99511087bb73ed8c67 Modified Files -------------- src/backend/executor/execIndexing.c | 16 +++++++ .../expected/insert-conflict-serializable.out | 44 +++++++++++++++++++ src/test/isolation/isolation_schedule | 1 + .../specs/insert-conflict-serializable.spec | 49 ++++++++++++++++++++++ 4 files changed, 110 insertions(+)
