X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6170BD1D2CF for ; Mon, 9 Feb 2004 13:03:29 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 18294-05 for ; Mon, 9 Feb 2004 09:03:26 -0400 (AST) Received: from srvr3.iniquinet.com (srvr2.iniquinet.com [64.240.87.12]) by svr1.postgresql.org (Postfix) with SMTP id E1A0CD1DC77 for ; Mon, 9 Feb 2004 09:03:16 -0400 (AST) Received: (qmail 22617 invoked by uid 104); 9 Feb 2004 12:50:15 -0000 Received: from Robert_Creager@LogicalChaos.org by srvr3.iniquinet.com by uid 101 with qmail-scanner-1.15 (clamscan: 0.54. spamassassin: 2.55. Clear:SA:0(-7.8/6.0):. Processed in 35.498709 secs); 09 Feb 2004 12:50:15 -0000 Received: from vsat-148-64-8-86.c119.t7.mrt.starband.net (HELO chaos.mshome.net) (148.64.8.86) by srvr3.iniquinet.com with SMTP; 9 Feb 2004 12:49:39 -0000 Received: from localhost.localdomain (thunder.mshome.net [192.168.0.250]) by chaos.mshome.net (Postfix) with ESMTP id 0E8E21834A9; Mon, 9 Feb 2004 06:02:30 -0700 (MST) Received: from logicalchaos.org (thunder.mshome.net [192.168.0.250]) by chaos.mshome.net (Postfix) with SMTP id 6F93F1E49FD; Mon, 9 Feb 2004 06:02:17 -0700 (MST) Date: Mon, 9 Feb 2004 06:02:07 -0700 From: Robert Creager To: Mark Gibson Cc: PGSQL Subject: Re: Implementation of a bag pattern using rules Message-Id: <20040209060207.3d208b4e.Robert_Creager@LogicalChaos.org> In-Reply-To: <40278022.5070408@cromwell.co.uk> References: <40278022.5070408@cromwell.co.uk> Organization: Starlight Vision, LLC. X-Mailer: Sylpheed version 0.9.5claws (GTK+ 1.2.10; i586-mandrake-linux-gnu) Mime-Version: 1.0 Content-Type: multipart/signed; protocol="application/pgp-signature"; micalg="pgp-sha1"; boundary="Multipart_Mon__9_Feb_2004_06_02_07_-0700_=.ldlRGNpUr+F_tc" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/78 X-Sequence-Number: 16769 --Multipart_Mon__9_Feb_2004_06_02_07_-0700_=.ldlRGNpUr+F_tc Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline Content-Transfer-Encoding: 7bit When grilled further on (Mon, 09 Feb 2004 12:42:10 +0000), Mark Gibson 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 --Multipart_Mon__9_Feb_2004_06_02_07_-0700_=.ldlRGNpUr+F_tc Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (GNU/Linux) iEYEARECAAYFAkAnhNkACgkQLQ/DKuwDYzlUzwCcCaAtnuAGV5NLsAkfszhfApIF JQUAn0rOdVuw8+hQhwoszvydrgcsdRfs =VFMC -----END PGP SIGNATURE----- --Multipart_Mon__9_Feb_2004_06_02_07_-0700_=.ldlRGNpUr+F_tc--