Could be a generational thing. There was a time not every query planner would properly eliminate columns based on the final projection and any intermediary copies required more I/O if you included everything. Even now I still find myself following "best practices" for things that haven't been relevant in a decade.

On a somewhat related note...

We've moved to MSSQL due to several reasons including customer demand.

We're experiencing the MSSQL query planner occasionally generating terrible plans for core queries which then gets cached, leading to support calls.

Workaround for now has been to have our query geneator append a fixed-value column which and have it change the value every 10 minutes, as a cache defeat.

Still, surprised the engine doesn't figure this out itself, like try regenerating plans frequently if they contain non-trivial table scans say.

Or just expire cache entries every 15 minutes or so, so a bad plan doesn't stick around for too long.

It's not undefined behavior, it's the fact that the uncorrelated subquery within the CTE doesn't specify an ordering, therefore it cannot be implicitly materialized / evaluated once. Postgres documentation is clear here [1]:

> If sorting is not chosen, the rows will be returned in an unspecified order.

The original query from TFA could've instead just had the uncorrelated subquery moved into a materialized CTE.

[1] https://www.postgresql.org/docs/current/queries-order.html

  DELETE … WHERE id IN (
    SELECT id … LIMIT 1 FOR UPDATE SKIP LOCKED
  )
  

aren't they the same in Postgres?

In DELETE ... RETURNING, RETURNING works like SELECT in a read query, so, yes, item_id is a column in task_queue and the result set is the item_id value of the row deleted.

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;