current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question
2  
I could not make it working this way either. A possible workaround could be to get clock_timestamp() both on entry and exit and RAISE LOG with that information (or the difference or whatever). –  dezso May 9 '14 at 8:40

1 Answer 1

up vote 1 down vote accepted

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.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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