CODEWITHSUNDEEP
postgresqlfunctionloggingtriggersaudit
Dietmar Schneider
Dietmar Schneider

Reputation: 53

PostgreSQL log trigger/function query data

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).

Upvotes: 3

Views: 7660

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246163

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.

Upvotes: 4

Related Questions