SELECT FOR UPDATE returns zero rows

From: Dima Pavlov <imyfess(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: SELECT FOR UPDATE returns zero rows
Date: 2016-09-17 18:49:41
Message-ID: CAHt_LuuiL+Nij2phzh+D4jT17f6bNxtABDL3iTs2Onaf9wGj5g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Why SELECT FOR UPDATE returns 0 rows in scenario below? But if I just
execute sql query from second transaction it always returns 1 row.

TRANSACTION 1:
--------------------------

BEGIN;
UPDATE t1 SET t1c3 = "string_value_1" WHERE t1c1 = 123456789;

-- Query returned successfully: one row affected, 51 msec execution time.

TRANSACTION 2:
--------------------------

WITH
cte1 AS (
SELECT t2c2 FROM t2 WHERE t2c1 = "string_value_2"
),

cte2 AS (
SELECT * FROM t1
WHERE
t1c1 = 123456789
AND t1c2 = (SELECT t2c2 FROM cte1)
FOR UPDATE
)

SELECT * FROM cte2

-- Waiting

TRANSACTION 1:
--------------------------

COMMIT;

-- Query returned successfully with no result in 41 msec.

TRANSACTION 2:
--------------------------

-- Returned 0 rows

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Levenson, Elliott I. 2016-09-17 22:28:46 :
Previous Message Kiran 2016-09-10 10:23:13 Trigger is not working for Inserts from the application