How do I get postgres to log the stored procedures it is running?

Question

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.

Answer 1

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.

Answer 2

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:

• Postgres query plan of a UDF invocation written in pgpsql