PostgreSQL log trigger/function query data

Question

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?

Laurenz Albe · Accepted Answer · 2020-11-26 12:06:18Z

4

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.

answered Nov 26, 2020 at 12:06

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dietmar Schneider Over a year ago

Thanks Laurenz-Albe for this pointer! Now I do get the log output, but with variables instead of values.

INSERT INTO misc.object_types (uuid, object_type_schema, object_type_table, label) VALUES (NEW.uuid, TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.label) ON CONFLICT DO NOTHING

Any idea on how to drill down further? Instead of the variable name NEW.uuid I would like to see the value that gets written to database (i confirmed that i have a value in the db). I could not find a switch in EXPLAIN nor AUTO_EXPLAIN.

Laurenz Albe Over a year ago

For more, you have to sprinkle your code with RAISE NOTICE to output the data.

Collectives™ on Stack Overflow

PostgreSQL log trigger/function query data

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related