From: Álvaro Herrera <alvherre@kurilemu.de>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix infer_arbiter_index during concurrent index operations
Date: Mon, 24 Nov 2025 16:18:34 +0000
Message-ID: <E1vNZGr-001C9L-21@gemulon.postgresql.org> (raw)
Fix infer_arbiter_index during concurrent index operations
Previously, we would only consider indexes marked indisvalid as usable
for INSERT ON CONFLICT. But that's problematic during CREATE INDEX
CONCURRENTLY and REINDEX CONCURRENTLY, because concurrent transactions
would end up with inconsistents lists of inferred indexes, leading to
deadlocks and spurious errors about unique key violations (because two
transactions are operating on different indexes for the speculative
insertion tokens). Change this function to return indexes even if
invalid. This fixes the spurious errors and deadlocks.
Because such indexes might not be complete, we still need uniqueness to
be verified in a different way. We do that by requiring that at least
one index marked valid is part of the set of indexes returned. It is
that index that is going to help ensure that the inserted tuple is
indeed unique.
This does not fix similar problems occurring with partitioned tables or
with named constraints. These problems will be fixed in follow-up
commits.
We have no user report of this problem, even though it exists in all
branches. Because of that and given that the fix is somewhat tricky, I
decided not to backpatch for now.
Author: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Discussion: https://postgr.es/m/CANtu0ogv+6wqRzPK241jik4U95s1pW3MCZ3rX5ZqbFdUysz7Qw@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/bc32a12e0db2df203a9cb2315461578e08568b9c
Modified Files
--------------
src/backend/commands/indexcmds.c | 3 +
src/backend/executor/execIndexing.c | 8 +
src/backend/executor/execPartition.c | 5 +-
src/backend/executor/nodeModifyTable.c | 2 +
src/backend/optimizer/util/plancat.c | 27 ++-
src/backend/utils/time/snapmgr.c | 2 +
src/test/modules/injection_points/Makefile | 7 +-
.../index-concurrently-upsert-predicate.out | 86 ++++++++
.../expected/index-concurrently-upsert.out | 86 ++++++++
.../expected/reindex-concurrently-upsert.out | 238 +++++++++++++++++++++
src/test/modules/injection_points/meson.build | 7 +
.../specs/index-concurrently-upsert-predicate.spec | 88 ++++++++
.../specs/index-concurrently-upsert.spec | 87 ++++++++
.../specs/reindex-concurrently-upsert.spec | 111 ++++++++++
14 files changed, 749 insertions(+), 8 deletions(-)
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: alvherre@kurilemu.de, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Fix infer_arbiter_index during concurrent index operations
In-Reply-To: <E1vNZGr-001C9L-21@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox