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 be 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 that are executed for an event by running relevant queries.
After ensuring triggers are applied correctly, set the logging level for postgresql server and client in postgres.conf file.
# let the 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 delete record';
This ensures 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 the potentially surprising behavior of PostgreSQL. There are some scenarios where PostgreSQL seems to be not working at first but it actually is the expected behavior.
- Unquoted object names will be treated as lowercase.
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 clients, servers & triggers, pinpointing the bug in triggers becomes easy. Once the bug is identified, appropriate action can be taken to fix the issue.