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