On Fri, Mar 31, 2017 at 4:47 PM, Robert Haas <robertmh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers