Обсуждение: Rule problem

Hi,

I have defined the following 3 tables (a,b,c) and 2 rules (a_delete,
c_delete) in postgres 7.2:
         Table "a"Column |  Type   | Modifiers
--------+---------+-----------id1    | integer | not null
Primary key: a_pkey
Rules: a_delete
         Table "b"Column |  Type   | Modifiers
--------+---------+-----------id1    | integer |id2    | integer |
         Table "c"Column |  Type   | Modifiers
--------+---------+-----------id2    | integer | not null
Primary key: c_pkey
Rules: c_delete

create rule a_delete as
on delete to a do
delete from b where id1 = old.id1;

create rule c_delete as
on delete to c do
delete from a where id1 = (select id1 from b where id2 = old.id2);

The tables contains the following values:

select * from c;id2
-----104105106

select * from a;id1
-----  1  2  3

select * from b;id1 | id2
-----+-----  1 | 105


The problem is that when I try to delete an entry i table c which have a
reference in table b to table a, the rule deletes the entry in table c and
table b but NOT in table a. (see output below)
Can someone please tell me why this happens and how to solve it

delete from c where id2 = 105;
DELETE 1

select * from a;id1
-----  1     <- This should also be deleted by the c_delete rule!!!  2  3

select * from b;id1 | id2
-----+-----
(0 rows)

select * from c;id2
-----104106


Thanks

Svenn Grindhaug.

Svenn Helge Grindhaug <svenn@ii.uib.no> writes:
> create rule a_delete as
> on delete to a do
> delete from b where id1 = old.id1;

> create rule c_delete as
> on delete to c do
> delete from a where id1 = (select id1 from b where id2 = old.id2);

The a_delete rule is run before the actual "DELETE FROM a" command,
so by the time the latter runs, the b row is gone, and no match exists.

I'd be inclined to think about doing this with foreign keys (set up
for ON DELETE CASCADE) instead of handmade rules.  If you really want
to do it by hand, triggers will probably work better than rules.
        regards, tom lane