The following bug has been logged online: Bug reference: 3764 Logged by: Eugene M. Hutorny Email address: eugene@ksf.kiev.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; ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings

"Eugene M. Hutorny" <eugene@ksf.kiev.ua> writes: > 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. This isn't a bug --- once the first update is performed, the visible view contents change and so the second update finds no matching row. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq