How to write a delete rule on a view?

Question

I'm trying to write a rule on a view to delete tuples from the component tables, but so far can only remove data from one of them. I've used postgres with basic views for a while, but I don't have any experience with rules on views.

I wrote a stupid little test case to figure out/show my problem. There's only one parent tuple per child tuple in this example (my actual schema isn't actually like this of course).

Component tables:

CREATE TABLE parent(
   id serial PRIMARY KEY,
   p_data integer NOT NULL UNIQUE
);
CREATE TABLE child(
   id serial PRIMARY KEY,
   parent_id integer NOT NULL UNIQUE REFERENCES parent(id),
   c_data integer NOT NULL
);

View:

CREATE TABLE child_view(
   id integer,
   p_data integer,
   c_data integer
);
CREATE RULE "_RETURN" AS ON SELECT TO child_view DO INSTEAD
   SELECT child.id, p_data, c_data 
      FROM parent JOIN child ON (parent_id=parent.id);

Problem delete rule

CREATE RULE child_delete AS ON DELETE TO child_view DO INSTEAD(
   DELETE FROM child WHERE id=OLD.id;
   DELETE FROM parent WHERE p_data=OLD.p_data;
);

The intent of the above rule is to remove tuples referenced in the view from the component tables. The WHERE p_data=OLD.p_data seems odd to me, but I don't see how else to reference the desired tuple in the parent table.

Here's what happens when I try to use the above rule:

>SELECT * FROM child_view;
 id | p_data | c_data 
----+--------+--------
  1 |      1 |     10
  2 |      2 |     11
  3 |      3 |     12
(3 rows)

>DELETE FROM child_view WHERE id=3;
DELETE 0

>SELECT * FROM child_view;
 id | p_data | c_data 
----+--------+--------
  1 |      1 |     10
  2 |      2 |     11
(2 rows)

But looking at the parent table, the second part of the delete isn't working (id=3 "should" have been deleted):

>SELECT * FROM parent;
 id | p_data 
----+--------
  1 |      1
  2 |      2
  3 |      3
(3 rows)

How should I write the deletion rule to remove both child and parent tuples?

This is using postgres v9.

Any help is appreciated. Also pointers to any materials covering rules on views beyond the postgres docs (unless I've obviously missed something) would also be appreciated. Thanks.

EDIT: as jmz points out, it would be easier to use a cascading delete than a rule here, but that approach doesn't work for my actual schema.

Answer 1

What you're seeing with the rule problem is that the rule system doesn't handle the data atomically. The first delete is executed regardless of the order of the two statements in the DO INSTEAD rule. The second statement is never executed, because the row to which OLD.id refers to has been removed from the view. You could use a LEFT JOIN, but that won't help you because of the example table design (it may work on your actual database schema).

The fundamental problem, as I see it, is that you're treating the rule system as it was a trigger.

Your best option is to use foreign keys and ON DELETE CASCADE instead of rules. With them your example schema would work too: You'd only need on delete for the parent table to get rid of all the children.