Re: Display individual query in pg_stat_activity

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