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

BUG #3764: Update count returns zero for a view with 'on update' rules when criteria contains updatable field

From: "Eugene M(dot) Hutorny" <eugene(at)ksf(dot)kiev(dot)ua>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3764: Update count returns zero for a view with 'on update' rules when criteria contains updatable field
Date: 2007-11-20 15:43:56
Message-ID: 200711201543.lAKFhuWY024444@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3764
Logged by:          Eugene M. Hutorny
Email address:      eugene(at)ksf(dot)kiev(dot)ua
PostgreSQL version: 8.2.4
Operating system:   Windows 2000
Description:        Update count returns zero for a view with 'on update'
rules when criteria contains updatable field
Details: 

I noticed strange behaviour of update statements issued to a view with an
'on update' rule when the where criterion contains the field being updated.

Please read this example:
-----------------------------------
create table a 
(
  aid integer not null,
  val varchar(32) null,
  constraint apk primary key (aid)
);

create table b 
(
  bid integer not null,
  mem varchar(32) null,
  constraint bpk primary key (bid)
);

create view ab(id,val,mem) as
select a.aid, a.val, b.mem 
from a inner join b on a.aid = b.bid;

create rule ab_ii as on insert to ab do instead
( insert into a(aid,val) select new.id, new.val;
  insert into b(bid,mem) select new.id, new.mem; 
);

create rule ab_iu as on update to ab do instead
( update a set val = new.val where a.aid = new.id;
  update b set mem = new.mem where b.bid = new.id;
);

insert into ab(id,val,mem) values(1,'1','1');
insert into ab(id,val,mem) values(2,'2','2');

-- !! This statement reports one row updated an it is expected result
update ab set val = '11' where id = 1;
-- !! This statement reports zero rows updated and it is unexpected result,
it indeed updates the row
update ab set val = '22' where id = 2 and val='2';

select * from ab;

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-11-20 17:12:04
Subject: Re: BUG #3762: Inherited serials change on dump/restore
Previous:From: Mike CharnokyDate: 2007-11-20 15:29:06
Subject: Re: BUG #3752: query yields "could not find block containing chunk", then server crashes

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