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

Thanks for the answer.

If the primary key was inferred as the arbiter index, shouldn't it be
UPDATE because the arbiter index was conflicted?
And when parallelism is low, the duplicate violations don't occur.

When I ran ON CONFLICT (user_id, date) DO UPDATE as you suggested, a
duplicate violation on primary key occurred.
However, the deadlock seems to disappear.
```
ERROR: duplicate key value violates unique constraint "email_stats_pkey"
DETAIL: Key (id)=(2024-01-01_1) already exists.
```

2024년 1월 11일 (목) 오전 12:12, Peter Geoghegan <pg(at)bowt(dot)ie>님이 작성:

> 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
>