pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOld query lying around in pg_stat_activity for weeks
3+ messages / 3 participants
[nested] [flat]
* Old query lying around in pg_stat_activity for weeks
@ 2021-03-26 11:16 Jacque Edmund <jacqueedmund@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jacque Edmund @ 2021-03-26 11:16 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Was alerted to an old query lying around in the pg_stat_activity view on a
little used server recently. It ha no values for wait_event_type or
wait_event and had an idle state value. The query_start value was about
two weeks old. Odd, but after verifying it had no pg_lock lookup, I
canceled it.
Might there be an infrequent staleness of items that appear in the
pg_stat_activity view that is just understood to be there from time to time?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Old query lying around in pg_stat_activity for weeks
@ 2021-03-26 11:25 Julien Rouhaud <rjuju123@gmail.com>
parent: Jacque Edmund <jacqueedmund@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Julien Rouhaud @ 2021-03-26 11:25 UTC (permalink / raw)
To: Jacque Edmund <jacqueedmund@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Fri, Mar 26, 2021 at 07:16:42AM -0400, Jacque Edmund wrote:
> Was alerted to an old query lying around in the pg_stat_activity view on a
> little used server recently. It ha no values for wait_event_type or
> wait_event and had an idle state value. The query_start value was about
> two weeks old. Odd, but after verifying it had no pg_lock lookup, I
> canceled it.
>
> Might there be an infrequent staleness of items that appear in the
> pg_stat_activity view that is just understood to be there from time to time?
If the state is "idle" it means it's only a connection doing nothing, it's not
really consuming any resources. You also can't cancel it, since it isn't doing
anything, just close it.
The best thing to do if you don't have persistent connections or a connection
pooler is to check where the connection coming from and investigate what could
be the client/application responsible for leaking an idle connection.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Old query lying around in pg_stat_activity for weeks
@ 2021-03-26 12:38 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2021-03-26 12:38 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; Jacque Edmund <jacqueedmund@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Fri, 2021-03-26 at 19:25 +0800, Julien Rouhaud wrote:
> On Fri, Mar 26, 2021 at 07:16:42AM -0400, Jacque Edmund wrote:
> > Was alerted to an old query lying around in the pg_stat_activity view on a
> > little used server recently. It ha no values for wait_event_type or
> > wait_event and had an idle state value. The query_start value was about
> > two weeks old. Odd, but after verifying it had no pg_lock lookup, I
> > canceled it.
> >
> > Might there be an infrequent staleness of items that appear in the
> > pg_stat_activity view that is just understood to be there from time to time?
>
> If the state is "idle" it means it's only a connection doing nothing, it's not
> really consuming any resources. You also can't cancel it, since it isn't doing
> anything, just close it.
>
> The best thing to do if you don't have persistent connections or a connection
> pooler is to check where the connection coming from and investigate what could
> be the client/application responsible for leaking an idle connection.
I guess Jacque is confused by the "query" column.
It does not necessarily show a query that is currently running, it just
shows the most recent query in the session.
If "state" is "idle", the query is already finished.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-03-26 12:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-26 11:16 Old query lying around in pg_stat_activity for weeks Jacque Edmund <jacqueedmund@gmail.com>
2021-03-26 11:25 ` Julien Rouhaud <rjuju123@gmail.com>
2021-03-26 12:38 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox