2

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.

  • Please clarify: Are you only looking for procedures called as part of a trigger? – Peter Eisentraut Jul 13 '16 at 18:16
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.

  • 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 '16 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. – Peter Eisentraut Jul 13 '16 at 18:11
0

Strictly speaking, there are not true "stored procedures" in Postgres. Just functions - doing almost but not quite the same.

The best option I see is putting RAISE LOG statements into plpgsql function bodies.

One other option that comes to mind: the additional module auto_explain. It's actually used to log all query plans and generates huge amounts of log output for debugging. But it has the unique capability to also log query plans for nested statements including SQL in plpgsql functions. And you can restrict it to long running queries. So if you should be hunting for performance problems in one of your functions, there is an option for you. But not to just log all function calls. Details:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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