Routed ON CONFLICT inserts broken by partition-local deferrable unique constraints in 19 and master

On Fri, Aug 28, 2026 at 09:19:52PM +0100, Zsolt Parragi wrote:

While testing ON CONFLICT on partitioned tables on master, I found
that a deferrable unique constraint on a leaf partition breaks every
routed insert that takes the no-conflict path. This is a regression
from commit 90eae926abbb (Fix ON CONFLICT with REINDEX CONCURRENTLY
and partitions[1]), so it affects master and the 19 betas, but not 18.

Thanks for the report.

Since 90eae926abbb, ExecInitPartitionInfo matches every leaf index
that has no parent against the arbiters mapped from the root.
IsIndexCompatibleAsArbiter compares several properties, but not
indimmediate.

This is new as of v19, but I also feel responsible for missing the
fact that indisimmediate was incorrect until 74276e685dd0, causing
random constraint errors during a concurrent build. So we could say
that 90eae926abbb got inspiration from the pre-74276e685dd0 code in
terms of the index copies created.

This is one of the issues I found with cross-checking feature
interactions with Claude[2], and I thoght I'll submit this first since
this is a PG19 regression.

Reproducer:
[...]
-- ERROR: ON CONFLICT does not support deferrable unique
-- constraints/exclusion constraints as arbiters
INSERT INTO d VALUES (2, 'two') ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b;
INSERT INTO d VALUES (3, 'three') ON CONFLICT (a) DO NOTHING;

That's nice.

I'll look at all that tomorrow in depth. Just too tired today to do
so now. :p
--
Michael

Hello!

Thanks for the report!

Looks like my fault, I'll also recheck everything a little bit later.

Best regards,
Mikhail.

Hello!

What I understood - the issue is a little bit wider, it also may
appear on a named constraint - there it is not checked for both
`deferrable` and `nulls not distinct` to be matching original index
itself.

Going to do something with it in near future.

Mikhail.

On Mon, 31 Aug 2026, Mihail Nikalayeu <mihailnikalayeu@gmail.com> wrote:

What I understood - the issue is a little bit wider, it also may
appear on a named constraint - there it is not checked for both
`deferrable` and `nulls not distinct` to be matching original index
itself.

I didn't add tests about this in the patch, but it should also handle these partition-local cases, e.g.

CREATE TABLE d (a int, b text, CONSTRAINT d_pk PRIMARY KEY (a)) PARTITION BY RANGE (a);
CREATE TABLE d1 PARTITION OF d FOR VALUES FROM (0) TO (100);
ALTER TABLE d1 ADD CONSTRAINT d1_a_def UNIQUE (a) DEFERRABLE;
INSERT INTO d VALUES (1, 'one');
INSERT INTO d VALUES (2, 'two') ON CONFLICT ON CONSTRAINT d_pk DO UPDATE SET b = EXCLUDED.b;
-- master: ERROR; patched: INSERT 0 1

Also, now that I took another look into this, a somewhat similar PG19 regression exists outside partitions tables, caused by a different commit (2bc7e886fc1):

CREATE TABLE t (a int, b text, CONSTRAINT t_u UNIQUE (a));
ALTER TABLE t ADD CONSTRAINT t_nnd UNIQUE NULLS NOT DISTINCT (a);
INSERT INTO t VALUES (NULL, 'one');

INSERT INTO t VALUES (NULL, 'two') ON CONFLICT ON CONSTRAINT t_u DO UPDATE SET b = 'upd';
-- 18: ERROR: duplicate key value violates unique constraint "t_nnd"
-- (named arbiter t_u sees no conflict on NULLs, insert proceeds, t_nnd rejects)
-- 19: INSERT 0 1 -> table now (NULL, 'upd')
-- (t_nnd silently used as arbiter user never named; error swallowed, row updated)

But that seems like a different issue requiring a different fix.

On Mon, Aug 31, 2026 at 07:47:29PM -0400, Zsolt Parragi wrote:

I didn't add tests about this in the patch, but it should also handle
these partition-local cases, e.g.

CREATE TABLE d (a int, b text, CONSTRAINT d_pk PRIMARY KEY (a))
PARTITION BY RANGE (a);
CREATE TABLE d1 PARTITION OF d FOR VALUES FROM (0) TO (100);
ALTER TABLE d1 ADD CONSTRAINT d1_a_def UNIQUE (a) DEFERRABLE;
INSERT INTO d VALUES (1, 'one');
INSERT INTO d VALUES (2, 'two') ON CONFLICT ON CONSTRAINT d_pk DO
UPDATE SET b = EXCLUDED.b;
-- master: ERROR; patched: INSERT 0 1

Nice. Perhaps the tests should be expanded for this pattern? Named
constraints seems also worth caring about as ON CONFLICT allows this
pattern..

Also, now that I took another look into this, a somewhat similar PG19
regression exists outside partitions tables, caused by a different
commit (2bc7e886fc1):

CREATE TABLE t (a int, b text, CONSTRAINT t_u UNIQUE (a));
ALTER TABLE t ADD CONSTRAINT t_nnd UNIQUE NULLS NOT DISTINCT (a);
INSERT INTO t VALUES (NULL, 'one');

INSERT INTO t VALUES (NULL, 'two') ON CONFLICT ON CONSTRAINT t_u DO
UPDATE SET b = 'upd';
-- 18: ERROR: duplicate key value violates unique constraint "t_nnd"
-- (named arbiter t_u sees no conflict on NULLs, insert proceeds,
t_nnd rejects)
-- 19: INSERT 0 1 -> table now (NULL, 'upd')
-- (t_nnd silently used as arbiter user never named; error
swallowed, row updated)

But that seems like a different issue requiring a different fix.

Ugh. This family of failures is super annoying because they do no
require a rebuilt index at all.

2bc7e886fc1 and the commit that has triggered this thread both refer
to the same set of improvements regarding the interactions of rebuilt
indexes and ON CONFLICT, so my take is that if we finish by updating
the same area of the code we could just group both changes together,
but I agree that my line of thoughts may be thin. It seems to me that
we'd better look first at this secondary issue before drawing a
conclusion regarding if both issues should be handled together or
separately.
--
Michael

Hello!

2bc7e886fc1 and the commit that has triggered this thread both refer
to the same set of improvements regarding the interactions of rebuilt
indexes and ON CONFLICT, so my take is that if we finish by updating
the same area of the code we could just group both changes together,
but I agree that my line of thoughts may be thin. It seems to me that
we'd better look first at this secondary issue before drawing a
conclusion regarding if both issues should be handled together or
separately.

This is a "grouped" version. Also, it handles possible collation
issues + provides a set of tests to pin the correct behaviour.

I haven't reviewed the tests too deeply (AI-generated) but they pass
on 18 (expect DO SELECT cases) and on the fixed version, failing on
the pre-fix.

Super-short version:
* move IsIndexCompatibleAsArbiter to index.c with a slight signature change
* handle `indimmediate` in it
* use `equal` for lists instead of list_difference
* use IsIndexCompatibleAsArbiter for both - partitioned and named
constraint cases
* simply infer_arbiter_indexes a bit (reverting part of 2bc7e886fc1b)

Best regards,
Mikhail.

On Tue, 01 Sep 2026, Mihail Nikalayeu <mihailnikalayeu@gmail.com> 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.

[RMT hat]

This one is listed as an open item, but there haven't been any updates to
the thread in a couple of weeks. What is the current status, and can it be
fixed before code freeze for 19beta4 begins on Saturday at noon UTC?

--
nathan

On 2026-Sep-16, Nathan Bossart wrote:

This one is listed as an open item, but there haven't been any updates to
the thread in a couple of weeks. What is the current status, and can it be
fixed before code freeze for 19beta4 begins on Saturday at noon UTC?

I haven't reviewed the proposed fix yet, but I'll strive to get the fix
in before that deadline.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/

On Tue, Sep 01, 2026 at 11:15:09AM +0200, Mihail Nikalayeu wrote:

2bc7e886fc1 and the commit that has triggered this thread both refer
to the same set of improvements regarding the interactions of rebuilt
indexes and ON CONFLICT, so my take is that if we finish by updating
the same area of the code we could just group both changes together,
but I agree that my line of thoughts may be thin. It seems to me that
we'd better look first at this secondary issue before drawing a
conclusion regarding if both issues should be handled together or
separately.

This is a "grouped" version. Also, it handles possible collation
issues + provides a set of tests to pin the correct behaviour.

+/*
+ * 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,

[...]

+	/* number of key attributes must match */
+	if (indexForm1->indnkeyatts != indexForm2->indnkeyatts)
+		return false;

I see that it was like this in the original commit, but isn't it too
restrictive regarding the goal stated in the function comment? If say
there are two unique indexes on columns (a), and (a, b), they have the
same understanding of what tuples will conflict, but the latter one will
not be used as an arbiter index. To be fair, I don't see how this may
become problem in practice, but still.

On 2026-Sep-01, Zsolt Parragi wrote:

On Tue, 01 Sep 2026, Mihail Nikalayeu <mihailnikalayeu@gmail.com> 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)

Pushed, thanks.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
Syntax error: function hell() needs an argument.
Please choose what hell you want to involve.

On 2026-Sep-16, Dmitry Dolgov wrote:

+	/* number of key attributes must match */
+	if (indexForm1->indnkeyatts != indexForm2->indnkeyatts)
+		return false;

I see that it was like this in the original commit, but isn't it too
restrictive regarding the goal stated in the function comment? If say
there are two unique indexes on columns (a), and (a, b), they have the
same understanding of what tuples will conflict, but the latter one will
not be used as an arbiter index. To be fair, I don't see how this may
become problem in practice, but still.

I'm not sure I understand this concern. Do you want to elaborate?

This functionality is there to support having two copies of "the same"
index during REINDEX CONCURRENTLY, and of course the second copy is
going to be identical in definition to the first one.

Maybe you want to propose a different name or a different comment for
this new function?

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/

On Fri, Sep 18, 2026 at 01:54:46PM +0200, Álvaro Herrera wrote:
On 2026-Sep-16, Dmitry Dolgov wrote:

+	/* number of key attributes must match */
+	if (indexForm1->indnkeyatts != indexForm2->indnkeyatts)
+		return false;

I see that it was like this in the original commit, but isn't it too
restrictive regarding the goal stated in the function comment? If say
there are two unique indexes on columns (a), and (a, b), they have the
same understanding of what tuples will conflict, but the latter one will
not be used as an arbiter index. To be fair, I don't see how this may
become problem in practice, but still.

I'm not sure I understand this concern. Do you want to elaborate?

This functionality is there to support having two copies of "the same"
index during REINDEX CONCURRENTLY, and of course the second copy is
going to be identical in definition to the first one.

Maybe you want to propose a different name or a different comment for
this new function?

Yes, I get that. But the way how comment is written seems to emphasize
interchangeability of indexes in general, mentioning REINDEX
CONCURRENTLY as one use case, but not necessarily the only one -- or at
least it's my reading of it. Maybe a better commentary can solve it, but
since it's already pushed, consider it to be an optional nit pick.

On 2026-Sep-18, Dmitry Dolgov wrote:

Yes, I get that. But the way how comment is written seems to emphasize
interchangeability of indexes in general, mentioning REINDEX
CONCURRENTLY as one use case, but not necessarily the only one -- or at
least it's my reading of it. Maybe a better commentary can solve it, but
since it's already pushed, consider it to be an optional nit pick.

I'll gladly take your nit pick.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
<inflex> really, I see PHP as like a strange amalgamation of C, Perl, Shell
<crab> inflex: you know that "amalgam" means "mixture with mercury",
more or less, right?
<crab> i.e., "deadly poison"