From: Álvaro Herrera <alvherre@kurilemu.de>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix ON CONFLICT ON CONSTRAINT during REINDEX CONCURRENTLY
Date: Mon, 01 Dec 2025 16:34:47 +0000
Message-ID: <E1vQ6rP-002Inv-0j@gemulon.postgresql.org> (raw)
Fix ON CONFLICT ON CONSTRAINT during REINDEX CONCURRENTLY
When REINDEX CONCURRENTLY is processing the index that supports a
constraint, there are periods during which multiple indexes match the
constraint index's definition. Those must all be included in the set of
inferred index for INSERT ON CONFLICT, in order to avoid spurious
"duplicate key" errors.
To fix, we set things up to match all indexes against attributes,
expressions and predicates of the constraint index, then return all
indexes that match those, rather than just the one constraint index.
This is more onerous than before, where we would just test the named
constraint for validity, but it's not more onerous than processing
"conventional" inference (where a list of attribute names etc is given).
This is closely related to the misbehaviors fixed by bc32a12e0db2, for a
different situation. We're not backpatching this one for now either,
for the same reasons.
Author: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Discussion: https://postgr.es/m/CANtu0ojXmqjmEzp-=aJSxjsdE76iAsRgHBoK0QtYHimb_mEfsg@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/2bc7e886fc1baaeee3987a141bff3ac490037d12
Modified Files
--------------
src/backend/optimizer/util/plancat.c | 187 ++++++++++++----
src/test/modules/injection_points/Makefile | 1 +
.../reindex-concurrently-upsert-on-constraint.out | 238 +++++++++++++++++++++
src/test/modules/injection_points/meson.build | 1 +
.../reindex-concurrently-upsert-on-constraint.spec | 110 ++++++++++
5 files changed, 490 insertions(+), 47 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 ON CONFLICT ON CONSTRAINT during REINDEX CONCURRENTLY
In-Reply-To: <E1vQ6rP-002Inv-0j@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