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

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

> Hitting a process with a signal and hoping it will produce a meaningful
> response in all circumstances without disrupting its current task was way
> too naive.
>

Hmm, I would have to disagree, sorry. For me the problem was dynamically
allocating everything at the time the signal is received and getting into
problems when that caused errors.

* INIT - Allocate N areas of memory for use by queries, which can be
expanded/contracted as needed. Keep a freelist of structures.
* OBSERVER - When requested, gain exclusive access to a diagnostic area,
then allocate the designated process to that area, then send a signal
* QUERY - When signal received dump an EXPLAIN ANALYZE to the allocated
diagnostic area, (set flag to show complete, set latch on observer)
* OBSERVER - process data in diagnostic area and then release area for use
by next observation

If the EXPLAIN ANALYZE doesn't fit into the diagnostic chunk, LOG it as a
problem and copy data only up to the size defined. Any other ERRORs that
are caused by this process cause it to fail normally.

That allows the observer to be another backend, or it allows the query
process to perform self-observation based upon a timeout (e.g. >1 hour) or
a row limit (e.g. when an optimizer estimate is seen to be badly wrong).

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