BUG #14328: SELECT FOR UPDATE doesn't return existing row

From: imyfess(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14328: SELECT FOR UPDATE doesn't return existing row
Date: 2016-09-19 16:01:36
Message-ID: 20160919160136.1348.55251@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14328
Logged by: Dima Pavlov
Email address: imyfess(at)gmail(dot)com
PostgreSQL version: 9.4.4
Operating system: Windows 10
Description:

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

ENVIRONMENT:
-------------
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);

TRANSACTION 1:
--------------
BEGIN;
-- This query updates t1c1 to its current value, it doesn't change
anything
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-bugs by date

  From Date Subject
Next Message Andomar 2016-09-19 16:18:49 Re: BUG #14328: SELECT FOR UPDATE doesn't return existing row
Previous Message Tony Wecker 2016-09-19 14:35:50 pgdg94 conflicts with RHEL 6.8