Re: Partitioning vs ON CONFLICT

On Fri, Mar 31, 2017 at 4:47 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> /*
> * Open partition indices (remember we do not support ON CONFLICT in
> * case of partitioned tables, so we do not need support information
> * for speculative insertion)
> */
>
> Part of the question here is definitional. Peter rightly pointed out
> upthread that we support INSERT .. ON CONFLICT in an inheritance
> situation, but that is different, because it infers whether there is a
> conflict in the particular child into which you are trying to insert,
> not whether there is a conflict across the whole hierarchy.

I would say that it doesn't infer anything at all, in the sense that
infer_arbiter_indexes() returns very early. It's then implied that
whatever constraint index OIDs that the executor later happens to find
will have speculative insertions. The optimizer doesn't try to predict
what that will look like within the executor, or even on a foreign
postgres_fdw server in the case of foreign tables. Foreign table
indexes are not known to the local installation, which is one reason
for this. You could INSERT ... ON CONFLICT DO NOTHING (no inference
specification) into an ordinary table with no indexes, and that also
works fine. (It's just silly.)

> More or
> less by definition, trying to insert into the room of the partitioning
> hierarchy is a different beast: it should consider uniqueness across
> the whole hierarchy in determining whether there is a conflict and, as
> Simon pointed out in the second email on the thread, we lack a
> mechanism to do that.

In my opinion, for the very limited ON CONFLICT DO NOTHING + no
inference specification case, the implementation should not care about
the presence or absence of unique indexes within or across partitions.
It might be sloppy for an application developer to do a whole lot of
this, but that's not a judgement I think we can make for them.

I don't feel strongly about this, though.

--
Peter Geoghegan