Re: ToDo: log plans of cancelled queries

On 11 January 2013 16:31, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2013/1/11 Stephen Frost <sfrost(at)snowman(dot)net>:
>> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>>> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>>> > An even better feature would be to be able to send a signal to a
>>> > running query to log its currently executing plan. That way you can
>>> > ask "Why so slow?" before deciding to kill it.
>>>
>>> That could conceivably work. At least it wouldn't require running
>>> EXPLAIN in a failed transaction.
>>
>> I like this idea, in general, also. Taking that to the next level would
>> be figuring out how you can do the same kind of thing through an
>> interactive psql session where the user running the query doesn't need
>> access to the database server or PG log files...
>>
>
> this is simple - it can be printed via elog(WARNING, ...) to original console
>
>> We can send a 'cancel query', how about a 'report on query' which
>> returns the plan and perhaps whatever other stats are easily available?
>
> there is only one question - that POSIX signal we can use?

We already overload the signals, so its just a new type for the signal
handler to cope with.

See procsignal_sigusr1_handler()

If we do it this way we can have time-based explain logging, so log
anything that hits 60 minutes etc..

Stephen's idea of an additional protocol message to support this is
fine, but I wouldn't want to limit this capability so it can only be
invoked from the client. I'd like a sysadmin be able to enquire about
other sessions.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services