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

On Fri, Nov 13, 2020 at 11:40 AM Andy S <gatekeeper(dot)mail(at)gmail(dot)com> wrote:

> That's then either a docs bug.
>
[...]

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

Please don't top-post. The convention here is to inline or bottom-post.

I agree that the overview statement quoted should be qualified. The
existing behavior is that the table listed in the insert is the table upon
which the ON CONFLICT arbiter constraint must exist [1]. This effectively
negates the benefit of leveraging the partitioning scheme as a form of
uniqueness enforcement in lieu of indexed columns. IIUC, the additional
partition constraint is still useful for select queries so its creation
still has merit and leverages that particular benefit of partitioning. But
today, you cannot fully benefit from that dynamic if you wish to leverage
ON CONFLICT during data insertion. An improvement there isn't something
that could be back-patched.

David J.

[1] From the INSERT documentation: "All table_name unique indexes that,
without regard to order, contain exactly the conflict_target-specified
columns/expressions are inferred (chosen) as arbiter indexes."