Problem with RULE to update tables - Mailing list pgsql-bugs
From | Sean Reifschneider |
---|---|
Subject | Problem with RULE to update tables |
Date | |
Msg-id | 20030317000922.GL2754@tummy.com Whole thread Raw |
Responses |
Re: Problem with RULE to update tables
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | 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 by date: