Re: BUG #15556: Duplicate key violations even when using ON CONFLICT DO UPDATE

On Mon, Dec 17, 2018 at 11:08 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> The problem is that unique index inference isn't sophisticated enough
> to recognize that the primary key ought to be inferred alongside the
> two other unique indexes, which are expression indexes. This is hardly
> surprising -- why would an expression index need to be created that
> was exactly equivalent to the primary key?

By the way, EXPLAIN ANALYZE INSERT ... ON CONFLICT will actually show
you which unique indexes/constraints have been inferred from the
target columns/expressions that appear in parenthesis. If two unique
indexes use different columns, or are otherwise equivalent based on
convention rather than on the semantics, then they're definitely not
going to be recognized as equivalent by the inference process.

You're not supposed to be able to UPDATE on a conflict on more than
one unique index, really. The general idea with inference is to avoid
unpleasant surprises when there are two indexes that enforce basically
the same constraint, such as when a bloated unique index is replaced
by creating a new index with CREATE UNIQUE INDEX CONCURRENTLY, before
the original is dropped. That's what I meant about this being an edge
case -- this hardly ever happens. So, yes, you can have multiple
unique indexes inferred, but it doesn't matter which one you take the
alternative UPDATE path on, because the rules of inference ensure that
it cannot matter. We can inferred multiple indexes precisely because
they'll all have the same conflicts.

I got asked about multiple inference specifications in one statement
quite a few times back when ON CONFLICT originally went in. That's not
how it's supposed to be used -- what happens when *both* constraints
are violated at once, in different ways? Just use multiple INSERT ...
ON CONFLICT statements instead.

--
Peter Geoghegan