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

Поиск
Список
Период
Сортировка
От Eugene M. Hutorny
Тема BUG #3764: Update count returns zero for a view with 'on update' rules when criteria contains updatable field
Дата
Msg-id 200711201543.lAKFhuWY024444@wwwmaster.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #3764: Update count returns zero for a view with 'on update' rules when criteria contains updatable field  (Peter Eisentraut <peter_e@gmx.net>)
Re: BUG #3764: Update count returns zero for a view with 'on update' rules when criteria contains updatable field  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
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;

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Mike Charnoky
Дата:
Сообщение: Re: BUG #3752: query yields "could not find block containing chunk", then server crashes
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Start up error