[BUGS] Problem with RULE to update tables

[Sean Reifschneider]

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 <[EMAIL PROTECTED]>
tummy.com, ltd. - Linux Consulting since 1995.  Qmail, Python, SysAdmin
  Back off man. I'm a scientist.   http://HackingSociety.org/

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [BUGS] Problem with RULE to update tables

[Sean Reifschneider]

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 <[EMAIL PROTECTED]>
tummy.com, ltd. - Linux Consulting since 1995.  Qmail, Python, SysAdmin

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])