Thread: Hello & create rule question

Hello!

I have just joined this mailing list, so please do not become frustrated, if I happen to ask
something that has already been answered recently.

There is a thing, which didn't work as I expected, so maybe you could tell me, what have I done
wrong?

There are 2 tables:

museums (museum_id int2, description text, address_id int2)
addresses (address_id int2, address text);

(they are actually a bit different, the museums.museum_id & address.address_id being unique
indices with default values from sequences etc.)

I want to delete museum's address from addresses when I delete something from museums.
So I thought to do it with a rule:

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?

Thanks in advance for any ideas & tips!


Emils Klotins       e-mail: emils@mail.usis.bkc.lv
Systems Manager     URL: http://www.usis.bkc.lv/
USIS Riga           7 Smilsu Str., Riga LV1050, LATVIA
......................................................
RSA PGP key published on: http://pgpkeys.mit.edu:11371

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) #