Re: 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