On 2026-Sep-01, Zsolt Parragi wrote:
> On Tue, 01 Sep 2026, Mihail Nikalayeu <[email protected]> wrote:
> > This is a "grouped" version. Also, it handles possible collation
> > issues + provides a set of tests to pin the correct behaviour.
>
> Thanks, this looks better what I had in mind, I would have missed a
> few corner cases this patch covers.
I spent some time with this and ended up with the attached. I don't I
found anything to change, apart from minor edits to the commit message.
I'll probably edit it some more before push, to mention the change of
list_difference() to equal().
The non-deterministic collation aspect mentioned in an XXX comment added
by the patch was a bug in 18 and back, and continues to be a bug after
this patch. That's shown with the following test case:
CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2', deterministic
= false);
-- First part of test case: ON CONFLICT listing a column works fine.
CREATE TABLE t (x text, y text);
ALTER TABLE t ADD CONSTRAINT t_x_key UNIQUE (x);
CREATE UNIQUE INDEX t_x_ci ON t (x COLLATE ci);
INSERT INTO t VALUES ('a', 'first');
INSERT INTO t VALUES ('A', 'second') ON CONFLICT (x) DO UPDATE SET y =
excluded.y;
-- the end result here is ('a', 'second'), showing that ON CONFLICT worked.
SELECT x, y FROM t;
-- repeat, but use ON CONFLICT ON CONSTRAINT. Throws error but shouldn't.
INSERT INTO t VALUES ('A', 'third') ON CONFLICT ON CONSTRAINT t_x_key DO UPDATE
SET y = excluded.y;
It's not on this patch to solve this problem, as it's not a new problem.
But we should consider a backpatchable fix at some point.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"I can't go to a restaurant and order food because I keep looking at the
fonts on the menu. Five minutes later I realize that it's also talking
about food" (Donald Knuth)
>From 8c01c41b126002fac2cf22b38ca25501e02e730c Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <[email protected]>
Date: Thu, 17 Sep 2026 19:32:20 +0200
Subject: [PATCH] Tighten definition of ON CONFLICT arbiter index equivalence
Commits 2bc7e886fc1b and 90eae926abbb taught ON CONFLICT to include
indexes matching an already selected arbiter, so that an index left
behind by REINDEX CONCURRENTLY continues to arbitrate together with its
replacement. Both checks were too permissive:
a) infer_arbiter_indexes() compared a candidate with a named
constraint's index using only attributes, expressions and predicate, but
ignored collation, NULLS NOT DISTINCT setting or deferrability. As a
result, an index with a difference in these settings could be accepted
even though it did not identify the same conflicts. Also, a deferrable
index that otherwise matches an arbiter index would also cause ON
CONFLICT to fail with "ON CONFLICT does not support deferrable unique
constraints/exclusion constraints as arbiters".
b) ExecInitPartitionInfo() also failed due to the failure to compare
deferrability, so partition-local deferrable indexes would be considered
and break inserts routed to that partition with the error mentioned
above.
Fix by making IsIndexCompatibleAsArbiter() compare those properties, and
changing infer_arbiter_indexes() to use that routine instead of open
coding equivalent logic. Also handle the named-constraint case in
infer_arbiter_indexes() separately instead of passing the constraint
index through the regular clause-inference matching. That function is
not static anymore, so move it to index.c, and also reimplement it to
use the Relation from the indexes only, no longer receiving the
IndexInfo (which wasn't really necessary.)
Add tests for ON CONFLICT ON CONSTRAINT with deferrable, NULLS NOT
DISTINCT, and different-collation sibling indexes, preserving the
behavior of released pre-19 versions. Also test routed inserts with a
partition-local deferrable unique constraint.
Author: Zsolt Parragi <[email protected]>
Author: Mihail Nikalayeu <[email protected]>
Reported-by: Zsolt Parragi <[email protected]>
Reviewed-by: Michael Paquier <[email protected]>
Backpatch-through: 19
Discussion: https://postgr.es/m/can4czfpeyxeyftxhpopujfvfb+1tx1jnxvbodmbg-zhpgpq...@mail.gmail.com
---
src/backend/catalog/index.c | 74 +++++++++++++
src/backend/executor/execPartition.c | 65 +----------
src/backend/optimizer/util/plancat.c | 102 +++++++-----------
src/include/catalog/index.h | 3 +
.../regress/expected/collate.icu.utf8.out | 28 +++++
src/test/regress/expected/insert_conflict.out | 96 +++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 18 ++++
src/test/regress/sql/insert_conflict.sql | 59 ++++++++++
8 files changed, 318 insertions(+), 127 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index ec21b83b6b8..2a46cc4de19 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2752,6 +2752,80 @@ BuildSpeculativeIndexInfo(Relation index, IndexInfo *ii)
}
}
+/* ----------------
+ * IsIndexCompatibleAsArbiter
+ * Return true if two indexes of the same table are interchangeable as
+ * speculative insertion arbiters for INSERT ON CONFLICT.
+ *
+ * To be interchangeable, the two indexes must agree on which tuples conflict,
+ * so every property bearing on that must be identical. Indexes that merely
+ * index the same columns can each have their own notion of what a duplicate
+ * is, and treating one as arbiter in place of the other would resolve
+ * conflicts the other does not have.
+ *
+ * This is built for REINDEX CONCURRENTLY: while it processes an arbiter
+ * index, an exact copy of it built by index_create_copy() exists alongside,
+ * and both copies must arbitrate together for all concurrent sessions to
+ * agree on the set of arbiters.
+ *
+ * Properties that do not affect which tuples conflict, such as validity,
+ * are deliberately not examined here; callers must check them as needed.
+ * ----------------
+ */
+bool
+IsIndexCompatibleAsArbiter(Relation indexRel1, Relation indexRel2)
+{
+ Form_pg_index indexForm1 = indexRel1->rd_index;
+ Form_pg_index indexForm2 = indexRel2->rd_index;
+
+ /* Only indexes of the same relation can be compared. */
+ Assert(indexForm1->indrelid == indexForm2->indrelid);
+
+ /* must match whether they're unique */
+ if (indexForm1->indisunique != indexForm2->indisunique)
+ return false;
+
+ /* No support currently for comparing exclusion indexes. */
+ if (indexForm1->indisexclusion || indexForm2->indisexclusion)
+ return false;
+
+ /* a deferrable index detects conflicts at a different time */
+ if (indexForm1->indimmediate != indexForm2->indimmediate)
+ return false;
+
+ /* the "nulls not distinct" criterion must match */
+ if (indexForm1->indnullsnotdistinct != indexForm2->indnullsnotdistinct)
+ return false;
+
+ /* number of key attributes must match */
+ if (indexForm1->indnkeyatts != indexForm2->indnkeyatts)
+ return false;
+
+ /* key columns, and their collations and opfamilies, must match */
+ for (int i = 0; i < indexForm1->indnkeyatts; i++)
+ {
+ if (indexForm1->indkey.values[i] != indexForm2->indkey.values[i])
+ return false;
+
+ if (indexRel1->rd_indcollation[i] != indexRel2->rd_indcollation[i])
+ return false;
+
+ if (indexRel1->rd_opfamily[i] != indexRel2->rd_opfamily[i])
+ return false;
+ }
+
+ /* index expressions and predicate must match */
+ if (!equal(RelationGetIndexExpressions(indexRel1),
+ RelationGetIndexExpressions(indexRel2)))
+ return false;
+
+ if (!equal(RelationGetIndexPredicate(indexRel1),
+ RelationGetIndexPredicate(indexRel2)))
+ return false;
+
+ return true;
+}
+
/* ----------------
* FormIndexDatum
* Construct values[] and isnull[] arrays for a new index tuple.
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
index 2b5f2e3cead..86fa0f0deaa 100644
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -493,65 +493,6 @@ ExecFindPartition(ModifyTableState *mtstate,
return rri;
}
-/*
- * IsIndexCompatibleAsArbiter
- * Return true if two indexes are identical for INSERT ON CONFLICT
- * purposes.
- *
- * Only indexes of the same relation are supported.
- */
-static bool
-IsIndexCompatibleAsArbiter(Relation arbiterIndexRelation,
- IndexInfo *arbiterIndexInfo,
- Relation indexRelation,
- IndexInfo *indexInfo)
-{
- Assert(arbiterIndexRelation->rd_index->indrelid == indexRelation->rd_index->indrelid);
-
- /* must match whether they're unique */
- if (arbiterIndexInfo->ii_Unique != indexInfo->ii_Unique)
- return false;
-
- /* No support currently for comparing exclusion indexes. */
- if (arbiterIndexInfo->ii_ExclusionOps != NULL ||
- indexInfo->ii_ExclusionOps != NULL)
- return false;
-
- /* the "nulls not distinct" criterion must match */
- if (arbiterIndexInfo->ii_NullsNotDistinct !=
- indexInfo->ii_NullsNotDistinct)
- return false;
-
- /* number of key attributes must match */
- if (arbiterIndexInfo->ii_NumIndexKeyAttrs !=
- indexInfo->ii_NumIndexKeyAttrs)
- return false;
-
- for (int i = 0; i < arbiterIndexInfo->ii_NumIndexKeyAttrs; i++)
- {
- if (arbiterIndexRelation->rd_indcollation[i] !=
- indexRelation->rd_indcollation[i])
- return false;
-
- if (arbiterIndexRelation->rd_opfamily[i] !=
- indexRelation->rd_opfamily[i])
- return false;
-
- if (arbiterIndexRelation->rd_index->indkey.values[i] !=
- indexRelation->rd_index->indkey.values[i])
- return false;
- }
-
- if (list_difference(RelationGetIndexExpressions(arbiterIndexRelation),
- RelationGetIndexExpressions(indexRelation)) != NIL)
- return false;
-
- if (list_difference(RelationGetIndexPredicate(arbiterIndexRelation),
- RelationGetIndexPredicate(indexRelation)) != NIL)
- return false;
- return true;
-}
-
/*
* ExecInitPartitionInfo
* Lock the partition and initialize ResultRelInfo. Also setup other
@@ -846,19 +787,15 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
foreach_int(arbiter_i, arbiters_listidxs)
{
Relation arbiter_rel;
- IndexInfo *arbiter_ii;
arbiter_rel = leaf_part_rri->ri_IndexRelationDescs[arbiter_i];
- arbiter_ii = leaf_part_rri->ri_IndexRelationInfo[arbiter_i];
/*
* If the non-ancestor index is compatible with the
* arbiter, use the non-ancestor as arbiter too.
*/
if (IsIndexCompatibleAsArbiter(arbiter_rel,
- arbiter_ii,
- unparented_rel,
- unparented_ii))
+ unparented_rel))
{
arbiterIndexes = lappend_oid(arbiterIndexes,
unparented_rel->rd_index->indexrelid);
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 4c9c5e9fc33..5231d0a26a3 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -27,6 +27,7 @@
#include "access/xlog.h"
#include "catalog/catalog.h"
#include "catalog/heap.h"
+#include "catalog/index.h"
#include "catalog/pg_am.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_statistic_ext.h"
@@ -795,6 +796,13 @@ find_relation_notnullatts(PlannerInfo *root, Oid relid)
* the purposes of inference. If no opclass (or collation) is specified, then
* all matching indexes (that may or may not match the default in terms of
* each attribute opclass/collation) are used for inference.
+ *
+ * If a named constraint was specified, none of that matching happens: the
+ * constraint's index is used, along with any index that is an exact
+ * structural equivalent of it. Such equivalents exist transiently while
+ * REINDEX CONCURRENTLY processes the constraint's index, and they must all
+ * arbitrate together so that every concurrent session resolves conflicts
+ * against the same set of indexes.
*/
List *
infer_arbiter_indexes(PlannerInfo *root)
@@ -804,18 +812,13 @@ infer_arbiter_indexes(PlannerInfo *root)
/* Iteration state */
Index varno;
RangeTblEntry *rte;
- Relation relation;
+ Relation relation,
+ indexRelFromConstraint = NULL;
Oid indexOidFromConstraint = InvalidOid;
List *indexList;
List *indexRelList = NIL;
- /*
- * Required attributes and expressions used to match indexes to the clause
- * given by the user. In the ON CONFLICT ON CONSTRAINT case, we compute
- * these from that constraint's index to match all other indexes, to
- * account for the case where that index is being concurrently reindexed.
- */
- List *inferIndexExprs = (List *) onconflict->arbiterWhere;
+ /* Normalized inference attributes and inference expressions: */
Bitmapset *inferAttrs = NULL;
List *inferElems = NIL;
@@ -911,39 +914,22 @@ infer_arbiter_indexes(PlannerInfo *root)
errmsg("constraint in ON CONFLICT clause has no associated index")));
/*
- * Find the named constraint index to extract its attributes and
- * predicates.
+ * Find that index in the list, so that candidate indexes can be
+ * compared against it below. The constraint belongs to the target
+ * relation, so its index must be here.
*/
foreach_ptr(RelationData, idxRel, indexRelList)
{
- Form_pg_index idxForm = idxRel->rd_index;
-
- if (indexOidFromConstraint == idxForm->indexrelid)
+ if (indexOidFromConstraint == RelationGetRelid(idxRel))
{
- /* Found it. */
- Assert(idxForm->indisready);
-
- /*
- * Set up inferElems and inferIndexExprs to match the
- * constraint index, so that we can match them in the loop
- * below.
- */
- for (int natt = 0; natt < idxForm->indnkeyatts; natt++)
- {
- int attno;
-
- attno = idxRel->rd_index->indkey.values[natt];
- if (attno != InvalidAttrNumber)
- inferAttrs =
- bms_add_member(inferAttrs,
- attno - FirstLowInvalidHeapAttributeNumber);
- }
-
- inferElems = RelationGetIndexExpressions(idxRel);
- inferIndexExprs = RelationGetIndexPredicate(idxRel);
+ Assert(idxRel->rd_index->indisready);
+ indexRelFromConstraint = idxRel;
break;
}
}
+ if (indexRelFromConstraint == NULL)
+ elog(ERROR, "could not find index %u of ON CONFLICT constraint",
+ indexOidFromConstraint);
}
/*
@@ -1028,13 +1014,16 @@ infer_arbiter_indexes(PlannerInfo *root)
else if (indexOidFromConstraint != InvalidOid)
{
/*
- * In the case of "ON constraint_name DO SELECT/UPDATE" we need to
- * skip non-unique candidates.
+ * When a constraint is named, the only other indexes that may
+ * arbitrate are exact structural equivalents of its index, which
+ * exist while REINDEX CONCURRENTLY is processing it.
*/
- if (!idxForm->indisunique &&
- (onconflict->action == ONCONFLICT_UPDATE ||
- onconflict->action == ONCONFLICT_SELECT))
- continue;
+ if (IsIndexCompatibleAsArbiter(indexRelFromConstraint, idxRel))
+ {
+ results = lappend_oid(results, idxForm->indexrelid);
+ foundValid |= idxForm->indisvalid;
+ }
+ continue;
}
else
{
@@ -1079,10 +1068,7 @@ infer_arbiter_indexes(PlannerInfo *root)
idxExprs = (List *) eval_const_expressions(root, (Node *) idxExprs);
}
- /*
- * If arbiterElems are present, check them. (Note that if a
- * constraint name was given in the command line, this list is NIL.)
- */
+ /* Check the arbiterElems against this index. */
match = true;
foreach_ptr(InferenceElem, elem, onconflict->arbiterElems)
{
@@ -1125,14 +1111,11 @@ infer_arbiter_indexes(PlannerInfo *root)
continue;
/*
- * In case of inference from an attribute list, ensure that the
+ * Now that all inference elements were matched, ensure that the
* expression elements from inference clause are not missing any
* cataloged expressions. This does the right thing when unique
* indexes redundantly repeat the same attribute, or if attributes
* redundantly appear multiple times within an inference clause.
- *
- * In case a constraint was named, ensure the candidate has an equal
- * set of expressions as the named constraint's index.
*/
if (list_difference(idxExprs, inferElems) != NIL)
continue;
@@ -1150,21 +1133,12 @@ infer_arbiter_indexes(PlannerInfo *root)
}
/*
- * Partial indexes affect each form of ON CONFLICT differently: if a
- * constraint was named, then the predicates must be identical. In
- * conventional inference, the index's predicate must be implied by
- * the WHERE clause.
+ * If it's a partial index, its predicate must be implied by the ON
+ * CONFLICT's WHERE clause.
*/
- if (OidIsValid(indexOidFromConstraint))
- {
- if (list_difference(predExprs, inferIndexExprs) != NIL)
- continue;
- }
- else
- {
- if (!predicate_implied_by(predExprs, inferIndexExprs, false))
- continue;
- }
+ if (!predicate_implied_by(predExprs,
+ (List *) onconflict->arbiterWhere, false))
+ continue;
/* All good -- consider this index a match */
results = lappend_oid(results, idxForm->indexrelid);
@@ -1202,7 +1176,9 @@ infer_arbiter_indexes(PlannerInfo *root)
*
* At least historically, Postgres has not offered collations or opclasses
* with alternative-to-default notions of equality, so these additional
- * criteria should only be required infrequently.
+ * criteria should only be required infrequently. XXX That is no longer
+ * true: nondeterministic collations, supported since PostgreSQL 12, do
+ * equate values that the default notion of equality keeps distinct.
*
* Don't give up immediately when an inference element matches some attribute
* cataloged as indexed but not matching additional opclass/collation
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index b952ad071d3..5add66817cf 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -140,6 +140,9 @@ extern bool CompareIndexInfo(const IndexInfo *info1, const IndexInfo *info2,
extern void BuildSpeculativeIndexInfo(Relation index, IndexInfo *ii);
+extern bool IsIndexCompatibleAsArbiter(Relation indexRel1,
+ Relation indexRel2);
+
extern void FormIndexDatum(IndexInfo *indexInfo,
TupleTableSlot *slot,
EState *estate,
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index a32e8965725..5ec848b82e4 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2102,6 +2102,34 @@ DETAIL: Key (x)=(ABC) already exists.
CREATE UNIQUE INDEX ON test3ci (x); -- error
ERROR: could not create unique index "test3ci_x_idx"
DETAIL: Key (x)=(abc) is duplicated.
+-- ON CONFLICT ON CONSTRAINT must not use an index that differs from the
+-- named constraint's index in collation
+CREATE TABLE test_arbiter_ci (x text, y text);
+ALTER TABLE test_arbiter_ci ADD CONSTRAINT test_arbiter_ci_x_key UNIQUE (x);
+CREATE UNIQUE INDEX test_arbiter_ci_x_ci
+ ON test_arbiter_ci (x COLLATE case_insensitive);
+INSERT INTO test_arbiter_ci VALUES ('abc', 'first');
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'second')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key
+ DO UPDATE SET y = excluded.y; -- error
+ERROR: duplicate key value violates unique constraint "test_arbiter_ci_x_ci"
+DETAIL: Key (x)=(ABC) already exists.
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'third')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key DO NOTHING; -- error
+ERROR: duplicate key value violates unique constraint "test_arbiter_ci_x_ci"
+DETAIL: Key (x)=(ABC) already exists.
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'fourth')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key
+ DO SELECT RETURNING *; -- error
+ERROR: duplicate key value violates unique constraint "test_arbiter_ci_x_ci"
+DETAIL: Key (x)=(ABC) already exists.
+SELECT x, y FROM test_arbiter_ci;
+ x | y
+-----+-------
+ abc | first
+(1 row)
+
+DROP TABLE test_arbiter_ci;
SELECT string_to_array('ABC,DEF,GHI' COLLATE case_insensitive, ',', 'abc');
string_to_array
-----------------
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 34e2e7ee355..b4c9bdb0e55 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -1064,6 +1064,102 @@ insert into parted_conflict_1 values (40, 'cuarenta')
on conflict (a) do update set b = excluded.b;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
drop table parted_conflict;
+-- a partition-local deferrable unique constraint on the arbiter columns
+-- must not be picked up as an additional arbiter for routed inserts
+create table parted_conflict (a int, b text, primary key (a)) partition by range (a);
+create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000);
+alter table parted_conflict_1 add constraint parted_conflict_1_a_def unique (a) deferrable;
+insert into parted_conflict values (40, 'forty');
+insert into parted_conflict values (40, 'cuarenta')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (41, 'forty-one')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (42, 'forty-two') on conflict (a) do nothing;
+select * from parted_conflict order by a;
+ a | b
+----+-----------
+ 40 | cuarenta
+ 41 | forty-one
+ 42 | forty-two
+(3 rows)
+
+drop table parted_conflict;
+-- ON CONFLICT ON CONSTRAINT has the same hazard: an index that merely covers
+-- the same columns as the named constraint's index, but is deferrable or
+-- disagrees with it about duplicates, must not join the arbiter set
+create table namedconstraint (a int, b text, c text);
+alter table namedconstraint add constraint namedconstraint_a_key unique (a);
+alter table namedconstraint add constraint namedconstraint_a_def unique (a) deferrable;
+create unique index namedconstraint_b_nnd on namedconstraint (b) nulls not distinct;
+alter table namedconstraint add constraint namedconstraint_b_key unique (b);
+insert into namedconstraint values (1, 'x', 'first');
+insert into namedconstraint values (2, null, 'first');
+insert into namedconstraint values (1, 'y', 'second')
+ on conflict on constraint namedconstraint_a_key do update set c = excluded.c;
+insert into namedconstraint values (3, null, 'second')
+ on conflict on constraint namedconstraint_b_key do update set c = excluded.c; -- error
+ERROR: duplicate key value violates unique constraint "namedconstraint_b_nnd"
+DETAIL: Key (b)=(null) already exists.
+insert into namedconstraint values (4, 'z', 'third')
+ on conflict on constraint namedconstraint_a_key do nothing;
+insert into namedconstraint values (5, null, 'third')
+ on conflict on constraint namedconstraint_b_key do nothing; -- error
+ERROR: duplicate key value violates unique constraint "namedconstraint_b_nnd"
+DETAIL: Key (b)=(null) already exists.
+select * from namedconstraint order by a;
+ a | b | c
+---+---+--------
+ 1 | x | second
+ 2 | | first
+ 4 | z | third
+(3 rows)
+
+drop table namedconstraint;
+-- same, with the indexes created in the opposite order
+create table namedconstraint2 (a int, b text, c text);
+alter table namedconstraint2 add constraint namedconstraint2_a_def unique (a) deferrable;
+alter table namedconstraint2 add constraint namedconstraint2_a_key unique (a);
+alter table namedconstraint2 add constraint namedconstraint2_b_key unique (b);
+create unique index namedconstraint2_b_nnd on namedconstraint2 (b) nulls not distinct;
+insert into namedconstraint2 values (1, 'x', 'first');
+insert into namedconstraint2 values (2, null, 'first');
+insert into namedconstraint2 values (1, 'y', 'second')
+ on conflict on constraint namedconstraint2_a_key do update set c = excluded.c;
+insert into namedconstraint2 values (3, 'z', 'second')
+ on conflict on constraint namedconstraint2_a_key do nothing;
+insert into namedconstraint2 values (4, null, 'second')
+ on conflict on constraint namedconstraint2_b_key do nothing; -- error
+ERROR: duplicate key value violates unique constraint "namedconstraint2_b_nnd"
+DETAIL: Key (b)=(null) already exists.
+-- ON CONFLICT DO SELECT reaches the same code
+insert into namedconstraint2 values (1, 'w', 'third')
+ on conflict on constraint namedconstraint2_a_key do select returning *;
+ a | b | c
+---+---+--------
+ 1 | x | second
+(1 row)
+
+insert into namedconstraint2 values (5, 'v', 'third')
+ on conflict on constraint namedconstraint2_a_key do select returning *;
+ a | b | c
+---+---+-------
+ 5 | v | third
+(1 row)
+
+insert into namedconstraint2 values (6, null, 'fourth')
+ on conflict on constraint namedconstraint2_b_key do select returning *; -- error
+ERROR: duplicate key value violates unique constraint "namedconstraint2_b_nnd"
+DETAIL: Key (b)=(null) already exists.
+select * from namedconstraint2 order by a;
+ a | b | c
+---+---+--------
+ 1 | x | second
+ 2 | | first
+ 3 | z | second
+ 5 | v | third
+(4 rows)
+
+drop table namedconstraint2;
-- test whole-row Vars in ON CONFLICT expressions
create table parted_conflict (a int, b text, c int) partition by range (a);
create table parted_conflict_1 (drp text, c int, a int, b text);
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index f0d9ee2c96d..b50cf3f6c7c 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -748,6 +748,24 @@ SELECT x, row_number() OVER (ORDER BY x), rank() OVER (ORDER BY x) FROM test3ci
CREATE UNIQUE INDEX ON test1ci (x); -- ok
INSERT INTO test1ci VALUES ('ABC'); -- error
CREATE UNIQUE INDEX ON test3ci (x); -- error
+
+-- ON CONFLICT ON CONSTRAINT must not use an index that differs from the
+-- named constraint's index in collation
+CREATE TABLE test_arbiter_ci (x text, y text);
+ALTER TABLE test_arbiter_ci ADD CONSTRAINT test_arbiter_ci_x_key UNIQUE (x);
+CREATE UNIQUE INDEX test_arbiter_ci_x_ci
+ ON test_arbiter_ci (x COLLATE case_insensitive);
+INSERT INTO test_arbiter_ci VALUES ('abc', 'first');
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'second')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key
+ DO UPDATE SET y = excluded.y; -- error
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'third')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key DO NOTHING; -- error
+INSERT INTO test_arbiter_ci VALUES ('ABC', 'fourth')
+ ON CONFLICT ON CONSTRAINT test_arbiter_ci_x_key
+ DO SELECT RETURNING *; -- error
+SELECT x, y FROM test_arbiter_ci;
+DROP TABLE test_arbiter_ci;
SELECT string_to_array('ABC,DEF,GHI' COLLATE case_insensitive, ',', 'abc');
SELECT string_to_array('ABCDEFGHI' COLLATE case_insensitive, NULL, 'b');
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index a5a84d1d4b8..d119158549f 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -619,6 +619,65 @@ insert into parted_conflict_1 values (40, 'cuarenta')
on conflict (a) do update set b = excluded.b;
drop table parted_conflict;
+-- a partition-local deferrable unique constraint on the arbiter columns
+-- must not be picked up as an additional arbiter for routed inserts
+create table parted_conflict (a int, b text, primary key (a)) partition by range (a);
+create table parted_conflict_1 partition of parted_conflict for values from (0) to (1000);
+alter table parted_conflict_1 add constraint parted_conflict_1_a_def unique (a) deferrable;
+insert into parted_conflict values (40, 'forty');
+insert into parted_conflict values (40, 'cuarenta')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (41, 'forty-one')
+ on conflict (a) do update set b = excluded.b;
+insert into parted_conflict values (42, 'forty-two') on conflict (a) do nothing;
+select * from parted_conflict order by a;
+drop table parted_conflict;
+
+-- ON CONFLICT ON CONSTRAINT has the same hazard: an index that merely covers
+-- the same columns as the named constraint's index, but is deferrable or
+-- disagrees with it about duplicates, must not join the arbiter set
+create table namedconstraint (a int, b text, c text);
+alter table namedconstraint add constraint namedconstraint_a_key unique (a);
+alter table namedconstraint add constraint namedconstraint_a_def unique (a) deferrable;
+create unique index namedconstraint_b_nnd on namedconstraint (b) nulls not distinct;
+alter table namedconstraint add constraint namedconstraint_b_key unique (b);
+insert into namedconstraint values (1, 'x', 'first');
+insert into namedconstraint values (2, null, 'first');
+insert into namedconstraint values (1, 'y', 'second')
+ on conflict on constraint namedconstraint_a_key do update set c = excluded.c;
+insert into namedconstraint values (3, null, 'second')
+ on conflict on constraint namedconstraint_b_key do update set c = excluded.c; -- error
+insert into namedconstraint values (4, 'z', 'third')
+ on conflict on constraint namedconstraint_a_key do nothing;
+insert into namedconstraint values (5, null, 'third')
+ on conflict on constraint namedconstraint_b_key do nothing; -- error
+select * from namedconstraint order by a;
+drop table namedconstraint;
+
+-- same, with the indexes created in the opposite order
+create table namedconstraint2 (a int, b text, c text);
+alter table namedconstraint2 add constraint namedconstraint2_a_def unique (a) deferrable;
+alter table namedconstraint2 add constraint namedconstraint2_a_key unique (a);
+alter table namedconstraint2 add constraint namedconstraint2_b_key unique (b);
+create unique index namedconstraint2_b_nnd on namedconstraint2 (b) nulls not distinct;
+insert into namedconstraint2 values (1, 'x', 'first');
+insert into namedconstraint2 values (2, null, 'first');
+insert into namedconstraint2 values (1, 'y', 'second')
+ on conflict on constraint namedconstraint2_a_key do update set c = excluded.c;
+insert into namedconstraint2 values (3, 'z', 'second')
+ on conflict on constraint namedconstraint2_a_key do nothing;
+insert into namedconstraint2 values (4, null, 'second')
+ on conflict on constraint namedconstraint2_b_key do nothing; -- error
+-- ON CONFLICT DO SELECT reaches the same code
+insert into namedconstraint2 values (1, 'w', 'third')
+ on conflict on constraint namedconstraint2_a_key do select returning *;
+insert into namedconstraint2 values (5, 'v', 'third')
+ on conflict on constraint namedconstraint2_a_key do select returning *;
+insert into namedconstraint2 values (6, null, 'fourth')
+ on conflict on constraint namedconstraint2_b_key do select returning *; -- error
+select * from namedconstraint2 order by a;
+drop table namedconstraint2;
+
-- test whole-row Vars in ON CONFLICT expressions
create table parted_conflict (a int, b text, c int) partition by range (a);
create table parted_conflict_1 (drp text, c int, a int, b text);
--
2.47.3
