Re: SELECT FOR UPDATE returns zero rows with CTE

Alban Hertroys <haramrae(at)gmail(dot)com> writes:
>> On 17 Sep 2021, at 8:32, Roman Guryanov <r(dot)guryanov(dot)integrix(at)gmail(dot)com> 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