The following bug has been logged online:
Bug reference: 1447
Logged by: Olleg Samoylov
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.4.6, 8.0.0
Operating system: x86_64-pc-linux-gnu, compiled by GCC x86_64-linux-gcc
(GCC) 3.3.5 (Debian 1:3.3.5-6)
Description: Sometimes rules don't work
Details:
Test below show this bug. This is very simplified query from real billing
system. If abonent get service less then $5 per month, the billing must get
other.
--create structure
-- table with abonent and his money
create table abonent (abonent integer primary key, money double precision
not null default 0);
-- table with history of money change
create table history (abonent integer not null references abonent, money
double precision not null);
-- to change money enough add record to table history
create rule history_i as on insert to history do (update abonent set
money=money+new.money where abonent=new.abonent);
-- insert example values
-- one abonent
insert into abonent values (1);
-- it get service for $2
insert into history values (1,-2);
-- Always must be 0, due to rule history_i
select abonent.money-history.money from (select sum(money) as money from
abonent) abonent, (select sum(money) as money from history) history;
-- Okey
-- payment per month, simplified but still show bug
-- minimal payment $5
insert into history (abonent,money) select abonent,-(money.money+5) as pay
from
( select abonent,sum(money) as money from history where money<0 group by
abonent) money
where money.money+5>0;
-- must be 0
select abonent.money-history.money from (select sum(money) as money from
abonent) abonent, (select sum(money) as money from history) history;
-- fail
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
