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.50 rows=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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
