Re: Display individual query in pg_stat_activity - Mailing list pgsql-hackers

Hi,

Hi

út 18. 8. 2020 v 8:54 odesílatel Masahiro Ikeda <ikedamsh@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)

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