pá 28. 8. 2020 v 10:06 odesílatel Masahiro Ikeda <ikeda...@oss.nttdata.com> napsal:
> On 2020-08-19 14:48, Drouvot, Bertrand wrote: > > Hi, > > On 8/18/20 9:35 AM, Pavel Stehule wrote: > > > >> Hi > >> > >> út 18. 8. 2020 v 8:54 odesílatel Masahiro Ikeda > >> <ikeda...@oss.nttdata.com> napsal: > >> > >>> Hi, > >>> > >>>> I've attached a patch to display individual query in the > >>>> pg_stat_activity query field when multiple SQL statements are > >>>> currently displayed. > >>>> > >>>> Motivation: > >>>> > >>>> When multiple statements are displayed then we don’t know > >>> which > >>>> one is currently running. > >>>> > >>>> I'm not sure I'd want that to happen, as it could make it much > >>>> harder to track the activity back to a query in the application > >>>> layer or server logs. > >>>> > >>>> Perhaps a separate field could be added for the current > >>> statement, > >>>> or a value to indicate what the current statement number in the > >>>> query is? > >>> > >>> As a user, I think this feature is useful to users. > >>> > >>> It would be nice that pg_stat_activity also show currently running > >>> query > >>> in a user defined function(PL/pgSQL) . > >>> > >>> I understood that this patch is not for user defined functions. > >>> Please let me know if it's better to make another thread. > > > > Yeah I think it would be nice to have. > > > > I also think it would be better to create a dedicated thread > > (specially looking at Pavel's comment below) > > Thank you. I will. > > >>> In general, PL/pgSQL functions have multiple queries, > >>> and users want to know the progress of query execution, doesn't > >>> it? > >> > >> I am afraid of the significant performance impact of this feature. > >> In this case you have to copy all nested queries to the stat > >> collector process. Very common usage of PL is a glue of very fast > >> queries. Sure, it is used like glue for very slow queries too. > >> Just I thinking about two features: > > OK, thanks for much advice and show alternative solutions. > > >> 1. extra interface for auto_explain, that allows you to get a stack > >> of statements assigned to some pid (probably these informations > >> should be stored inside shared memory and collected before any query > >> execution). Sometimes some slow function is slow due repeated > >> execution of relatively fast queries. In this case, the deeper > >> nested level is not too interesting. You need to see a stack of > >> calls and you are searching the first slow level in the stack. > > Thanks. I didn't know auto_explain module. > I agreed when only requested, it copy the stack of statements. > > >> 2. can be nice to have a status column in pg_stat_activity, and > >> status GUC for sending a custom information from deep levels to the > >> user. Now, users use application_name, but some special variables > >> can be better for this purpose. This value of status can be > >> refreshed periodically and can substitute some tags. So developer > >> can set > >> > >> BEGIN > >> -- before slow long query > >> SET status TO 'slow query calculation xxy %d'; > >> ... > >> > >> It is a alternative to RAISE NOTICE, but with different format - > >> with format that is special for reading from pg_stat_activity > >> > >> For long (slow) queries usually you need to see the sum of all times > >> of all levels from the call stack to get valuable information. > > In comparison to 1, user must implements logging statement to > their query but user can control what he/she wants to know. > > I worry which solution is best. > There is no best solution - @1 doesn't need manual work, but @1 is not too useful when queries are similar (first n chars) and are long. In this case custom messages are much more practical. I don't think so we can implement only one design - in this case we can support more tools with similar purpose but different behaviors in corner cases. > >> p.s. pg_stat_activity is maybe too wide table already, and probably > >> is not good to enhance this table too much > > Thanks. I couldn't think from this point of view. > > After I make some PoC patches, I will create a dedicated thread. > > Regards, > -- > Masahiro Ikeda > NTT DATA CORPORATION >
