Message-ID: <e86ac186ec9da52ed932f8e6debbb51ade4b6d85.camel@cybertec.at>
Subject: Re: Old query lying around in pg_stat_activity for weeks
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Julien Rouhaud <rjuju123@gmail.com>, Jacque Edmund
 <jacqueedmund@gmail.com>
Cc: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Date: Fri, 26 Mar 2021 13:38:18 +0100
In-Reply-To: <20210326112528.6uiso3o2fgk73rqg@nol>
References: 
	<CANOmo00m4BDU1EqXQ6HAc8N7uQoiwo1zM=2BnR3Mq+K3hkGCHw@mail.gmail.com>
	 <20210326112528.6uiso3o2fgk73rqg@nol>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/e86ac186ec9da52ed932f8e6debbb51ade4b6d85.camel%40cybertec.at>
Precedence: bulk

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