Обсуждение: BUG #1447: Sometimes rules don't work

The following bug has been logged online:

Bug reference:      1447
Logged by:          Olleg Samoylov
Email address:      olleg@mipt.ru
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

"Olleg Samoylov" <olleg@mipt.ru> writes:
> create rule history_i as on insert to history do (update abonent set
> money=money+new.money where abonent=new.abonent);

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

What happens in the above is that the "new.money" placeholder is
replaced by the subselect from the INSERT command, and since the rule
fires after the insert itself is done, you get different results from
the second evaluation of the subselect.

You should be using a trigger for this sort of thing, not a rule.
Rules have their uses, but propagating copies of data from one place
to another is generally not one of them.  It's too easy to get bit by
the fact that a rule is a macro and thus subject to multiple-evaluation
gotchas.

            regards, tom lane

Tom Lane wrote:
> "Olleg Samoylov" <olleg@mipt.ru> writes:
>
>>create rule history_i as on insert to history do (update abonent set
>>money=money+new.money where abonent=new.abonent);
>
>>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;

> What happens in the above is that the "new.money" placeholder is
> replaced by the subselect from the INSERT command, and since the rule
> fires after the insert itself is done, you get different results from
> the second evaluation of the subselect.

IMHO, this is incorrect.

> You should be using a trigger for this sort of thing, not a rule.
> Rules have their uses, but propagating copies of data from one place
> to another is generally not one of them.

If you don't plan to fix this, this "feature" must be well documented.

--
Olleg