Re: BUG #18279: Duplicate key violation and Deadlock when using ON CONFLICT/DO UPDATE with multiple unique indexes

On Wed, Jan 10, 2024 at 4:28 AM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> I have a table with a primary key and a unique index.
> First, I ran an INSERT query and then several INSERT INTO ON CONFLICT DO
> UPDATE queries in parallel.
> This will almost always result in Duplicate key violations, and sometimes
> Deadlocks.
> When I removed the unique index, there was no Duplicate key violation or
> Deadlock.

This is not a bug. The statements you've shown use "ON CONFLICT (id)
DO UPDATE", which will make the primary key the arbiter index -- the
index used for upserting. The duplicate violation errors relate to the
unique index, though.

In general the implementation only uses one arbiter index to decide
whether to insert or to update each row (barring certain edge cases
where the table has multiple equivalent/redundant unique indexes).
It's possible that you'll get the behavior you expect by specifying
"ON CONFLICT (user_id, date) DO UPDATE" instead -- though I can't be
sure of that.

--
Peter Geoghegan