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

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