Postgres deadlock happened with multi-column unique constraint when upsert single record

Question

Abstract

1. What is 'speculative token'?
2. Why deadlock is happened? I've tried upsert just single row with insert.. with on conflict.. do update syntax.
3. There was UNIQUE INDEX index_foo_date ON table_a USING btree (foo_id, date). This error is disappeared after I got rid of UNIQUE constraint of (foo_id, date). But I'd like to know exact reason.

Errors

I've got so many these errors at the same time. In my code, there is just upsert single column syntax.

DataAccessException

SQL [insert into "public"."table_a" ("id", "foo_id", "date", "counter")
  values ('1:2023-12-17', '5', cast('2023-12-17' as date), 1)
  on conflict ("id") do update set "counter" = ("public"."table_a"."counter" + 1)];
  ERROR: deadlock detected
  Detail: Process 3208 waits for ShareLock on transaction 2964346221; blocked by process 2511.
          Process 2511 waits for ShareLock on speculative token 3 of transaction 2964346220;
          blocked by process 3208.
  Hint: See server log for query details.
  Where: while inserting index tuple (12512,98) in relation "index_foo_date"

SQL [insert into "public"."table_a" ("id", "foo_id", "date", "counter")
  values ('1:2023-12-17', '5', cast('2023-12-17' as date), 1)
  on conflict ("id") do update set "counter" = ("public"."table_a"."counter" + 1)];
  ERROR: duplicate key value violates unique constraint "index_foo_date"
  Detail: Key (foo_id, date)=(5, 2023-12-17) already exists.

SQL [insert into "public"."table_a" ("id", "foo_id", "date", "counter")
  values ('1:2023-12-17', '5', cast('2023-12-17' as date), 1)
  on conflict ("id") do update set "counter" = ("public"."table_a"."counter" + 1)];
  ERROR: deadlock detected
  Detail: Process 3034 waits for ShareLock on speculative token 7 of transaction 2964312410; blocked by process 2994.
          Process 2994 waits for ShareLock on transaction 2964320791;
          blocked by process 3034.
  Hint: See server log for query details.

More Info

1. Table

CREATE TABLE table_a (
  id CHARACTER VARYING PRIMARY KEY NOT NULL,
  foo_id CHARACTER VARYING REFERENCES foo(id) NOT NULL,
  date DATE NOT NULL,
  counter INTEGER NOT NULL DEFAULT 0
);

CREATE UNIQUE INDEX index_foo_date ON table_a USING btree (foo_id, date);

2. id has foo_id:date form such as '5:2023-12-17'

My thought

1. As a result of my local testing,

   • when a psql sessionA had ExclusiveLock, other sessions(sessionB, sessionC) are blocked and waiting to get ShareLock.
   • When sessionA got commit; sessionB got ExclusiveLock.
   • And then sessionC worked same. so I don't know why...

2. I've got some keywords.

   • speculative token / speculative insertion lock / sepculative-execution
   • unique composite index

3. I feel like it's related to unique constraint about composite fields foo_id and date. But I can't figure out exact reason.