Concurrently updating an updatable view

From: Hiroshi Inoue <inoue(at)tpf(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Concurrently updating an updatable view
Date: 2007-05-14 03:58:14
Message-ID: 4647DE56.7080104@tpf.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dave Page 2007-05-14 08:05:15 Re: What is happening on buildfarm member baiji?
Previous Message CK Tan 2007-05-14 02:36:57 Re: Seq scans roadmap