How can I enable logging for a single function in PostgreSQL?

Question

I want to log the whole function runtime for a single particular function in PostgreSQL? Is this possible?

The value of log_min_duration_statement = 3000 in postgresql.conf and I've tried to do: ALTER FUNCTION mychema.myfunc(json) SET log_min_duration_statement = 100, however I don't see any logs in /var/log/postgresql/postgresql-9.x-main.log

If the the log_min_duration_statement of the function is less than the log_min_duration_statement in the postgresql.conf does the function runtime get logged?

Can I add an exception somewhere to tell PostgreSQL that I want my function to get logged regardless if the global log_min_duration_statement value is < the log_min_duration_statement of the function?

I want the log_min_duration_statement of the function to take precedence over the global config value to see logs for that one function I'm interested in, not all the others, because enabling logging for all requests adds a significant overhead.

Answer 1

I tried the approach you described and it did not work for me, too. As log_min_duration_statement can be set only by a superuser, I also tried defining the function with SECURITY DEFINER, logged in as a superuser - to no avail.

My only idea left (for pl/pgsql functions only) is to save clock_timestamp() into a variable as the very first statement in the BEGIN block, then do a RAISE LOG as the last one (before the RETURN statement, if there is any), with the difference of the current clock_timestsmp() and the saved one.

To my understanding, the result of this may be somewhat smaller than the time logged by the normal logging - the time spent on entering and exiting the function will not be taken into account for sure.

(Note that with some helper functions you could also do this in SQL functions.)