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