UPSERT (INSERT INTO ... ON CONFLICT) with multiple unique indexes - concurrency & unique constraint violation

Question

I have the following (simplified) table examples in a PostgreSQL 11 database:

| Column  | Type 
| id      | uuid
| core_id | character varying(255)
| name    | character varying(255)

Indexes: 
  "examples_core_id_pkey" PRIMARY KEY, btree (core_id)
  "examples_core_id_key" UNIQUE CONSTRAINT, btree (core_id)
  "examples_id_unique" UNIQUE CONSTRAINT, btree (id)

Now, let's consider the following SQL statement:

INSERT INTO examples
( id, core_id, name)
VALUES
( $id, $coreId, $name) 
ON CONFLICT (core_id)
DO UPDATE 
SET 
  name = $name  

When I try two inserts

• Insert 1 ('abc', 'abc', 'somename')
• Insert 2 ('abc' 'abc', 'somename')

I sometimes get a SequelizeUniqueConstraint error:

duplicate key value violates unique constraint examples_id_unique

I don't understand why this is happening because I am specifying core_id in the ON CONFLICT clause. Thus, I am interpreting it so that if there's a lack of uniqueness on core_id, just perform the UPDATE. I was thinking postgres would just check the arbitrer index (core_id) in those cases and run the update.

Based on postgres docs v11

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified by conflict_target is violated, the alternative conflict_action is taken.

I wonder if this is a concurrency problem because in a production environment, I get the following flow:

• Receive message 1 and message 2 with the same content at the same time.
• Message 1 passes, Message 2 fails with the sequelize validation error.
• Message 2 gets reprocessed, then passes.

This seems very similar to this question INSERT ON CONFLICT DO UPDATE SET (an UPSERT) statement with a unique constraint is generating constraint violations when run concurrently but haven't found a solution to this problem yet.

Answer 1

The "arbiter index" chosen by the UPSERT is determined by the "conflict target" declared in the ON CONFLICT clause. The manual:

conflict_target can perform unique index inference. When performing inference, it consists of one or more index_column_name columns and/or index_expression expressions, and an optional index_predicate. All table_name unique indexes that, without regard to order, contain exactly the conflict_target-specified columns/expressions are inferred (chosen) as arbiter indexes.

In your case, ON CONFLICT (id, core_id) determines (only!) the multicolumn unique constraint id_core_id_unique. But not the other three unique constraints / indexes. The unique violation is only suppressed for the chosen arbiter index(es). Determining an alternative action would be ambiguous for multiple differing constraints.

You actually have 4 unique indexes:

  "examples_id_pkey" PRIMARY KEY, btree (core_id)
  "examples_core_id_key" UNIQUE CONSTRAINT, btree (core_id)
  "examples_id_unique" UNIQUE CONSTRAINT, btree (id)
  "id_core_id_unique" UNIQUE CONSTRAINT, btree (core_id, id)

The constraint (and index) examples_core_id_key is 100% redundant, because the PK index is already implemented with an identical unique index. Drop that constraint at your earliest convenience (and implicitly also the index).

That leaves two more unique indexes that will not tolerate duplicate input. The only way to catch unique violations from multiple different UNIQUE (and EXCLUSION) constraints is the generic clause ON CONFLICT DO NOTHING. See:

• How to use RETURNING with ON CONFLICT in PostgreSQL?

Or you get rid of the other overlapping constraints. Do you really need all (remaining) three? A UNIQUE constraint on (core_id, id) makes little sense for a table with a PRIMARY KEY on (core_id). Multiple UNIQUE constraints are a rare exception for a halfway normalized design to begin with ...

---

You changed the question after my answer. I am not writing another answer, but some variation of this will be the perfect solution to deal with multiple separate UNIQUE constraints:

• Use multiple conflict_target in ON CONFLICT clause