Re: Old query lying around in pg_stat_activity for weeks

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