Re: Display individual query in pg_stat_activity

pá 28. 8. 2020 v 10:06 odesílatel Masahiro Ikeda <ikedamsh(at)oss(dot)nttdata(dot)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
> >> <ikedamsh(at)oss(dot)nttdata(dot)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
>