3

I'm not asking how to log what the stored procedure does, I simply want a list of all the stored procedures that are being triggered and run dumped into the statement log.

I've got it logging all the statements with:

log_statement=all

but all that is logging is the statements sent to the server, not the triggered stored procedures that are running because of them.

I'm trying to find out which which functions are being fired because of triggers. I wouldn't mind know which triggers are firing, but most interested in knowing which functions are getting called because of them.

I've inherited this code base and it has a LOT of stored procedures, I'd just like to know what's getting run, I can turn on logging in those procedures if I need it, but I don't want to go annotating all the stored procs to see all the statements they are executing.

1
  • Please clarify: Are you only looking for procedures called as part of a trigger? Jul 13, 2016 at 18:16

2 Answers 2

3

You can see a count of all user-defined functions being called in the system view pg_stat_user_functions.

There is no facility to write all function calls to the log. That would be quite bulky.

If you're feeling adventurous, you can play with the settings debug_print_parse and debug_print_plan to get more detailed information about what is being called. Again, this will be bulky.

2
  • Not seeing debug_print_plan helping here, It lays out the query plan, but I don't see it showing which triggers are being run, unless I just am not reading it right.
    – boatcoder
    Jul 13, 2016 at 17:03
  • The plan will contain a FUNCEXPR with a funcid for each function call. But if you want to know which triggers are being run, this won't help you. Jul 13, 2016 at 18:11
1

Strictly speaking, there are were no true "stored procedures" before Postgres 11. Just functions - doing almost but not quite the same. SQL procedures have been added with Postgres 11. See:

The best option I see is putting RAISE LOG statements into PL/pgSQL function bodies.

Either way, one other option to log routines (functions and procedures): the additional module auto_explain. It's actually used to log all query plans and generates large amounts of log output for debugging. But it has the unique capability to also log query plans for nested statements including SQL in PL/pgSQL routines. Can be restricted to long-running queries. If you should be hunting for performance problems in one of your functions, that's an option. But not to just log function calls. Details:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.