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

Problem with RULE to update tables

From: Sean Reifschneider <jafo(at)tummy(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problem with RULE to update tables
Date: 2003-03-17 00:09:22
Message-ID: 20030317000922.GL2754@tummy.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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/

Responses

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2003-03-17 17:59:38
Subject: Re: Bug #902: Build problems with 7.3.1 and 7.3.2 on SunOS (crypt.h)
Previous:From: Glen MazzaDate: 2003-03-15 22:08:10
Subject: PostgreSQL Home page: User Survey returning errors

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