Locking row for INSERT, still getting duplicate key exception

Question

I am trying to run the following query:

WITH upd AS (
  UPDATE fila_all SET id = id WHERE
  id = (select id from fila_all fa where fa.id not in (select msg_id from fila_consumers where group_name = 'group0') and fa.queue_name = 'myqueue' order by id asc limit 1 for update of fa skip locked)
  returning id
)
INSERT INTO fila_consumers select fa2.id, 'new-worker0', 'group0', 'Running'
FROM fila_all fa2 JOIN upd on upd.id = fa2.id
RETURNING msg_id

id is a primary key on fila_all, and (msg_id, group_name) is a primary key on fila_consumers.

fila_consumers only has four columns, id bigint, worker_name varchar, group varchar, status enum.

I would expect this query to never fail due to a conflict on fila_consumers_pk, because the rows are locked, and skipped, on the update, so two transactions cannot get the same fila_all.id for insertion in fila_consumers. But I get the following error:

duplicate key value violates unique constraint "fila_consumers_pkey"
    Detail: Key (msg_id, group_name)=[...]

I don't want to use ON CONFLICT DO NOTHING, I'd like to understand why this is happening.

All insertions on fila_consumers are based on this statement, multiple instances of this statement run concurrently, but no other statements are executed that insert on this table.

What am I missing here? Is there a way around this?

Answer 1

Assuming the INSERT targets the right columns, which we can't tell without table definition and target definition list.

The statement boils down to this:

WITH cte AS (
   SELECT fa.id, {GROUP}, {worker}, 'Running'
   FROM   fila_all fa
   WHERE  NOT EXISTS (
      SELECT FROM fila_consumers fc
      WHERE  fc.msg_id = fa.id
      AND    fc.group_name = {GROUP}
      )
   AND    fa.queue_name = {queue}
   ORDER  BY fa.id
   LIMIT  1
   FOR    UPDATE SKIP LOCKED
   )
                         -- target definition list!
INSERT INTO fila_consumers (msg_id, group_name, col3, col4)
TABLE  cte
RETURNING msg_id;

You are locking a row in table fila_all (twice over in the original). So multiple instances of the same query cannot insert duplicate rows in table fila_consumers, coming from the same source. Good.

But there is nothing to prevent other concurrent transactions from inserting / updating rows in fila_consumers in the time window between the SELECT and the INSERT.

The expensive empty UPDATE in your original makes this window much bigger than necessary. My version with only SELECT should minimize the problem. And NOT EXISTS is typically faster than NOT IN. See:

• Select rows which are not present in other table

But the problem is still there.

Any particular reason you are trying to avoid INSERT ... ON CONFLICT?
That UPSERT functionality was added to fix your kind of problems.

Related:

• Postgres UPDATE ... LIMIT 1