agora inbox for pgsql-committers@postgresql.orghelp / color / mirror / Atom feed
pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. 7+ messages / 1 participants [nested] [flat]
* pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. @ 2026-09-15 10:51 Dean Rasheed <dean.a.rasheed@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: Dean Rasheed @ 2026-09-15 10:51 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. @ 2026-09-15 10:51 Dean Rasheed <dean.a.rasheed@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: Dean Rasheed @ 2026-09-15 10:51 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/e7006ecaebe6a64402a5d9eb574aad3215ba0aa4 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(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. @ 2026-09-15 10:51 Dean Rasheed <dean.a.rasheed@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: Dean Rasheed @ 2026-09-15 10:51 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. @ 2026-09-15 10:51 Dean Rasheed <dean.a.rasheed@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: Dean Rasheed @ 2026-09-15 10:51 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/5b4ebfbfaec322dd7b2d2ee25075ae9efe9bee0a 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(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. @ 2026-09-15 10:51 Dean Rasheed <dean.a.rasheed@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: Dean Rasheed @ 2026-09-15 10:51 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/80c9a58b4dc773bb997196e75717829f202f1697 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(+) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. @ 2026-09-15 10:51 Dean Rasheed <dean.a.rasheed@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: Dean Rasheed @ 2026-09-15 10:51 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/cbd2406d07818bd786b1b6459ab67c2823d0aadb 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(+) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. @ 2026-09-15 10:51 Dean Rasheed <dean.a.rasheed@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: Dean Rasheed @ 2026-09-15 10:51 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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_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(+) ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-09-15 10:51 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-09-15 10:51 pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. Dean Rasheed <dean.a.rasheed@gmail.com> 2026-09-15 10:51 pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. Dean Rasheed <dean.a.rasheed@gmail.com> 2026-09-15 10:51 pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. Dean Rasheed <dean.a.rasheed@gmail.com> 2026-09-15 10:51 pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. Dean Rasheed <dean.a.rasheed@gmail.com> 2026-09-15 10:51 pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. Dean Rasheed <dean.a.rasheed@gmail.com> 2026-09-15 10:51 pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. Dean Rasheed <dean.a.rasheed@gmail.com> 2026-09-15 10:51 pgsql: Fix missing SIREAD lock on the row found by ON CONFLICT. Dean Rasheed <dean.a.rasheed@gmail.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox