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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andy S <gatekeeper(dot)mail(at)gmail(dot)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
>