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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-bugs by date

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