Question

I frequently view it mentioned that rules ought to be prevented and triggers used rather. I can tell the risk within the rule system, and surely you will find valid ways to use rules, right? What exactly are they?

I am asking this from general interest I am not so seasoned with databases.

Illustration of what may well be a valid use

For example, previously I have required to lock lower certain data, so I have done something similar to this:

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

Then if I wish to edit the protected data:

START TRANSACTION;
  ALTER TABLE exampletable DISABLE RULE protect_data;
  -- edit data as I like
  ALTER TABLE exampletable ENABLE RULE protect_data;
COMMIT;

To be sure this really is hacky, however i could not alter the application(s) being able to access the database within this situation (as well as throw errors in internet marketing). So bonuses for locating grounds why this really is a harmful/invalid utilisation of the rule system, but not why this really is bad design.

Among the use cases for RULES are updateable sights (although that alterations in 9.1 as that version introduces Rather Than triggers for sights)

One other good explanation are available in the manual:

For things that could be implemented by both, that is best is dependent on using the database. A trigger is fired for just about any affected row once. A guide manipulates the query or creates one more query. Therefore if many rows may take a hit in a single statement, a guide giving one extra command will probably be faster than the usual trigger that's known as for every row and should execute its procedures many occasions. However, the trigger approach is conceptually far simpler compared to rule approach, and it is simpler for beginners to obtain right.

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

Some issues with rules are proven here: http://www.depesz.com/index.php/2010/06/15/to-rule-or-not-to-rule-that-is-the-question/

The greatest disadvantage to rules is the fact that individuals don't understand them.

For instance, one may think that getting 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;

Means when I'll problem:

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

No query is going to be went. Which isn't true. The query will be run, but it will likely be went in modified version - with added condition.

In addition - rules break very helpful factor, that's coming back 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 - should you really, really, positively need to use writable sights, and you are using pre 9.1 PostgreSQL - you might possess a justification to make use of rules.

In most other cases - you are probably shooting yourself inside a feet, even when you do not immediately view it.

What about this: You've got a table that should be transformed right into a view. To be able to support legacy programs that place into stated table, a guide is produced that maps "card inserts" towards the new view towards the underlying table(s).