Re: BUG #14526: no unique or exclusion constraint matching the ON CONFLICT

On Tue, Feb 7, 2017 at 3:57 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> btw ... not relevant to this particular complaint, but I notice that
> infer_arbiter_indexes will accept an index that is indisvalid and
> indisunique, but should it be checking indimmediate as well? That is,
> does the ON CONFLICT code work if the uniqueness checks are deferred?
> I could not find any regression tests exercising such a case.

It won't work with deferrable constraints (even when immediate
enforcement is in effect, so obscure reasons). Enforcement occurs in
the executor -- see ExecCheckIndexConstraints().

You may recall that I wrote a refactoring patch that attempted to make
the situation clearer, which Heikki didn't like. Currently, the
constant UNIQUE_CHECK_PARTIAL is sort of overloaded to also be used
with speculative insertion, which, aside from being ugly, has various
minor practical disadvantages.

--
Peter Geoghegan