Re: [SQL] Hello & create rule question

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(at)debis(dot)com (Jan Wieck) #