Skip site navigation (1) Skip section navigation (2)

Re: BUG #6608: SELECT FOR UPDATE not obtaining row exclusive locks in CTEs

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: duncan(dot)burke(at)orionvm(dot)com(dot)au
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6608: SELECT FOR UPDATE not obtaining row exclusive locks in CTEs
Date: 2012-04-25 22:02:41
Message-ID: 5673.1335391361@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
duncan(dot)burke(at)orionvm(dot)com(dot)au writes:
> I found that running a SELECT FOR UPDATE query in a CTE does not block
> simultaneous transactions from running the same query.

The reason this test case doesn't do anything:

> CREATE FUNCTION lock_0(int) returns int as $$
>     WITH locked as (
>         SELECT 1 FROM foo
>         WHERE x = $1
>         FOR UPDATE)
>     SELECT 1
> $$ LANGUAGE SQL;

is that the CTE is unreferenced.  While we force INSERT/UPDATE/DELETE
CTEs to be executed even when not referenced, that does not apply to
SELECTs; see
http://www.postgresql.org/docs/9.1/static/queries-with.html
which states "execution of a SELECT is carried only as far as the
primary query demands its output".

If I change the function to say "WITH ... SELECT * FROM locked" then
blocking occurs as expected.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: piyushmDate: 2012-04-26 01:32:49
Subject: BUG #6614: pg_dump not working for tables with japanese name
Previous:From: Tom LaneDate: 2012-04-25 21:28:24
Subject: Re: BUG #6605: wrong type cast from timestamp to timestamptz

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group