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
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 |