Thread: SELECT FOR UPDATE returns zero rows with CTE
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 returnedTest case:
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (_pk serial, t1c1 integer, t1c2 integer, t1c3 text);
CREATE TABLE t2 (_pk serial, t2c1 text, t2c2 integer);
insert into t1 (t1c1, t1c2, t1c3) values(123456789, 100, 'string_value_1');
insert into t2 (t2c1, t2c2) values('string_value_2', 100);
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;> 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 updateon 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 youstart 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 thanyou use for the actual update. It’s okay for this single-row example, but if you had a table full of data, you wouldnow have locked all rows with the value t1c2 = 100 for update. If that update never happens (or the locking doesn’tget 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
updateon 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