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

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;

Am Dienstag, 20. November 2007 schrieb Eugene M. Hutorny:
> Description:        Update count returns zero for a view with 'on update'
> rules when criteria contains updatable field

This is known and documented.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

"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

This 'feature' makes such a view unusable in the clients (MS Access or any
other ADO based) that *do verify* update count and report an error if it is
not of expected value.
Is there a workaround for this?

Peter, you mentioned it is documented, may you please point me to the
document you meant?

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Eugene M. Hutorny" <eugene@ksf.kiev.ua>
Cc: <pgsql-bugs@postgresql.org>
Sent: Tuesday, November 20, 2007 8:28 PM
Subject: Re: [BUGS] BUG #3764: Update count returns zero for a view with 'on
update' rules when criteria contains updatable field


"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

----- Original Message -----
From: "Peter Eisentraut" <peter_e@gmx.net>
To: <pgsql-bugs@postgresql.org>
Cc: "Eugene M. Hutorny" <eugene@ksf.kiev.ua>
Sent: Tuesday, November 20, 2007 7:58 PM
Subject: Re: [BUGS] BUG #3764: Update count returns zero for a view with 'on
update' rules when criteria contains updatable field


Am Dienstag, 20. November 2007 schrieb Eugene M. Hutorny:
> Description:        Update count returns zero for a view with 'on update'
> rules when criteria contains updatable field

This is known and documented.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Am Mittwoch, 21. November 2007 schrieb Eugene Hutorny:
> Peter, you mentioned it is documented, may you please point me to the
> document you meant?

http://www.postgresql.org/docs/8.2/static/rules-status.html

This is the part that addresses this.  If you find that between what is
written and Tom's comments, it doesn't adequately explain the situation,
please let us know how to improve it.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/