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

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

The constraint raised, it's specification equals and matches. It is ignored
and raised exception is passed through. No, it's a bug;

Nevertheless, suppose I have a top-level partition bound primary key which
covers all 4 columns and then my INSERT has all 4 of them in ON CONFLICT
specification. This works as intended but: the exact index raised was not
the one inferred since the top-level partition itself is not a default
partition hence always empty hence it's index is always empty; an empty
index cannot raise uniqueness violation exceptions then the one raised must
be the leaf partition's (whichever it is). How could it not be a bug? Also:
the certain partition to which data is to be inserted is computed at query
planning stage the very stage where the planner could also find out if an
index matching the given specification could be inferred since it's the
only index that matters.

Also:
https://www.postgresql.org/docs/11/ddl-partitioning.html
> The partitioning substitutes for leading columns of indexes, reducing
index size and making it more likely that the heavily-used parts of the
indexes fit in memory.
That is exactly what this partitioning scheme is made for. Yet ridiculous
constraint matching punishes for benefiting from partitioning.
> Updating the partition key of a row might cause it to be moved into a
different partition where this row satisfies the partition bounds.
> BEFORE ROW triggers, if necessary, must be defined on individual
partitions, not the partitioned table.
What else could witness even better the top level table constraint has no
sense since it is never even examined, but instead the leaf partition exact
computed definition matters.

On Fri, Nov 13, 2020 at 7:03 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > insert into tbl values (1, 1, 1, 1, 'b') on conflict (part_key4) do
> update
> > set values_columns = excluded.values_columns;
> > -- ERROR: there is no unique or exclusion constraint matching the ON
> > CONFLICT specification
>
> I see no bug here. The partitioned table indeed does not have any
> such index. Moreover, if you had tried to make one, you would have
> gotten
>
> ERROR: unique constraint on partitioned table must include all
> partitioning columns
> DETAIL: PRIMARY KEY constraint on table "tbl" lacks column "part_key1"
> which is part of the partition key.
>
> The short answer here is that uniqueness constraints on the individual
> partitions are not a substitute for a constraint on the whole partitioned
> table.
>
> regards, tom lane
>