Sometimes, your trigger functions might run into the Multiversion Concurrency Control (MVCC) visibility rules of how PostgreSQL's system interacts with changes to data.
A function declared as STABLE
or IMMUTABLE
will never see changes applied to the underlying table by the previous triggers.
A VOLATILE
function follows more complex rules which are, in a nutshell, as follows:
BEFORE
triggers detects whether no changes are made by the current statement, and the statement-level AFTER
triggers detects all of the changes made by the statement.BEFORE
triggers, as the operation has not occurred yet. Changes made by other triggers to other rows in the same statement are visible, and as the order of the rows processed is undefined, you need to be cautious. Starting from PostgreSQL 9.3, an error is thrown if a tuple to be updated or deleted has already been updated or deleted by a BEFORE
trigger.INSTEAD OF
triggers. The changes made by the triggers fired in the same command in the previous rows are visible to the current invocation of the trigger function. Row-level AFTER
triggers are fired when all of the changes to all the rows of the outer command are complete and visible to the trigger function.All these rules apply to functions that query data in the database; the OLD
and NEW
rows are, of course, visible as described previously.
The same information in, perhaps, different words is available at http://www.postgresql.org/docs/current/static/spi-visibility.html.
Triggers are an appropriate tool for use in database-side actions, such as auditing, logging, enforcing complex constraints, and even replication (several logical replication systems such as Slony are based on triggers used in production). However, for most application logic, it is much better to avoid triggers as they can lead to really weird and hard-to-debug problems. As a good practice, follow the rules provided in the following table:
Rule |
Description |
---|---|
Rule 1 |
Do not change data in the primary key, foreign key, or unique key columns of any table |
Rule 2 |
Do not update records in the table that you read during the same transaction |
Rule 3 |
Do not aggregate over the table you are updating |
Rule 4 |
Do not read data from a table that is updated during the same transaction |
3.19.234.150