SELECT FOR UPDATE returns zero rows with CTE

On 17 Sep 2021, at 8:32, Roman Guryanov <r.guryanov.integrix@gmail.com> wrote:

Hello, could you check my problem.
Why does SELECT FOR UPDATE return 0 rows in the scenario below? (execution in transaction)

If delete 'FOR UPDATE', 1 row returned

Test case:
DROP TABLE IF EXISTS
t1;

CREATE TABLE t1 (_pk serial, t1c1 integer, t1c2 integer
, t1c3 text);

insert into t1 (t1c1, t1c2, t1c3) values(123456789, 100, 'string_value_1'
);

(…cut everything related to unused t2…)

WITH
cte1
AS
(
UPDATE
t1
SET t1c3 = 'string_value_1'
WHERE t1c1 = 123456789
returning t1c1, t1c2
),
cte2
AS
(
SELECT * FROM
t1
WHERE t1c1 = 123456789
AND t1c2 = (SELECT t1c2 FROM cte1)
FOR UPDATE
)

SELECT * FROM cte2;

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 suspect your intention for this query is to first lock the rows, then update them and then select them, but instead you start with updating them, then lock those rows after the fact and then you try to select those locked rows.

Also, selecting the updated rows by t1c2 in cte2 seems rather risky, as that is a rather different selection criterium than you use for the actual update. It’s okay for this single-row example, but if you had a table full of data, you would now have locked all rows with the value t1c2 = 100 for update. If that update never happens (or the locking doesn’t get rolled back), well…

Regards,

Alban Hertroys
--
There is always an exception to always.

Alban Hertroys <haramrae@gmail.com> writes:

On 17 Sep 2021, at 8:32, Roman Guryanov <r.guryanov.integrix@gmail.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