Re: On-demand running query plans using auto_explain and signals

On Mon, Aug 31, 2015 at 12:35 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>>>
>>> http://www.postgresql.org/message-id/CAFj8pRAXcS9B8ABgiM-zauVgGqDhPZOaRz5YSp1_Nhv9HP8nKw@mail.gmail.com
>>>
>>
>> Ah, thanks! Somehow I've missed this mail. You didn't add the patch to
>> a commitfest back then I think?
>>
>
> I had no time to finish this patch - there is few issues in signal
> handling and returning back result - but still I want it :) - and what I
> know - almost all other SQL db has similar functionality.
>

I've updated the patch for the current master and also added some
unexpected parameters handling, so attached is a v2.

I'd say we should hide the so-designed pg_cmdstatus() interface behind more
friendly calls like pg_explain_backend() and pg_backend_progress() to give
some naming examples, to remove the need for magic numbers in the second
arg.

What I've found missing in this approach is the insight into nested
executor runs, so that if you're running a "SELECT my_func()", you only see
this outer query in the pg_cmdstatus() output. With the auto_explain
approach, by hooking into executor I was able to capture the nested queries
and their plans as well.

It's conceptually trivial to add some code to use the Executor hooks here,
but I don't see any precedent for this except for contrib modules
(auto_explain and pg_stat_statements), I'm just not sure if that would be
OK-ish.

And when we solve that, there is another problem of having a sane interface
to query the nested plans. For a psql user, probably the most interesting
would be the topmost (level=1) and the innermost (e.g. level=-1) plans. We
might also want to provide a full nesting of plans in a structured format
like JSON or... *cough* XML, for programs to consume and display nicely
with folding and stuff.

And the most interesting would be making instrumentation work with all of
the above.

I'm adding this to the next CF.

--
Alex