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

On 2026-Sep-01, Zsolt Parragi wrote:

> On Tue, 01 Sep 2026, Mihail Nikalayeu <mihailnikalayeu(at)gmail(dot)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)