The following bug has been logged on the website: Bug reference: 6608 Logged by: Duncan Burke Email address: duncan.bu...@orionvm.com.au PostgreSQL version: 9.1.3 Operating system: gentoo Description:
I found that running a SELECT FOR UPDATE query in a CTE does not block simultaneous transactions from running the same query. i.e it appears to not be obtaining an exclusive row lock as expected. CREATE TABLE foo ( x int PRIMARY KEY, y int ); INSERT INTO foo VALUES (0,0); CREATE FUNCTION lock_0(int) returns int as $$ WITH locked as ( SELECT 1 FROM foo WHERE x = $1 FOR UPDATE) SELECT 1 $$ LANGUAGE SQL; CREATE FUNCTION lock_1(int) returns int as $$ WITH locked as ( UPDATE FOO SET y = y WHERE x = $1) SELECT 1 $$ LANGUAGE SQL; --run in two simultaneous transactions, lock_0 does not block BEGIN; SELECT lock_0(0); COMMIT; --run in two simultaneous transactions, lock_1 blocks BEGIN; SELECT lock_1(0); COMMIT; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs