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;
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 |