agora inbox for pgsql-committers@postgresql.org
help / color / mirror / Atom feedFrom: Dean Rasheed <dean.a.rasheed@gmail.com>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT.
Date: Tue, 15 Sep 2026 10:51:30 +0000
Message-ID: <E1x6Ql8-00000000QhM-2bTv@gemulon.postgresql.org> (raw)
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
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/fb60892f4034017e77b8d4376d3ca8937dcbbab2
Modified Files
--------------
src/backend/executor/execIndexing.c | 19 ++++
.../expected/insert-conflict-serializable.out | 116 +++++++++++++++++++++
src/test/isolation/isolation_schedule | 1 +
.../specs/insert-conflict-serializable.spec | 71 +++++++++++++
src/test/modules/injection_points/Makefile | 3 +-
.../expected/on_conflict_probe_window.out | 113 ++++++++++++++++++++
src/test/modules/injection_points/meson.build | 1 +
.../specs/on_conflict_probe_window.spec | 57 ++++++++++
8 files changed, 380 insertions(+), 1 deletion(-)
view thread (7+ messages) latest in thread
Message-ID: <E1x6Ql8-00000000QhM-2bTv@gemulon.postgresql.org>
Permalink: ../E1x6Ql8-00000000QhM-2bTv@gemulon.postgresql.org/
Also on: postgresql.org/message-id/E1x6Ql8-00000000QhM-2bTv@gemulon.postgresql.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: dean.a.rasheed@gmail.com, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT.
In-Reply-To: <E1x6Ql8-00000000QhM-2bTv@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox