3

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.

  • Why not use a Foreign Key with on delete cascade?? – Michael Kutz Jan 17 at 11:12
  • Thanks for the response. It's because that would be for the other way around: When I delete the rp 20 the reach 2,20 would be deleted. – signedav Jan 17 at 12:48
1

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

  • Thanks. Your solution with the triggers works. But I do actually wan't to know why the rules do behave like that. Is there an explanation? – signedav Jan 17 at 12:50
  • @signedav: sorry, I can't give you an explanation for that. I never bothered to even try to understand rules as I consider them a legacy feature that's not worth using and that has gotten a much better and easier to handle replacement. – a_horse_with_no_name Jan 17 at 14:07
  • Okay. Thanks anyway. I see the problem can be solved else. But if someone can explain this behavior, I would be interested. It's just not doing it. There is no warning or something. This is quite dangerous. I didn't knew that rules are so unreliable... – signedav Jan 17 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.