Обсуждение: BUG #6608: SELECT FOR UPDATE not obtaining row exclusive locks in CTEs

The following bug has been logged on the website:

Bug reference:      6608
Logged by:          Duncan Burke
Email address:      duncan.burke@orionvm.com.au
PostgreSQL version: 9.1.3
Operating system:   gentoo
Description:=20=20=20=20=20=20=20=20

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 =3D $1
        FOR UPDATE)
    SELECT 1
$$ LANGUAGE SQL;

CREATE FUNCTION lock_1(int) returns int as $$
    WITH locked as (
        UPDATE FOO
        SET y =3D y
        WHERE x =3D $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;

duncan.burke@orionvm.com.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