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

On Thu, Dec 24, 2015 at 1:57 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>
> 2015-12-24 3:23 GMT+01:00 Michael Paquier <michael(dot)paquier(at)gmail(dot)com>:
>>
>> On Thu, Dec 17, 2015 at 6:45 PM, Shulgin, Oleksandr
>> <oleksandr(dot)shulgin(at)zalando(dot)de> wrote:
>> > On Wed, Dec 16, 2015 at 8:39 PM, Tomas Vondra
>> > <tomas(dot)vondra(at)2ndquadrant(dot)com>
>> > wrote:
>> >> On 12/01/2015 10:34 AM, Shulgin, Oleksandr wrote:
>> >>>
>> >>>
>> >>> I have the plans to make something from this on top of
>> >>> pg_stat_statements and auto_explain, as I've mentioned last time. The
>> >>> next iteration will be based on the two latest patches above, so it
>> >>> still makes sense to review them.
>> >>>
>> >>> As for EXPLAIN ANALYZE support, it will require changes to core, but
>> >>> this can be done separately.
>> >>
>> >> I'm re-reading the thread, and I have to admit I'm utterly confused
>> >> what
>> >> is the current plan, what features it's supposed to provide and whether
>> >> it
>> >> will solve the use case I'm most interested in. Oleksandr, could you
>> >> please
>> >> post a summary explaining that?
>> >>
>> >> My use case for this functionality is debugging of long-running
>> >> queries,
>> >> particularly getting EXPLAIN ANALYZE for them. In such cases I either
>> >> can't
>> >> run the EXPLAIN ANALYZE manually because it will either run forever
>> >> (just
>> >> like the query) and may not be the same (e.g. due to recent ANALYZE).
>> >> So we
>> >> need to extract the data from the process executing the query.
>> >>
>> >> I'm not essentially opposed to doing this in an extension (better an
>> >> extension than nothing), but I don't quite see how you could to do that
>> >> from
>> >> pg_stat_statements or auto_explain. AFAIK both extensions merely use
>> >> hooks
>> >> before/after the executor, and therefore can't do anything in between
>> >> (while
>> >> the query is actually running).
>> >>
>> >> Perhaps you don't intend to solve this particular use case? Which use
>> >> cases are you aiming to solve, then? Could you explain?
>> >
>> > Thanks for your interest in this patch!
>> >
>> > My motivation is the same as your use case: having a long-running query,
>> > be
>> > able to look inside this exact query run by this exact backend.
>> >
>> > I admit the evolution of ideas in this patch can be very confusing: we
>> > were
>> > trying a number of different approaches, w/o thinking deeply on the
>> > implications, just to have a proof of concept.
>>
>> It's great to see ideas of concepts and things to help address those
>> issues, at least we are agreeing that there is a hole in the
>> instrumentation and that it would be nice to fill it with something.
>> Still, it is not completely clear which approach would be taken. Is it
>> fair to mark the current patch as returned with feedback then?
>
>
> +1

So, done this way. We could always move it to next CF if someone
wishes to move on. But at this point that would be a different
approach than what was proposed first..
--
Michael