Postgresql – Choosing a RULE instead of a TRIGGER

postgresqltrigger

I my current database schema, there are hundreds of tables that reference another single table (called transaction_entry), that stores generic information about each row (when it was created, last updated, who created, who updated, and so on). For instance consider this (simplified) schema:

CREATE TABLE transaction_entry (
   id integer, 
   created timestamp,
   modified timestamp);
CREATE TABLE person (
   id integer,
   te_id integer REFERENCES transaction_entry(id),
   name text);

Currently, we update this information on the client side (python), but I am considering moving this to the database itself, either using triggers or rules.

This should behave so that every time one row is updated, the referenced transaction_entry should be updated as well. Most of the time a single row is updated in the queries, but there are rare cases that a lot of rows will be updated by a single query.

The postgres documentation does state that:

If you actually want an operation that fires independently for each physical row, you probably want to use a trigger, not a rule

This statement alone should make me decide that a TRIGGER is the correct choice for my case, but I did test with a RULE as well, and that does work fine. This is the rule that I tested with (also simplified):

CREATE RULE foo AS ON UPDATE TO person DO ALSO
   UPDATE transaction_entry SET modified = statement_timestamp() WHERE id = OLD.te_id;

What is the best choice for this scenario? Is a TRIGGER really the only option here, since each row has its on transaction_entry or is a RULE a safe option as well? What about performance? Should there be a significant difference between those two for this case?

I am leaning towards RULES since the syntax is considerably shorter and simpler then a corresponding trigger.

Best Answer

This depends most likely on how your updates will be accomplished, because

" 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."

- Postgres Documentation

So it seems that you find rule simpler is quite convenient in the scenario where you make a large amount of changes to the data. Either should work, though.

Related Question