Re: Display individual query in pg_stat_activity

Hi,

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you can confirm the sender and know the content is safe.

Hi

On Mon, Jul 27, 2020 at 3:40 PM Drouvot, Bertrand <bdrouvot@amazon.com> wrote:

Hi hackers,

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?

Thanks for he feedback.

I like the idea of adding extra information without changing the current behavior.

A value to indicate what the current statement number is, would need parsing the query field by the user to get the individual statement.

I think the separate field makes sense (though it come with an extra memory price) as it will not change the existing behavior and would just provide extra information (without any extra parsing needed for the user).

I attached a mock up v2 patch that adds this new field.

Outcome Examples:

  backend_type  |                                            query                                            |   individual_query
----------------+---------------------------------------------------------------------------------------------+----------------------
 client backend | select backend_type, query, individual_query from pg_stat_activity where length(query) > 0; |
 client backend | select pg_sleep(10);select pg_sleep(20);                                                    | select pg_sleep(20);

or

  backend_type   |                                                                                              query                                                                                               |           individual_query
-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------
 client backend  | select backend_type, query, individual_query from pg_stat_activity where length(query) > 0;                                                                                                      |
 client backend  | select test();                                                                                                                                                                                   |
 parallel worker | select count(*) as "first" from foo;select pg_sleep(10);create index bdtidx on foo(generate_series);select count(*) as "second" from foo;select pg_sleep(11);select count(*) as "third" from foo | select count(*) as "second" from foo;
 parallel worker | select count(*) as "first" from foo;select pg_sleep(10);create index bdtidx on foo(generate_series);select count(*) as "second" from foo;select pg_sleep(11);select count(*) as "third" from foo | select count(*) as "second" from foo;

As you can see the individual_query field is populated only when the query field is a multiple statements one.

Regards,

Bertrand

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com