Thread: Implementation of a bag pattern using rules
Hello, I've been playing around with a simple solution for a bag or sparse
matrix using rules,
but have encountered a few problems I wish to discuss.
The bag pattern is commonly used for shopping baskets (item => quantity).
This sollution can also be used for a sparse matrix too (row,col => value).
Example:
CREATE TABLE bag_test
( item text PRIMARY KEY, qty integer
);
To add/modify/del items in the above table is tedious,
you need to first check for existence of an item then choose your SQL
statement (INSERT/UPDATE/DELETE/do nothing).
I want to be able to add/modify/del an item using only INSERT.
eg:
INSERT INTO bag_test VALUES ('apple', 1);
INSERT INTO bag_test VALUES ('apple', 12);
In the second statement, ee have a choice though, of whether to
increase the quantity of 'apple' by 12, or set the quantity of 'apple'
to 12.
So, for the absolute option (set 'apple' to 12), we can use the
following rule:
CREATE RULE bag_abs AS ON INSERT TO bag_test WHERE EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item) DO
INSTEAD UPDATE bag_test SET qty = NEW.qty WHERE item = NEW.item;
I also want the item to be deleted if it's quantity is <= 0:
CREATE RULE bag_del AS ON UPDATE TO bag_test WHERE NEW.qty <= 0 DO INSTEAD DELETE FROM bag_test WHERE item
=NEW.item;
Alternatively, for the relative option (increase 'apple' by 12), replace
the 'bag_abs' rule with:
CREATE RULE bag_rel AS ON INSERT TO bag_test WHERE EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item) DO
INSTEAD UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item;
(You still need the 'bag_del' rule if you want quantity <= 0 to be deleted)
Unfortunately there is a problem with 'bag_rel':
When the item already exists, it works fine, the item's quantity
is increased by the amount given in the INSERT statement.
BUT, if the item doesn't exist it gets double the quantity given in the
statement.
eg:
> SELECT * FROM bag_test; item | qty ------+----- (0 rows)
> INSERT INTO bag_test VALUES ('apple', 12); INSERT 0 1 > SELECT * FROM bag_test; item | qty -------+-----
apple | 24 (1 row)
This is double the expected value!
> INSERT INTO bag_test VALUES ('apple', 12); INSERT 0 0 > SELECT * FROM bag_test; item | qty -------+-----
apple | 36 (1 row)
But, this worked fine (increased by 12)!
> INSERT INTO bag_test VALUES ('apple', -36); INSERT 0 0 > SELECT * FROM bag_test; item | qty ------+-----
(0 rows)
Deleting works fine too.
Does anyone know how to prevent the problem with the initial insert?
I've read 'The Rule System' chapter several times, it's fairly heavy going,
and results in much head scratching, but I still can't work out how to
fix it.
Any suggestions on improving the rules?
Other than the problem mentioned, can anyone see a flaw in this method?
Cheers
--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
When grilled further on (Mon, 09 Feb 2004 12:42:10 +0000), Mark Gibson <gibsonm@cromwell.co.uk> confessed: > > CREATE RULE bag_abs AS ON INSERT TO bag_test > WHERE > EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item) > DO INSTEAD > UPDATE bag_test SET qty = NEW.qty WHERE item = NEW.item; > > CREATE RULE bag_rel AS ON INSERT TO bag_test > WHERE > EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item) > DO INSTEAD > UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item; I'm no expert, just up early. I believe both of these rules are tripping. bag_abs is likely going first, then bag_rel, so bag_abs is inserting the record, then bag_rel is updating it. You could verify this by deleting the two rules, then re-creating in the opposite order, and see if your inserted values change. How would you expect the system to choose one of the two rules, which is what you apparently expect? Later, Rob -- 05:57:27 up 16:48, 3 users, load average: 2.23, 2.17, 2.16
Robert Creager wrote: >When grilled further on (Mon, 09 Feb 2004 12:42:10 +0000), >Mark Gibson <gibsonm@cromwell.co.uk> confessed: > > > >> CREATE RULE bag_abs AS ON INSERT TO bag_test >> WHERE >> EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item) >> DO INSTEAD >> UPDATE bag_test SET qty = NEW.qty WHERE item = NEW.item; >> >> CREATE RULE bag_rel AS ON INSERT TO bag_test >> WHERE >> EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item) >> DO INSTEAD >> UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item; >> >> > >I'm no expert, just up early. I believe both of these rules are >tripping. >bag_abs is likely going first, then bag_rel, so bag_abs is inserting the >record, >then bag_rel is updating it. You could verify this by deleting the two >rules, >then re-creating in the opposite order, and see if your inserted values >change. > >How would you expect the system to choose one of the two rules, which is >what >you apparently expect? > > > I probably didn't make this clear enough: The system doesn't choose, YOU choose EITHER 'bag_abs' OR 'bag_rel' depending on which behaviour is most appropriate for your application. 'bag_del' can be used in combination with either, to remove empty items. The 'bag_abs'/'bag_del' rules work perfectly - I've provided them for feedback, and hopefully others will find them useful. It's only the 'bag_rel' rule that is giving me a headache. Also, IIRC, rules are applied in alphabetical order, NOT the order in which they were created. Cheers. -- Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk> Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England.
Mark, love the idea, guess I should have read it somewhere but haven't. Obvious and beautiful. Please let me know if you or someone else solves the initial double value. Got me thinking of all the places I cold have used this instead of coding select/insert/update/delete. Also have you worked a solutions where both the abs and relative inserts apply to the same bag eg insert another apple vs set apples to 5 Much of my attitude to triggers has been non-committal. Your example changes that. Well thanks again Richard Sydney-Smith ----- Original Message ----- From: "Mark Gibson" <gibsonm@cromwell.co.uk> To: <pgsql-sql@postgresql.org> Sent: Monday, February 09, 2004 8:42 PM Subject: [SQL] Implementation of a bag pattern using rules > Hello, > I've been playing around with a simple solution for a bag or sparse > matrix using rules, > but have encountered a few problems I wish to discuss. > The bag pattern is commonly used for shopping baskets (item => quantity). > This sollution can also be used for a sparse matrix too (row,col => value). > > Example:
Mark Gibson <gibsonm@cromwell.co.uk> writes:
> Alternatively, for the relative option (increase 'apple' by 12), replace
> the 'bag_abs' rule with:
> CREATE RULE bag_rel AS ON INSERT TO bag_test
> WHERE
> EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item)
> DO INSTEAD
> UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item;
This can't work because an ON INSERT rule fires after the INSERT itself
is executed. You have the equivalent of
INSERT INTO ... WHERE NOT EXISTS(SELECT ...);
UPDATE ... WHERE item = NEW.item AND EXISTS(SELECT ...);
The INSERT will execute because there's no row matching the EXISTS(),
and then the UPDATE will execute too because now there is a matching
row. In some contexts this is a feature. However, you want a single
test to control both actions.
I think you need to use a BEFORE INSERT trigger instead. It could
do something like
-- see if there is an existing row, if so update itUPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item;-- if
therewas one, suppress the INSERTIF found THEN RETURN NULL;END IF;-- else allow the INSERTRETURN NEW;
You could also extend the trigger to handle the
delete-upon-reaching-zero logic.
regards, tom lane
Richard Sydney-Smith wrote:
>Mark,
>
>love the idea, guess I should have read it somewhere but haven't. Obvious
>and beautiful. Please let me know if you or someone else solves the initial
>double value.
>
>
>
I used to use functions for this kind of thing,
and was thinking that what SQL really needed was an 'UPDATE OR INSERT'
command,
then it suddenly came to me last night, it could be done with rules or
triggers.
[I've posted a trigger solution for the relative values separately, in
response to Tom Lanes help]
>Got me thinking of all the places I cold have used this instead of coding
>select/insert/update/delete.
>Also have you worked a solutions where both the abs and relative inserts
>apply to the same bag
>
>eg insert another apple vs set apples to 5
>
>
>
Hmmm, yeah, I'm wondering about that one. It would be handy.
Custom datatype maybe - an integer with a flag to indicate absolute or
relative???
eg:
INSERT INTO bag_test VALUES ('orange', '10 abs');
INSERT INTO bag_test VALUES ('orange', '-5 rel');
or views that modify an underlying table???
eg:
INSERT INTO bag_test_abs VALUES ('orange', 10);
INSERT INTO bag_test_rel VALUES ('orange', -5);
I have no idea yet whether these are possible though, any ideas?
>Much of my attitude to triggers has been non-committal. Your example changes
>that.
>
>
>
Triggers, rules and functions ROCK. It's allowed us to move all the
business logic into the
database itself so we can create really simple clients easily in any
language/environment.
Right, I'm off home now :)
Cheers
--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
Tom Lane wrote: >Mark Gibson <gibsonm@cromwell.co.uk> writes: > > >> CREATE RULE bag_rel AS ON INSERT TO bag_test >> WHERE >> EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item) >> DO INSTEAD >> UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item; >> >> > >This can't work because an ON INSERT rule fires after the INSERT itself >is executed. > > I suspected that it may be impossible with rules, but I thought I'd ask, I'm still trying to get to grips with them. >I think you need to use a BEFORE INSERT trigger instead. >You could also extend the trigger to handle the >delete-upon-reaching-zero logic. > > So, here's my proof-of-concept trigger for the relative quantities: CREATE OR REPLACE FUNCTION bag_rel_trigger() RETURNS TRIGGER AS ' DECLAREoldqty bag_test.qty%TYPE; BEGINIF NEW.qty <> 0 THEN SELECT INTO oldqty qty FROM bag_test WHERE item = NEW.item; IF NOT FOUND AND NEW.qty > 0 THEN RETURN NEW; END IF; IF oldqty + NEW.qty <= 0 THEN DELETE FROM bag_test WHERE item = NEW.item; ELSE UPDATE bag_test SETqty = qty + NEW.qty WHERE item = NEW.item; END IF;END IF;RETURN NULL; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER bag_rel BEFORE INSERT ON bag_test FOR EACH ROW EXECUTE PROCEDURE bag_rel_trigger(); I think it should be possible to make the trigger generic for any table, the quantity column could be passed as a parameter to the trigger, but it would require some horribly complex code to determine the primary key and lots of EXECUTE calls - a lot of overhead each time the trigger is called :( I was thinking maybe of a function thats create a trigger optimized for the table. Any ideas? Cheers -- Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk> Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England.
When grilled further on (Mon, 09 Feb 2004 13:49:17 +0000), Mark Gibson <gibsonm@cromwell.co.uk> confessed: > I probably didn't make this clear enough: > Nah. After re-reading your e-mail, I say what I missed the first time. 'Bout 1 hour before my normal thinking time... Cheers, Rob -- 20:20:54 up 1 day, 7:11, 3 users, load average: 2.32, 2.18, 2.17