logoalt Hacker News

swid • yesterday at 6:13 AM • 1 reply • view on HN

I wrote this to a deleted comment, but even if the CTE was materialized, the subquery of the CTE would still not be...

For instance, with the stand alone query:

  DELETE … WHERE id IN (
    SELECT id … LIMIT 1 FOR UPDATE SKIP LOCKED
  )
the planner is free to turn that IN ( subquery ) into a nested‐loop semi‐join, re-executing the subquery as many times as it deems optimal. Therefore it can delete more than 1 row.

Replies

Sesse__ • yesterday at 9:31 AM

More to the point: The SQL standard doesn't talk about semijoins at all, it conceptually evaluates the WHERE clause for each and every row. So the question is whether the subquery is guaranteed to give the same result each and every time. That would presumably depend on the transaction isolation level in use, except that SKIP LOCKED (which is nonstandard, from what I know) presumably calls off all bets anyway.