logoalt Hacker News

swid • today at 5:07 AM • 1 reply • view on HN

I think arguably there is no bug here, but the blog doesn't do a good job of explaining the issue and their fix may work because they remove the CTE and switch to `=`, but they prove they don't understand why the difference when they suggest "using id IN (...) might still be necessary"; if you do that, then the problem will return.

There are two factors here.

The subquery

    SELECT id FROM task_queue WHERE queue_group_id = 15 FOR UPDATE SKIP LOCKED LIMIT 1
can return different ids each time you run it. If it was ordered, then it would always return the same id and if postgres optimized in a way that it runs more than once it would just get the same result each time anyway.

Otherwise, you need to force postgres to evaluate your subquery exactly once by materializing it. There are different ways this might be accomplished - the blog post does this incidentally by using `=`. But it is not the only way to tell postgres that.

For instance, like this. But it is fragile - without AS MATERIALIZED, it could be run more than once.

    WITH candidate AS MATERIALIZED (
      SELECT id FROM task_queue WHERE queue_group_id = 15 FOR UPDATE SKIP LOCKED LIMIT 1
    )
    DELETE FROM task_queue t USING candidate c WHERE t.id = c.id
    RETURNING t.item_id;

Replies

shayonj • today at 1:15 PM

That's correct, if you want to keep using CTE, the gurantee can be achieved via something like this

WITH deleted_tasks AS MATERIALIZED (

  SELECT id 
  FROM task_queue
  WHERE queue_group_id = 5
  AND status = 'pending'
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)

DELETE FROM task_queue WHERE id IN (SELECT id FROM deleted_tasks) RETURNING item_id, id;