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

Hi

2015-08-31 19:09 GMT+02:00 Shulgin, Oleksandr <oleksandr(dot)shulgin(at)zalando(dot)de>
:

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

Thank you very much

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

I had similar idea - this is good enough for start, but target interface
iis based on integration with EXPLAIN statement

some like EXPLAIN PROCESS or EXPLAIN PID or EXPLAIN VERBOSE PID ..

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

I understand - originally I didn't think about nested queries, but it is
good idea and probably not a problem:

Not for XML and JSON where we can describe nesting simply

It is little bit harder for plain text - but we can use similar format that
is used for subplans or some like

top query:
SELECT fx()

nested (1. level) query:
SELECT ....

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

the important functionality is drawing complete text of query - it was my
original motivation, because I had not way how to get complete query before
its finishing

Probably the communication between processes should be more complex :( -
the SHM queue should be used there, because some plans can be terrible long.

The using shared write buffer (one for all) is too simply solution probably
- good for prototype, but not good for core.

I have a idea about communication:

1. caller prepare buffer, shm queue and signalize target process -
parameter is pid od caller
2. target process fills a write buffer and close queue
3. caller show data and close buffer, close queue

Now almost all code for communication is in upstream - the missing part is
injection one end of queue to any process dynamicaly.

Regards

Pavel

>
> I'm adding this to the next CF.
>
> --
> Alex
>