1

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.

  • 1
    For users who come across this post, it seems that the consensus is use triggers unless you really know what you are doing or the performance hit from them is unacceptable, as rules can work in unexpected ways. See this related SO question – Albert Rothman Mar 25 '18 at 23:11
1

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.

  • But given the rule that I created, this will still require an adicional query for each affected row, right? If so, performance-wise there will be no differnce, correct? – romaia Sep 17 '14 at 20:16
  • 2
    That depends. If you are executing an UPDATE statement that affects 50 rows, a TRIGGER will result in 50 queries whereas a RULE will accomplish the same in a single query to make the 50 updates. If you are only updating one row with each UPDATE statement, then there is no difference. – LowlyDBA Sep 17 '14 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.