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

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