PostgreSQL triggers will associate a
function to a table for an event. If multiple triggers of the same kind are defined for the same event, they will executed in alphabetical order by name.
In this article we will see how to debug PostgreSQL triggers.
First ensure triggers are enabled on the required tables for INSERT/UPDATE/DELETE events. We can check available triggers by running the following query.
SELECT * FROM information_schema.triggers;
We can also use
EXPLAIN to show triggers which are executed for an event by running relevant queries.
After ensuring triggers are applied correctly, set logging level for postgresql server and client in postgres.conf file.
# let server log all queries log_statement = 'all' # set client message to log level client_min_messages = log
Restart postgresql to reflect configuration changes.
# Linux sudo service postgres restart # Mac brew services restart postgres
Tail the logs and check if queries are executing correctly with appropriate values.
After enabling logging for PostgreSQL, we can raise messages/errors in triggers so that we can see if any unexpected things are happening at any point in the trigger.
RAISE 'Updating row with ID: %', id; RAISE division_by_zero; RAISE WARNING 'Unable to deleted record';
This makes sure triggers are executing as expected and if there are any warnings/errors, it will log a message.
Even though queries and triggers are executing correctly, we might not see the desired result because of potentially suprising behaviour of PostgreSQL. There are a some scenarios where PostgreSQL seems to be not working at first but it actually is the expected behaviour.
- Unquoted object names will be treated as lower case.
SELECT FOO FROM barwill become
SELECT foo FROM bar.
- Comparing nullable fields. This might yield strange results as
NULL != NULL.
- PostgreSQL uses POSIX offsets. For
04:21:42 UTC+01, +1 means the timezone is west of Greenwich.
By being aware of common PostgreSQL gotchas and enabling logging for PostgreSQL client, server & triggers, pinpointing the bug in triggers becomes easy. Once the bug is identified, appropriate action can be taken to fix the issue.