Database Reference
In-Depth Information
Visibility
Sometimes your trigger functions may run into the
Multiversion Concurrency Con-
trol
(
MVCC
) visibility rules of how PostgreSQL's system interacts with changes to
data.
A function declared
STABLE
or
IMMUTABLE
will never see changes applied to the un-
derlying table by previous triggers.
A
VOLATILE
function follows more complex rules, which are in a nutshell as follows:
• The statement-level
BEFORE
triggers see no changes made by the current
statement, and statement-level
AFTER
triggers see all of the changes
made by the statement.
• Data changes by the operation to the row causing the trigger to fire are of
course not visible to
BEFORE
triggers, as the operation has not happened
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 this needs
caution!
• The same is true of
INSTEAD OF
triggers. The changes by the triggers
fired in the same command on previous rows are visible to current invoca-
tion of trigger function. Row-level
AFTER
triggers are fired when all of the
changes to all rows of the outer command are complete and visible to the
trigger function.
This all applies to functions querying data in the database, the
OLD
and
NEW
rows are
of course visible as described previously.
The same information in perhaps a different wording is available at
ht-
And most importantly - use triggers cautiously!
Triggers are an appropriate tool for using in database-side actions, such as auditing,
logging, enforcing complex constraints, and even replication (there are several logical
replication systems based of triggers in production use). However, for most applica-
