BUG #18279: Duplicate key violation and Deadlock when using ON CONFLICT/DO UPDATE with multiple unique indexes - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18279
Logged by:          Yeongeun Noh
Email address:      nye7181@gmail.com
PostgreSQL version: 16.1
Operating system:   MacOS 13.1
Description:

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.

I have two questions regarding this situation.

1. Since I had already inserted a row, I expected the INSERT INTO ON
CONFLICT DO UPDATE queries to only update it.
However, if there is a unique index, it seems to try to insert rather than
update.
2. Each transaction executes only one query for one row. How does a deadlock
occur?

Here's the reproduction code.
```sql
CREATE TABLE email_stats (
  id varchar NOT NULL PRIMARY KEY,
  user_id integer NOT NULL,
  date date NOT NULL,
  count integer NOT NULL
);
CREATE UNIQUE INDEX user_id_date ON email_stats(user_id, date);
```

```sh
#!/bin/bash
CONNECTION_COUNT=500
QUERY_COUNT_PER_CONNECTION=10
INSERT_QUERY="INSERT INTO email_stats (id, user_id, date, count) VALUES
('2024-01-01_1', 1, '2024-01-01', 1);"
ON_CONFLICT_QUERY="INSERT INTO email_stats (id, user_id, date, count) VALUES
('2024-01-01_1', 1, '2024-01-01', 1) ON CONFLICT (id) DO UPDATE SET count =
email_stats.count + 1;"

perform() {
  echo "Start"
  insert_first
  insert_multiple
}
insert_first() {
  psql -h localhost -p 5432 -U channel -d channel -c "$INSERT_QUERY"
}
insert_multiple() {
  echo "creating connections..."
  for ((i = 0; i < $CONNECTION_COUNT; i++)); do
    (
      echo "inserting records for connection $i..."
      for ((j = 0; j < $QUERY_COUNT_PER_CONNECTION; j++)); do
        psql -h localhost -p 5432 -U channel -d channel -c
"$ON_CONFLICT_QUERY" 1> /dev/null
      done
    ) &
  done
  wait
}

perform
```

Here's the server error log.
```
2024-01-10 06:56:07.473 UTC [4331] ERROR: 23505: duplicate key value
violates unique constraint "user_id_date"
2024-01-10 06:56:07.473 UTC [4331] DETAIL: Key (user_id, date)=(1,
2024-01-01) already exists.
2024-01-10 06:56:07.473 UTC [4331] LOCATION: _bt_check_unique,
nbtinsert.c:666
2024-01-10 06:56:07.473 UTC [4331] STATEMENT: INSERT INTO email_stats (id,
user_id, date, count) VALUES ('2024-01-01_1', 1, '2024-01-01', 1) ON
CONFLICT (id) DO UPDATE SET count = email_stats.count + 1;
2024-01-10 06:56:07.645 UTC [4542] ERROR: 23505: duplicate key value
violates unique constraint "user_id_date"
2024-01-10 06:56:07.645 UTC [4542] DETAIL: Key (user_id, date)=(1,
2024-01-01) already exists.
2024-01-10 06:56:07.645 UTC [4542] LOCATION: _bt_check_unique,
nbtinsert.c:666
2024-01-10 06:56:07.645 UTC [4542] STATEMENT: INSERT INTO email_stats (id,
user_id, date, count) VALUES ('2024-01-01_1', 1, '2024-01-01', 1) ON
CONFLICT (id) DO UPDATE SET count = email_stats.count + 1;
2024-01-10 06:56:11.346 UTC [5064] ERROR: 40P01: deadlock detected
2024-01-10 06:56:11.346 UTC [5064] DETAIL: Process 5064 waits for ShareLock
on transaction 6520; blocked by process 5277.
Process 5277 waits for ShareLock on speculative token 1 of transaction 6386;
blocked by process 5064.
Process 5064: INSERT INTO email_stats (id, user_id, date, count) VALUES
('2024-01-01_1', 1, '2024-01-01', 1) ON CONFLICT (id) DO UPDATE SET count =
email_stats.count + 1;
Process 5277: INSERT INTO email_stats (id, user_id, date, count) VALUES
('2024-01-01_1', 1, '2024-01-01', 1) ON CONFLICT (id) DO UPDATE SET count =
email_stats.count + 1;
2024-01-10 06:56:11.346 UTC [5064] HINT: See server log for query details.
2024-01-10 06:56:11.346 UTC [5064] CONTEXT: while inserting index tuple
(34,3) in relation "user_id_date"
2024-01-10 06:56:11.346 UTC [5064] LOCATION: DeadLockReport,
deadlock.c:1130
2024-01-10 06:56:11.346 UTC [5064] STATEMENT: INSERT INTO email_stats (id,
user_id, date, count) VALUES ('2024-01-01_1', 1, '2024-01-01', 1) ON
CONFLICT (id) DO UPDATE SET count = email_stats.count + 1;
```