Обсуждение: 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@tummy.com>
tummy.com, ltd. - Linux Consulting since 1995.  Qmail, Python, SysAdmin
      Back off man. I'm a scientist.   http://HackingSociety.org/

Sean Reifschneider <jafo@tummy.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@tummy.com>
tummy.com, ltd. - Linux Consulting since 1995.  Qmail, Python, SysAdmin