Re: Display individual query in pg_stat_activity

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 <mailto: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)

>
> 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:
>
> 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.
>
> 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.
>
> Regards
>
> Pavel
>
> p.s. pg_stat_activity is maybe too wide table already, and probably is
> not good to enhance this table too much
>
>
Thanks

Bertrand

>
> --
> Masahiro Ikeda
> NTT DATA CORPORATION
>
>