Re: Display individual query in pg_stat_activity

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.
>
> 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

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