Re: BUG #16714: INSERT ON CONFLICT DO UPDATE fails to infer constraint if it's not at top-level partition - Mailing list pgsql-bugs

From Andy S
Subject Re: BUG #16714: INSERT ON CONFLICT DO UPDATE fails to infer constraint if it's not at top-level partition
Date
Msg-id CAFAcjJOECU-Wr+cz1qZV-G_-U5DF0DaD0oRqjcjrDU2uGY0XQw@mail.gmail.com
Whole thread Raw
In response to Re: BUG #16714: INSERT ON CONFLICT DO UPDATE fails to infer constraint if it's not at top-level partition  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
Again that's not true:

insert into tbl values (1, 1, 1, 1, 'b');
-- ERROR:  duplicate key value violates unique constraint
"tbl_p1_p2_p3_0_pkey"
-- DETAIL:  Key (part_key4)=(1) already exists.

The exact primary key violation triggers, this exact primary key spec is then defined to no success. Yes, I insist this arbiter constraint spec equals to the one in error message. In my actual DDL primary key is defined at the level prior to the leaf partition set (so I don't mess with the PK each leaf partition), though the exact PK on columns 3 and 4 is a very common selection criteria within the application so it helps. Adding other columns to PK would add no selectivity (though could poison the index) and rather wastes space within each index instance since a single index instance uniqueness of columns 1 and 2 values is guaranteed by DDL.

Requiring a user to define a wide value range index when it does not help to guarantee uniqueness and/or querying at least violates the description of partitioning I previously cited.

On Fri, Nov 13, 2020 at 10:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andy S <gatekeeper.mail@gmail.com> writes:
> That's then either a docs bug. The documentation states:
>> For each individual row proposed for insertion, either the insertion
>> proceeds, or, if an *arbiter* constraint or index specified by
>> *conflict_target* is violated, the alternative *conflict_action* is taken

Yeah, but you did not specify a valid arbiter constraint.  ON CONFLICT is
not a get-out-of-jail-free card; it will not trap any error whatsoever,
only a detected unique-constraint violation.

What you probably want here is to declare (part_key1, part_key4)
as the primary key of the partitioned table, and use that in the
ON CONFLICT spec.  That will save you from having to make constraints
on the individual partitions, too.

                        regards, tom lane

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #16714: INSERT ON CONFLICT DO UPDATE fails to infer constraint if it's not at top-level partition
Next
From: Devrim Gündüz
Date:
Subject: Re: BUG #16716: postgresql11-devel pckage from Refreshing service 'spacewalk' complains about missing