Thread: nested queries vs. pg_stat_activity
Hello,
An other solution is to expose nested queryid, and to join it with pg_stat_statements.
Actual development trying to add queryid to pg_stat_activity isn't helpfull, because it is only exposing top level one.
Extension pg_stat_sql_plans (github) propose a function called pg_backend_queryid(pid),
that gives the expected queryid (that is stored in shared memory for each backend) ...
Regards
PAscal
On Mon, Aug 10, 2020 at 12:51 PM legrand legrand <legrand_legrand@hotmail.com> wrote: > An other solution is to expose nested queryid, and to join it with pg_stat_statements. > Actual development trying to add queryid to pg_stat_activity isn't helpfull, because it is only exposing top level one. > Extension pg_stat_sql_plans (github) propose a function called pg_backend_queryid(pid), > that gives the expected queryid (that is stored in shared memory for each backend) ... That'd help people using pg_stat_statements, but not others. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Aug 10, 2020 at 9:51 PM Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Aug 10, 2020 at 12:51 PM legrand legrand
<legrand_legrand@hotmail.com> wrote:
> An other solution is to expose nested queryid, and to join it with pg_stat_statements.
> Actual development trying to add queryid to pg_stat_activity isn't helpfull, because it is only exposing top level one.
> Extension pg_stat_sql_plans (github) propose a function called pg_backend_queryid(pid),
> that gives the expected queryid (that is stored in shared memory for each backend) ...
That'd help people using pg_stat_statements, but not others.
Would it even solve the problem for them? pg_stat_statements collects aggregate stats and not a view of what's happening right now -- so it'd be mixing two different types of values. And it would get worse if the same thing is executed multiple times concurrently.
On Mon, Aug 10, 2020 at 4:09 PM Magnus Hagander <magnus@hagander.net> wrote: > Would it even solve the problem for them? pg_stat_statements collects aggregate stats and not a view of what's happeningright now -- so it'd be mixing two different types of values. And it would get worse if the same thing is executedmultiple times concurrently. True. You could find that you have a queryId that had already been evicted from the table. I think it's better to look for a more direct solution to this problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Hi
po 10. 8. 2020 v 22:21 odesílatel Robert Haas <robertmhaas@gmail.com> napsal:
On Mon, Aug 10, 2020 at 4:09 PM Magnus Hagander <magnus@hagander.net> wrote:
> Would it even solve the problem for them? pg_stat_statements collects aggregate stats and not a view of what's happening right now -- so it'd be mixing two different types of values. And it would get worse if the same thing is executed multiple times concurrently.
True. You could find that you have a queryId that had already been
evicted from the table.
I think it's better to look for a more direct solution to this problem.
I am thinking about an extension (but it can be in core too) that does copy query string and execution plan to shared memory to separate buffers per session (before query start). It should eliminate a problem with performance with locks
There can be two functions
show_query(pid int, "top" bool default true) .. it shows query without truncating
show_plan(pid int, "top" bool default true, format text default "text")
When the argument "top" is false, then you can see the current query.
Regards
Pavel
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company