> 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
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: piyushm||Date: 2012-04-26 01:32:49|
|Subject: BUG #6614: pg_dump not working for tables with japanese name|
|Previous:||From: Tom Lane||Date: 2012-04-25 21:28:24|
|Subject: Re: BUG #6605: wrong type cast from timestamp to timestamptz |