Postgresql – Delete rule on view does not execute every statement

postgresql

When I have more than one statements in a rule on a view, the second one is not executed in case the first one deletes the original entry. Even when the second statement does not have anything to do with the original entry at all.

Here is a simple example. Tables are reach and rp (reachpoint) and in reach we have an id that can be referenced to rp.

CREATE TABLE test_delete_rule.reach
(
  id integer NOT NULL,
  rp integer
);

CREATE TABLE test_delete_rule.rp
(
  id integer NOT NULL
);

INSERT INTO test_delete_rule.reach (id, rp) VALUES (1,10);
INSERT INTO test_delete_rule.reach (id, rp) VALUES (2,20);
INSERT INTO test_delete_rule.reach (id, rp) VALUES (3,30);
INSERT INTO test_delete_rule.reach (id, rp) VALUES (4,40);
INSERT INTO test_delete_rule.rp (id) VALUES (10);
INSERT INTO test_delete_rule.rp (id) VALUES (20);
INSERT INTO test_delete_rule.rp (id) VALUES (30);
INSERT INTO test_delete_rule.rp (id) VALUES (40);

And there is a view on reach called vw_reach:

CREATE VIEW test_delete_rule.vw_reach AS 
    select * from test_delete_rule.reach

And on this view is a rule, deleting the reach and the referenced rp:

CREATE OR REPLACE RULE reach_on_delete AS
  ON DELETE TO test_delete_rule.vw_reach 
  DO INSTEAD 
  ( 
    DELETE FROM test_delete_rule.reach
    WHERE reach.id = old.id;

    DELETE FROM test_delete_rule.rp
    WHERE rp.id = old.rp;
  );

When we delete now an entry from the vw_reach 

delete from test_delete_rule.vw_reach where id = 2

The entry with id 2 from reach is deleted, but not the entry with 20 from rp.

When I have the same rule on the table with DO ALSO ir works:

CREATE OR REPLACE RULE reach_on_delete AS
    ON DELETE TO test_delete_rule.reach  
    DO ALSO 
    (
       DELETE FROM test_delete_rule.rp
       WHERE rp.id = old.rp;
    );

So according the response here https://stackoverflow.com/questions/5534927/how-to-write-a-delete-rule-on-a-view the second statement is not executed because old.rp does not exist anymore. This makes sense but I cannot confirm.
Because it cannot be because of the old, because it does not execute the second statement neither if it has nothing to do with the original entry.

E.g.: 

CREATE OR REPLACE RULE reach_on_delete AS
    ON DELETE TO test_delete_rule.vw_reach 
    DO INSTEAD 
    ( 
       DELETE FROM test_delete_rule.reach
       WHERE reach.id = old.id;

       DELETE FROM test_delete_rule.rp
       WHERE rp.id = 20;
    );

Although the the second command has nothing to do with the values of the original entry.

But what in fact it works, when the order changes and the statement concerning the original is at the end. The following works:

CREATE OR REPLACE RULE reach_on_delete AS
    ON DELETE TO test_delete_rule.vw_reach 
    DO INSTEAD 
    ( 
        DELETE FROM test_delete_rule.rp
        WHERE rp.id = old.rp;

        DELETE FROM test_delete_rule.reach
        WHERE reach.id = old.id;
    );

So the conclusion is, that the rule is not continued after the original entry is deleted. It kinds of escapes. It does not do that on the table with DO ALSO.

Does anyone have a explanation for that behavior? It's quite confusing.

Best Answer

Quote from the manual

Caution

In many cases, tasks that could be performed by rules on INSERT/UPDATE/DELETE are better done with triggers. Triggers are notationally a bit more complicated, but their semantics are much simpler to understand.

(Emphasis mine)

I think this is one of the many cases where this is better done with a trigger:

create or replace function delete_reach() 
  returns trigger
as
$$
begin
  DELETE FROM reach
  WHERE reach.id = old.id;

  DELETE FROM rp
  WHERE rp.id = old.rp;
  return old;
end;  
$$
language plpgsql;

create trigger delete_reach_trigger 
   instead of delete 
   on vw_reach
   for each row
   execute procedure delete_reach();

Online example: https://rextester.com/KMEDTV61929

Related Question