Re: On-demand running query plans using auto_explain and signals - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: On-demand running query plans using auto_explain and signals |
| Date | |
| Msg-id | CAFj8pRAznQUWpO-yOa7CeRB35weUBJeCH5yq9dSzePJFfK_KMA@mail.gmail.com Whole thread Raw |
| In response to | Re: On-demand running query plans using auto_explain and signals ("Shulgin, Oleksandr" <oleksandr.shulgin@zalando.de>) |
| Responses |
Re: On-demand running query plans using auto_explain and signals
|
| List | pgsql-hackers |
Hi
2015-08-31 19:09 GMT+02:00 Shulgin, Oleksandr <oleksandr.shulgin@zalando.de>:
On Mon, Aug 31, 2015 at 12:35 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:Ah, thanks! Somehow I've missed this mail. You didn't add the patch to a commitfest back then I think?I had no time to finish this patch - there is few issues in signal handling and returning back result - but still I want it :) - and what I know - almost all other SQL db has similar functionality.I've updated the patch for the current master and also added some unexpected parameters handling, so attached is a v2.
Thank you very much
I'd say we should hide the so-designed pg_cmdstatus() interface behind more friendly calls like pg_explain_backend() and pg_backend_progress() to give some naming examples, to remove the need for magic numbers in the second arg.
I had similar idea - this is good enough for start, but target interface iis based on integration with EXPLAIN statement
some like EXPLAIN PROCESS or EXPLAIN PID or EXPLAIN VERBOSE PID ..
What I've found missing in this approach is the insight into nested executor runs, so that if you're running a "SELECT my_func()", you only see this outer query in the pg_cmdstatus() output. With the auto_explain approach, by hooking into executor I was able to capture the nested queries and their plans as well.
I understand - originally I didn't think about nested queries, but it is good idea and probably not a problem:
Not for XML and JSON where we can describe nesting simply
It is little bit harder for plain text - but we can use similar format that is used for subplans or some like
top query:
SELECT fx()
nested (1. level) query:
SELECT ....
It's conceptually trivial to add some code to use the Executor hooks here, but I don't see any precedent for this except for contrib modules (auto_explain and pg_stat_statements), I'm just not sure if that would be OK-ish.And when we solve that, there is another problem of having a sane interface to query the nested plans. For a psql user, probably the most interesting would be the topmost (level=1) and the innermost (e.g. level=-1) plans. We might also want to provide a full nesting of plans in a structured format like JSON or... *cough* XML, for programs to consume and display nicely with folding and stuff.And the most interesting would be making instrumentation work with all of the above.
the important functionality is drawing complete text of query - it was my original motivation, because I had not way how to get complete query before its finishing
Probably the communication between processes should be more complex :( - the SHM queue should be used there, because some plans can be terrible long.
The using shared write buffer (one for all) is too simply solution probably - good for prototype, but not good for core.
I have a idea about communication:
1. caller prepare buffer, shm queue and signalize target process - parameter is pid od caller
2. target process fills a write buffer and close queue
3. caller show data and close buffer, close queue
Now almost all code for communication is in upstream - the missing part is injection one end of queue to any process dynamicaly.
Regards
Pavel
I'm adding this to the next CF.--Alex
pgsql-hackers by date: