What are PostgreSQL RULEs good for?

I've had a few bitter experiences with rules when dealing with volatile functions (if memory serves, depesz' blog post highlights some of them).

I've also broken referential integrity when using them because of the timing in which fkey triggers get fired:

CREATE OR REPLACE RULE protected_example AS
  ON DELETE TO example
  WHERE OLD.protected
  DO INSTEAD NOTHING;

... then add another table, and make example reference that table with an on delete cascade foreign key. Then, delete * from that table... and recoil in horror.

I reported the above issue as a bug, which got dismissed as a feature/necessary edge case. It's only months later that I made sense of why that might be, i.e. the fkey trigger does its job, and the rule then kicks in and does its own, but the fkey trigger won't check that its job was done properly for performance reasons.

The practical use-case where I still use rules is when a BEFORE trigger that pre-manipulates data (the SQL standard says is not allowed, but Postgres will happily oblige) can result in pre-manipulating the affected rows and thus changing their ctid (i.e. it gets updated twice, or doesn't get deleted because an update invalidated the delete).

This results in Postgres returning an incorrect number of affected rows, which is no big deal until you monitor that number before issuing subsequent statements.

In this case, I've found that using a strategically placed rule or two can allow to pre-emptively execute the offending statement(s), resulting in Postgres returning the correct number of affected rows.

Some problems with rules are shown here: http://www.depesz.com/index.php/2010/06/15/to-rule-or-not-to-rule-that-is-the-question/ (for instance, if a random() is included in a query, it might get executed twice and return different values).

The biggest drawback of rules is that people don't understand them.

For example, one might think that having the rule:

CREATE OR REPLACE RULE protect_data AS
  ON UPDATE TO exampletable             -- another similar rule for DELETE
  WHERE OLD.type = 'protected'
  DO INSTEAD NOTHING;

Will mean that if I'll issue:

update exampletable set whatever = whatever + 1 where type = 'protected'

No query will be ran. Which is not true. The query will be run, but it will be ran in modified version - with added condition.

What's more - rules break very useful thing, that is returning clause:

$ update exampletable set whatever = whatever + 1 where type = 'normal' returning *;
ERROR:  cannot perform UPDATE RETURNING on relation "exampletable"
HINT:  You need an unconditional ON UPDATE DO INSTEAD rule with a RETURNING clause.

To wrap it - if you really, really, positively have to use writable views, and you're using pre 9.1 PostgreSQL - you might have a valid reason to use rules.

In all other cases - you're most likely shooting yourself in a foot, even if you don't immediately see it.

How about this: You have a table that needs to be changed into a view. In order to support legacy applications that insert into said table, a rule is created that maps "inserts" to the new view to the underlying table(s).

One of the use cases for RULES are updateable views (although that changes in 9.1 as that version introduces INSTEAD OF triggers for views)

Another good explanation can be found in the manual:

For the things that can be implemented by both, which is best depends on the usage of the database. A trigger is fired for any affected row once. A rule manipulates the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is called for every single row and must execute its operations many times. However, the trigger approach is conceptually far simpler than the rule approach, and is easier for novices to get right.

(Taken from: http://www.postgresql.org/docs/current/static/rules-triggers.html)