Problem with RULE to update tables

Поиск
Список
Период
Сортировка
От Sean Reifschneider
Тема Problem with RULE to update tables
Дата
Msg-id 20030317000922.GL2754@tummy.com
обсуждение исходный текст
Ответы Re: Problem with RULE to update tables
Список 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@tummy.com>
tummy.com, ltd. - Linux Consulting since 1995.  Qmail, Python, SysAdmin
      Back off man. I'm a scientist.   http://HackingSociety.org/

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Nick Eskelinen
Дата:
Сообщение: Re: pg_dumpall segfault
Следующее
От: Nick Eskelinen
Дата:
Сообщение: pg_dumpall segfault