Skip site navigation (1) Skip section navigation (2)

Re: BUG #1447: Sometimes rules don't work

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Olleg Samoylov" <olleg(at)mipt(dot)ru>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1447: Sometimes rules don't work
Date: 2005-01-29 23:32:13
Message-ID: 27649.1107041533@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Olleg Samoylov" <olleg(at)mipt(dot)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

In response to

Responses

pgsql-bugs by date

Next:From: John HansenDate: 2005-01-30 01:56:58
Subject: Bug in create operator and/or initdb
Previous:From: Michael FuhrDate: 2005-01-29 23:20:47
Subject: Re: Error messages

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group