Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de [53.2.131.8]) by hub.org (8.9.2/8.9.1) with SMTP id LAA92894 for ; Fri, 5 Mar 1999 11:41:12 -0500 (EST) (envelope-from wieck@sapserv.debis.de) Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-sql@postgreSQL.org id m10IxlV-000EBQC; Fri, 5 Mar 99 17:47 MET Message-Id: From: jwieck@debis.com (Jan Wieck) Subject: Re: [SQL] Hello & create rule question To: emils@mail.usis.bkc.lv Date: Fri, 5 Mar 1999 17:47:57 +0100 (MET) Cc: pgsql-sql@postgreSQL.org Reply-To: jwieck@debis.com (Jan Wieck) In-Reply-To: <199903051429.QAA04937@omega.bkc.lv> from "Emils Klotins" at Mar 5, 99 04:31:08 pm X-Mailer: ELM [version 2.4 PL25] Content-Type: text Emils Klotins wrote: > [...] > > museums (museum_id int2, description text, address_id int2) > addresses (address_id int2, address text); > > CREATE RULE museumdel ON DELETE TO museums DO DELETE FROM addresses WHERE > addresses.address_id=current.address_id; > > Now, when I do DELETE FROM museums, the psql monitor displays DELETE 0 and when I > check the tables, the proper entry from museums IS deleted, but the associated addresses row > is not. > > What am I doing wrong? The fault is that you're using v6.3.2. The above cannot work before v6.4. Details on that: Let's say you're doing DELETE FROM museums WHERE museum_id < 100; The additional query generated by the rule system to delete the addresses would look like DELETE FROM addresses WHERE museums.museum_id < 100 AND addresses.address_id = museums.museum_id; The bug in v6.3.2 is, that this additional query is executed AFTER the delete from museum and between these two queries a command counter increment is done by the traffic cop. Thus, at the time the addresses should get deleted, there are no museums with id < 100 left and the above DELETE will not find anything to delete. That's why you're seeing the DELETE 0 response, because the response sent to the client is allways the one from the last executed query. If you try (on your old installation) to use the following (also not working) rule CREATE RULE museumdel ON DELETE TO museums DO UPDATE addresses SET address = 'deleted' WHERE address_id = old.address_id; you should see that the response to a delete from museums becomes UPDATE 0 You must upgrade to a past v6.4 version to use rules ON UPDATE/DELETE. The last bugfix release on the v6.4 tree, v6.4.3 will be out in a few days. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #