From: jwieck@debis.com
To: emils@mail.usis.bkc.lv
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Hello & create rule question
Date: Fri, 5 Mar 1999 17:47:57 +0100 (MET)
Message-ID: <m10IxlV-000EBQC@orion.SAPserv.Hamburg.dsh.de> (raw)
In-Reply-To: <199903051429.QAA04937@omega.bkc.lv>
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) #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jwieck@debis.com, emils@mail.usis.bkc.lv
Subject: Re: [SQL] Hello & create rule question
In-Reply-To: <m10IxlV-000EBQC@orion.SAPserv.Hamburg.dsh.de>
References: <199903051429.QAA04937@omega.bkc.lv> <m10IxlV-000EBQC@orion.SAPserv.Hamburg.dsh.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox