Hi 2015-08-31 19:09 GMT+02:00 Shulgin, Oleksandr <oleksandr.shul...@zalando.de> :
> On Mon, Aug 31, 2015 at 12:35 PM, Pavel Stehule <pavel.steh...@gmail.com> > wrote: > >> >>>> >>>> http://www.postgresql.org/message-id/cafj8praxcs9b8abgim-zauvggqdhpzoarz5ysp1_nhv9hp8...@mail.gmail.com >>>> >>> >>> 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 >
