Logging functions executed by queries in 8.2?
Hackers,
In recent versions, we've changed the logging of function executions so
that only the function call is logged, and not any of the queries which it
may execute internally. While most of the time this method is superior
for performance analysis, in applications with extensive multi-line stored
procedures sometimes you want to log each individual query.
While it's kind of possible to do this via RAISE NOTICE, that doesn't
accurately get you execution times, let alone allow you do to thinks like
log query plans. This is seriously hampering our ability to
performance-optimize for TPCE.
Questions:
1) Is there any workaround for 8.2 which would allow us to log the function
queries and plans?
2) Would it be reasonable to add a log_function_bodies option for 8.3?
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
Hackers,
In recent versions, we've changed the logging of function executions so
that only the function call is logged, and not any of the queries which
it may execute internally. While most of the time this method is
superior for performance analysis, in applications with extensive
multi-line stored procedures sometimes you want to log each individual
query.
Actually, it's even more twisted now; if I turn on debug_print_plan, I can
get the *plan* for queries executed by a function, but I can't get their
durations.
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
Josh Berkus <josh@agliodbs.com> writes:
In recent versions, we've changed the logging of function executions so
that only the function call is logged, and not any of the queries which it
may execute internally. While most of the time this method is superior
for performance analysis, in applications with extensive multi-line stored
procedures sometimes you want to log each individual query.
ISTM that the wave of the future for this is an instrumentation plug-in,
not further kluging of the query logging functionality. I had the
impression that Korry and EDB had some prototype capability in that
direction already, and surely it shouldn't be that hard to write if not.
regards, tom lane
Josh Berkus <josh@agliodbs.com> writes:
In recent versions, we've changed the logging of function executions so
that only the function call is logged, and not any of the queries which it
may execute internally. While most of the time this method is superior
for performance analysis, in applications with extensive multi-line stored
procedures sometimes you want to log each individual query.ISTM that the wave of the future for this is an instrumentation plug-in,
not further kluging of the query logging functionality. I had the
impression that Korry and EDB had some prototype capability in that
direction already, and surely it shouldn't be that hard to write if not.
There's a sort of "proof-of-concept" PL/pgSQL tracer plugin in the
debugger project on pgFoundry - I haven't played with it in a few months
so I can't promise that it will run at the moment.
If anyone is interested, let me know and I'll add this to my ToDo
list.
-- Korry
Korry,
If anyone is interested, let me know and I'll add this to my ToDo
list.
The Sun benchmarking team needs this. However, we need to be able to feed
the data into some kind of mass analysis ala pg_fouine so that we can do
overall performance analysis.
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
If anyone is interested, let me know and I'll add this to my ToDo
list.The Sun benchmarking team needs this. However, we need to be able to feed
the data into some kind of mass analysis ala pg_fouine so that we can do
overall performance analysis.
I've also included a PL/pgSQL profiler in the PL debugger project - this
plugin spits out an XML profile so you should be able to massage
it/aggregate it however you like.
-- Korry