alt Hacker NewsSince I don't often write raw SQL, I can only assume the author named their CTE `deleted_tasks` to elucidate that the query might delete multiple items. Otherwise, it makes little sense, for they intended to "pop" a single row, and yet their aptly named `deleted_tasks` ended up removing more than one!
The query reads to me like a conceptual mish-mash. Without understanding what the innermost `SELECT` was meant to accomplish, I'd naturally interpret the `WHERE id IN (...)` as operating on a set. But the most sacrilegious aspect is the inclusion of `FOR UPDATE SKIP LOCKED`. It assumes a very specific execution order that the query syntax doesn't actually enforce.
Am I right to think that not avoiding lock contention, i.e. omitting `SKIP LOCKED` would have actually produced the intended result?
DELETE with an overly-broad operator in the WHERE clause and no explicit limit: check, non-trivial subquery in the WEHERE: check. This should not have passed code review, let alone have been caught in production.
I will give OP the benefit of the doubt and say that automated testing did not catch this because the optimisations depend on table statistics, and not because it was not appropriately covered.