We have business logic in SQL queries (~200 triggers), that supplement our application code. and we have some bugs in it -to find them I would like to see all transactions that change anything on the database (instead of checking 40 tables by hand).
We enabled logging in /etc/postgresql/10/main/postgresql.conf by setting:
log_statement = 'mod' # none, ddl, mod, all
This logs all INSERT/DELETE/UPDATE statements from the application correctly, like so:
INSERT INTO misc.object_types (uuid, object_type_schema, object_type_table) VALUES 'e49fcebd-e8f4-4ca4-b664-e7194685ae3f', 'gis', 'lines')
But the actions performed by SQL functions are not logged. Setting
track_functions = all # none, pl, all
in the conf file does show that the trigger/function got executed. But I am interested in the actual SQL statement with all values, just like with log_statement= 'mod'
Now I have found some topics here, but they all rely on manually creating an audit table or manually logging with RAISE (like this one Log firing of triggers in postgres 9.1).
How can I enable logging of queries executed by triggers/functions without manual (error-prone) code?
Even more important: Why are triggers/functions handled differently than regular queries? Does that also mean that a trigger calls a different INSERT than a manual INSERT?
log_statement will log only top-level statements, that is statements sent by the client. Nested statements are not logged.
One way to log those is to use the auto_explain contrib module. You'll have to enable it, set auto_explain.log_nested_statements = on and auto_explain.log_min_duration = 0. Then all statements, even nested ones, will be logged, along with their execution plan.