Based on my understanding, your comment is the most accurate in this thread.

However, I think it's telling that almost every single comment here has a different understanding of how PostgreSQL works, and nobody can link to any documentation that conclusively addresses this case. I think that indicates that this is a failing if not of implementation, then of documentation.

For example: another comment says that `=` forces PostgreSQL to evaluate the subquery exactly once. I couldn't find any justification for that statement. Is it guaranteed? I don't know.

Another comment says "if only the subquery was ordered then the result would be consistent", but AFAIK, this is not true in the presence of "SKIP LOCKED".

I think the problem stems from the fact that the SQL standard is written from an academic point of view: the ordering of execution of subqueries is not defined because it is irrelevant within the relational algebra, so optimizations which change the execution order are just that: optimizations, with no observable side effects.

Real database systems do not actually have the nice algebraic properties of the relational algebra... The same subquery may give different results over multiple executions, etc. Given this, it's important that the database system give sufficient and sufficiently clear guarantees such that they can still be used correctly, and so that the optimizations implemented by the system can be justified.

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.

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

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

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;

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.

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.

> it seems like the subquery is getting re-run on every single row of the task queue table

Y'all might find the term "correlated subquery" helpful here.

When I want them, I always get them. Sometimes I get them when I don't want them. Sometimes it's even my fault, not the result of the query planner looking at me sideways.

"I'm really not sure what motivated the author to create the CTE in the first place, as the final select seems to be essentially the identity function."

We're likely seeing a simplified example. All queries should be able to be embedded in a CTE without integrity loss.

God gave us Postgres to teach humanity that everything is an INSERT.

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.

in general end to end tests/whole system tests which mock/stub as little as possible and are focused on testing whole use-case flows is a good starting point (sadly in many teams also a constant uphill battle from convincing PM over allocate time to bootstrap it to making CI harder due to basically having to bootstrap a whole testing system including db etc. to educating devs to write such tests properly, instead of writing unit tests at a end to end test level (which rarely ends well).

Optimal you then combine this with automatic collection and analysis of log traced to detect performance regression (which is hard but just because of the involved tooling but now it needs a CI env with reliable perf. characteristics e.g. not GitHub hosted GitHub action runners).

Through in this case the problem is a trap in the behavior of `IN (...)` sub queries which probably is not specific to WITH clause and might be as deeply rooted as the SQL standard but a you have to pay for being able to read it I'm not sure.

aren't they the same in Postgres?