Alban Hertroys <haramrae@xxxxxxxxx> writes: >> On 17 Sep 2021, at 8:32, Roman Guryanov <r.guryanov.integrix@xxxxxxxxx> wrote: >> Why does SELECT FOR UPDATE return 0 rows in the scenario below? (execution in transaction) > Most likely the outer select returns 0 rows because you locked the rows you expected in cte2 and didn’t perform an update on those locked rows yet. I might be wrong (ENOCAFFEINE), but I think what is happening is that the UPDATE updates the row and then the FOR UPDATE filter skips the row on the grounds that the row is already-updated-by-self. In an ordinary UPDATE, there's a hard restriction not to update a row already updated in the same command, to avoid possibly-infinite loops if the same row is visited more than once due to join behavior or the like. I think that we use the same semantics in FOR UPDATE, and I'm pretty sure that the two WITH clauses would be treated as all one command. I'd have to say that overall this example is one of the worst bits of SQL I've seen lately. Aside from the issues Alban noted, the "t1c2 = (SELECT t1c2 FROM cte1)" part will fail outright if cte1 returns more than one row, because that's a scalar sub-select not a join. And there's a real question of which WITH clause acts first: yeah, cte2 can't *complete* without running cte1, but it might act partially, including performing the other half of its WHERE. If cte1 were updating t1c1 then I think it'd be pretty close to undefined what results you get. What's the point of doing it like this, rather than just having cte1 return all the columns needed? regards, tom lane
