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

On Mon, Dec 17, 2018 at 1:55 AM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> Below a self reproducing testcase for this behaviour.
>
> I would have expected the statement to either insert or update, but
> receiving
> the duplicate key violation is not something that I expected.
>
> Should I expect a duplicate key violation for the given table and
> statements?

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? Actually, it's impossible
to make this work in principle, because nothing obligates you to
insert the same thing into column i as column j or k -- you've merely
done it that way this one time.

Inference is clever enough to not differentiate based on irrelevant
factors like column ordering among available, equivalent unique
indexes. It's already very sophisticated for a mechanism that's just
there to handle edge cases. I don't see a need for it to become more
sophisticated.

--
Peter Geoghegan