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:

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

There seems to be a mix-up regarding the uniqueness restriction (a
constraint) and the way it is implemented (unique indexes, wherever they
may exist). That might be partly the documentation's fault for using index
in improper places but I haven't fully checked that out. In any case, the
unique constraint on the partitioned table can very well be enforced even
if that named relation doesn't actually have data nor a backing unique
index. The point of the partitioned table is to abstract that away from
the user.

It would be nice if the ON CONFLICT behavior could be abstracted away as
well, and you are right in your expectation that it would. But it is not a
bug that our implementation is lacking in that area, though it could maybe
be better documented, as evidenced by this thread.

David J.