Thread: nested queries vs. pg_stat_activity

nested queries vs. pg_stat_activity

From
legrand legrand
Date:
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

Re: nested queries vs. pg_stat_activity

From
Robert Haas
Date:
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



Re: nested queries vs. pg_stat_activity

From
Magnus Hagander
Date:
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. 

--

Re: nested queries vs. pg_stat_activity

From
Robert Haas
Date:
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



Re: nested queries vs. pg_stat_activity

From
Pavel Stehule
Date:
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