Hi developers,
Concurrently updating an updatable view seems to cause
an unexpected result. Is it a known issue?
=> select version(); version
-------------------------------------------------------------------PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC
gcc.exe(GCC)3.4.4 (mingw special)
(1 row)
=> create table test (id int4 primary key, dt text) NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey"for table "test" CREATE TABLE
=> insert into test values (1, 'a'); INSERT 0 1
=> create view test_v as select * from test; CREATE VIEW
=> create rule test_upd as on update to test_v do instead update test set dt=NEW.dt where id=OLD.id; CREATE RULE
The result of concurrently running the same query update test_v set dt='b' where dt='a'
is as follows.
session-1 => begin; BEGIN
session-1 => update test_v set dt='b' where dt='a'; UPDATE 1
session-2 => begin; BEGIN
session-2 => update test_v set dt='b' where dt='a'; (blocked)
session-1 => commit; COMMIT
(session-2) UPDATE 1
*Explain* shows the following plan for the query here.
=> explain update test_v set dt='b' where dt='a'; QUERY PLAN
-----------------------------------------------------------------Hash Join (cost=24.57..50.59 rows=6 width=10) Hash
Cond:(public.test.id = public.test.id) -> Seq Scan on test (cost=0.00..21.60 rows=1160 width=10) -> Hash
(cost=24.50..24.50rows=6 width=4) -> Seq Scan on test (cost=0.00..24.50 rows=6 width=4) Filter:
(dt= 'a'::text)
(6 rows)
regards,
Hiroshi Inoue