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 <zsolt.parragi@percona.com>
Author: Andrey Borodin <x4mmm@yandex-team.ru>
Reported-by: Andrey Borodin <x4mmm@yandex-team.ru>
Reported-by: Zsolt Parragi <zsolt.parragi@percona.com>
Discussion: https://postgr.es/m/787936C5-4155-4CF9-939D-39DC0EC1C892@yandex-team.ru
Discussion: https://postgr.es/m/CAN4CZFM1GkHJkpMeo4G5rxtacVsfeKCJYiik9E9AKX1E9VYQ1w@mail.gmail.com
Backpatch-through: 14
Branch
------
REL_18_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/6567f3f1f21353b5b0e9538d058998ab76c5a37c
Modified Files
--------------
src/backend/executor/execIndexing.c | 20 +++++++++
.../expected/insert-conflict-serializable.out | 44 +++++++++++++++++++
src/test/isolation/isolation_schedule | 1 +
.../specs/insert-conflict-serializable.spec | 49 +++++++++++++++++++++
src/test/modules/injection_points/Makefile | 3 +-
.../expected/on_conflict_probe_window.out | 35 +++++++++++++++
src/test/modules/injection_points/meson.build | 1 +
.../specs/on_conflict_probe_window.spec | 51 ++++++++++++++++++++++
8 files changed, 203 insertions(+), 1 deletion(-)
