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

[pg-hub-mirror[bot]]

Read-only mirror. Join the conversation on pgsql-hackers; activity here is not sent upstream.

• Original author: Nathan Bossart <nathandbossart(at)gmail(dot)com>
• Mailing list: pgsql-hackers
• Message-ID: aqq48iKIUbOuxT1q@nathan
• Original email

---

[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

[pg-hub-mirror[bot]]

Alvaro Herrera <alvherre(at)kurilemu(dot)de> via pgsql-hackers · original email

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/

[pg-hub-mirror[bot]]

Dmitry Dolgov <9erthalion6(at)gmail(dot)com> via pgsql-hackers · original email

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

2bc7e88 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.

[pg-hub-mirror[bot]]

A patch is now available as PR #413.