Re: Problem with RULE to update tables

I've tried this on both 7.3 and 7.2.3, and am running into an odd
problem. Basically I have one table that is a count of available and one
that has allocations against that. I wanted to use rules to auto-update the
available count, and everything works fine as long as I do deletes
one-by-one...

If I delete multiple records, the available count is only updated once.
For example:

create table a ( available integer );
create table b ( id integer, used integer );
create rule a_delete as on delete to b
do update a set available = available + OLD.used;
create rule a_insert as on insert to b
do update a set available = available - NEW.used;

insert into a (available) values (100);
insert into b (id, used) values (1, 50);
insert into b (id, used) values (2, 50);
select available from a;
>>> available
>>>-----------
>>> 0
>>>(1 row)
delete from b where id = 2;
select available from a;
>>> available
>>>-----------
>>> 50
>>>(1 row)
insert into b (id, used) values (2, 50);
select available from a;
>>> available
>>>-----------
>>> 0
>>>(1 row)
delete from b;
select available from a;
>>> available
>>>-----------
>>> 50 <<<<<<<<<<<< Should be 100
>>>(1 row)
select * from b;

I made a rule-set that inserted data into a log table, and it was
showing that the update was indeed happening twice:

create table log ( seq serial, available integer, id integer,
used integer );
create rule a_delete as on delete to b
do (update a set available = available + OLD.used;
insert into log ( available, id, used )
select available, OLD.id, OLD.used from a);

the results are:

seq | available | id | used
-----+-----------+----+------
1 | 50 | 2 | 50
2 | 50 | 1 | 50
3 | 50 | 2 | 50
(3 rows)

So, it's like the value of "available" is being cached or otherwise just
does not reflect the first update.

Any thoughts? I would expect this to work, but it seems there's
something about the updates done a rule can only impact a row once. Is
there a better way to do this?

Thanks,
Sean
--
"Science exists to lend belief to sci-fi movies."
Sean Reifschneider, Inimitably Superfluous <jafo(at)tummy(dot)com>
tummy.com, ltd. - Linux Consulting since 1995. Qmail, Python, SysAdmin
Back off man. I'm a scientist. http://HackingSociety.org/

Sean Reifschneider <jafo(at)tummy(dot)com> writes:
> I've tried this on both 7.3 and 7.2.3, and am running into an odd
> problem. Basically I have one table that is a count of available and one
> that has allocations against that. I wanted to use rules to auto-update the
> available count, and everything works fine as long as I do deletes
> one-by-one...

> If I delete multiple records, the available count is only updated once.

Yup. A rule is invoked once per query, not once per operated-on record.
You'll find that a trigger acts more like what you are expecting.

regards, tom lane

On Mon, Mar 17, 2003 at 02:41:29PM -0500, Tom Lane wrote:
>Yup. A rule is invoked once per query, not once per operated-on record.
>You'll find that a trigger acts more like what you are expecting.

Ah, yeah, that seems to work exactly as expected. At least, my tests
now pass successfully after converting from a rule to a trigger. It was
a pain that trigger functions can't be SQL. I just wanted to run some
SQL after the delete/insert/update, and ended up having to wrap it in
a pl/python function...

Thanks,
Sean
--
"Isn't having a smoking section in a restaurant kind of like having a
peeing section in a swimming pool?" -- David Broadfoot
Sean Reifschneider, Inimitably Superfluous <jafo(at)tummy(dot)com>
tummy.com, ltd. - Linux Consulting since 1995. Qmail, Python, SysAdmin