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.