Re: Auto-explain patch

On Wed, 2008-07-09 at 09:11 +0000, Dean Rasheed wrote:
> Simon, I like your proposal, and I think I can see how to code it
> fairly easily.
>
> There is one thing that it doesn't allow, however, which the debug_xxx
> parameters do, and that is for a non-superuser to trace SQL used in
> functions, from an interactive client session. For me, this is quite a
> big thing, because I find it most convienient to turn these parameters
> on while writing and tweaking stored procedures, and have the output
> go straight to my psql window, without having to connect as a superuser
> and trawl through log files.
>
> So I suggest grouping these parameters in their own category
> (eg. "sql_trace") and then having additional parameters to control
> where the output would go. So the sql_trace parameters would be:
>
> * sql_trace_min_planner_duration
> * sql_trace_min_executor_duration
> * sql_trace_explain_plan
>
> and they would work exactly as you describe, except they would be
> settable by normal users. Then the destination(s) for the statement
> and EXPLAIN logging would be controlled by:
>
> * client_sql_trace = on | off - settable by a normal user to allow a
> client session to see the sql_trace output. If this parameter is on,
> the sql_trace will be logged as NOTICE output.

After sleeping on this, I think we should follow your idea.

If its possible to do the sql_trace_* parameters as a single one, I
would prefer it, since it makes it more practical to use dynamically.
Not sure how...maybe with a wrapper function?

sql_trace(integer) sets just sql_trace_min_executor_duration
sql_trace(integer, boolean) sets executor and explain
sql_trace(integer, integer, boolean) sets all 3

I think you probably need to drop the sql_ off the front because of
parameter length only.

No need for the other log_... parameter though.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support