Thread: Help with distinctly non-intuitive rule behaviour

Hello,

Can this possibly be right?

From my troubleshooting of a DELETE rule it appears that the rule's WHERE
condition sees the *results* of the rule-actions. Apart from being pretty
odd, this is a proving to be a big problem in my situation.

Here is a cut-down example:

Given a table 'users', let's say we would like to 'soft-delete' users from
the table by rewriting DELETE as UPDATE using the rule below. For active
users the users.user_departed field is 'infinity'::TIMESTAMP.  The UPDATE
action sets the timestamp of the user's deletion, if and only if the current
timestamp is in the future:

CREATE RULE rule_soft_delete_user AS ON DELETE TO users WHERE user_departed > now() DO INSTEAD    UPDATE users SET
user_departed= now()     WHERE user_id = OLD.user_id;
 

However, this does not work. I know this because I get a foreign-key
violation resulting from an unmolested DELETE action even for a user who has
not been soft-deleted. If as a test I change the UPDATE action so that it
does something other than change the timestamp then the rule is applied.

This seems pretty weird - can it be true?

---
Simon Kinsella
This message has been scanned for viruses.

"Simon Kinsella" <simon@bluefiresystems.co.uk> writes:
> CREATE RULE rule_soft_delete_user AS ON DELETE TO users
>   WHERE user_departed > now()
>   DO INSTEAD 
>     UPDATE users SET user_departed = now()
>       WHERE user_id = OLD.user_id;

This is run before the delete.  However, since it's a conditional rule,
the delete is still run, with the added where-condition
NOT(user_departed > now()).  Since the UPDATE has caused that to be
true, the delete happens.  You should reconsider whether this is to be
conditional or not.
        regards, tom lane