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

On 25 September 2015 at 12:13, Shulgin, Oleksandr <
oleksandr(dot)shulgin(at)zalando(dot)de> wrote:

> I now believe that use of ProcessInterrupts() in the recently proposed
> design as well as manipulation of proc latch due to use of shared memory
> queue are major blockers.
>
> In order to simplify things up, I've taken a step back and looked again at
> the auto_explain contrib module. I now propose the most simple thing
> possible in my opinion: a new GUC option for auto_explain. It will make
> every backend, in which the module is loaded via *_preload_libraries
> mechanism or using CREATE EXTENSION/LOAD commands, to actively publish the
> plans of queries in dynamic shared memory as long as
> auto_explain.publish_plans is turned on.
>
> The greatest part for me, is that this approach doesn't require handling
> of signals and is isolated in an external module, so it can be readily used
> with the current server versions, no need to wait for >= 9.6.
>

This is a major change of direction, so the thread title no longer applies
at all.

The requirement is to be able to see the output of EXPLAIN ANALYZE for a
running process. Ideally, we would dump the diagnostic output for any
process that runs longer than a specific timeout, so we can decide whether
to kill it, or just save that for later diagnostics.

I'm interested in helping the original goal happen. Dumping an EXPLAIN,
without ANALYZE info, is not at all interesting since it has no value for
immediate action or post-facto diagnosis, sorry to say - making it happen
for every backend just makes it even less useful.

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