pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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) #





view thread (2+ messages)

Message-ID: <m10IxlV-000EBQC@orion.SAPserv.Hamburg.dsh.de>
Permalink:  ../m10IxlV-000EBQC@orion.SAPserv.Hamburg.dsh.de/
Also on:    postgresql.org/message-id/m10IxlV-000EBQC@orion.SAPserv.Hamburg.dsh.de

 · 

reply

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