Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group