Re: proposal 9.4. Explain on signal

2013/5/16 Thom Brown <thom(at)linux(dot)com>:
> On 16 May 2013 11:09, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> Hello
>>
>> I proposed a some months log plans of cancelled queries
>> http://www.postgresql.org/message-id/CAFj8pRA-DuzkmDtu52CiUgb0P7TVri_B8LtjMJfWcnr1LPts6w@mail.gmail.com
>> . After discussion the proposal was changed to get plan of any running
>> query.
>>
>> I have a proof concept patch now and I am thinking so it can work well
>>
>> So I propose following concept:
>>
>> 1. function pg_explain_backend(PID int, loglevel int default 'log',
>> explain_top_level boolean default true);
>>
>> Execution of this function ensure sending sigusr1 signal to PID process.
>>
>> 2. Sigusr1 handler will be enhanced for PROCSIG_EXPLAIN_MESSAGES
>> message and it will write explain result to log.
>>
>>
>> It share lot of code with auto_explain module. So I am thinking so we
>> should move auto_explain functionality to core. Then EXPLAIN ON SIGNAL
>> can be used for monitoring of query evaluating.
>
> What a neat idea. So the original plan of EXPLAINing cancelled
> queries... does this cater for that? Can cancelled queries
> automatically invoke the EXPLAIN functionality as part of this
> feature?
>

I would to get EXPLAIN of long queries without waiting on end.

So it is possible for manual cancelation (not for timeout)

SELECT pg_explain_backend(xx);
SELECT pg_cancel_backend(xx);

Regards

Pavel

> --
> Thom