2

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.

Improve this question
4
  • 1
    "Don't believe it matters, but I'm running this query with sequelize version 6.35.2". You are typically required to check if the queries you are running work without sequelize, otherwise that does not make it a minimal, reproducible example. Once you have checked, you can strip sequelize from your question or add the precision that query XXX works but not once executed from sequelize. Commented Feb 10, 2024 at 0:48
  • 1
    Postgres 11 has reached EOL in 2023. Upgrade to a supported version at your earliest convenience! Commented Feb 10, 2024 at 1:30
  • @Atmo great callout, let's remove sequelize from the equation here. I don't think it matters. I see another person is running into this stackoverflow.com/questions/73164161/… ... unclear to me what the solution is yet. Commented Feb 14, 2024 at 4:54
  • I find DO UPDATE SET name = $name very weird. Any reason why you don't use the special (and documented) excluded table, i.e. DO UPDATE SET name = Excluded.name? Commented Feb 14, 2024 at 10:25

1 Answer 1

Reset to default
2

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:

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:

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hey, first, thanks for the detailed answer. I have removed id_core_id_unique for now but I still need at least 2 unique constraints on core_id (pk) and id (unique). I guess I'm struggling with : 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 How do I perform an upsert (change the record in the db) when potentially 2 constraints are violated ? I don't want to use DO NOTHING because I want to update the record.
@Rose: UPSERT only allows to update a single conflicting row with ON CONFLICT ... DO UPDATE, but there can be two in this case. (Updating both would create another dupe!) What to do in this case? BTW, I hope you've also dropped examples_core_id_key.
Yes, it does seem like a good callout but right now, its not quite straightforward to remove this from my prod db because other foreign keys depend on it. Currently examples_core_id_key does not seem to be the constraint that gets violated at the moment, so while removing it might do good in terms of avoiding redundancy, I don't believe it will fix the problem. I have done some further digging and I actually believe this post is very similar to what I'm experiencing stackoverflow.com/questions/73164161/….
Also updated my example above with the latest setup I have.
The problem: you morphed your question into a different one, obsoleting my given answer. If I was your paid consultant, that would be an ok workflow. But this is a Q/A site. My answer isn't just for you, it's for the general public. I put in some effort to give you (and the interested audience) a detailed answer. For a new question, please ask a new question. Edits to your question should aim to clarify the original question, not change it into a new one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.