What are PostgreSQL RULEs good for?

database postgresql triggers rules
StackExchange saddens dancek picture StackExchange saddens dancek · May 5, 2011 · Viewed 19.7k times · Source

Question

I often see it stated that rules should be avoided and triggers used instead. I can see the danger in the rule system, but certainly there are valid uses for rules, right? What are they?

I'm asking this out of general interest; I'm not very seasoned with databases.

Example of what might be a valid use

For instance, in the past I've needed to lock down certain data, so I've done something like 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 want 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;

I agree this is hacky, but I couldn't change the application(s) accessing the database in this case (or even throw errors at it). So bonus points for finding a reason why this is a dangerous/invalid use of the rule system, but not for why this is bad design.

Answer

a_horse_with_no_name picture a_horse_with_no_name · May 5, 2011

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)

Related questions

Using pg_notify in PostgreSQL trigger function

I am attempting to issue a notification from a PostgreSQL trigger function. I can successfully use the NOTIFY command, but I am not having any luck with pg_notify. Even though I receive a notification when I invoke the pg_…

Read More
When we need return value from trigger procedure?

Documentation about trigger procedures (https://www.postgresql.org/docs/9.6/static/plpgsql-trigger.html) says: "A trigger function must return either NULL or a record/row". Example CREATE TRIGGER my_trigger AFTER INSERT ON table_name FOR EACH ROW EXECUTE PROCEDURE some_…

Read More
How to disable PostgreSQL triggers in one transaction only?

I need to temporary disable one PostgreSQL trigger in a transaction, but without hardlocking table. Does someone know if it is possible? Something like this without locking table and disabling trigger outside of this transaction. BEGIN TRANSACTION; ALTER TABLE foo …

Read More