Message-ID: <6af4a50d5a942117f2c76add61c37d2a55810a3f.camel@cybertec.at>
Subject: Re: How to check if session is a hung thread/session
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Edwin UY <edwin.uy@gmail.com>, "pgsql-admin@lists.postgresql.org"
	 <pgsql-admin@lists.postgresql.org>
Date: Sun, 10 Nov 2024 06:29:44 +0100
In-Reply-To: 
 <CA+wokJ_QejkPWUym3njMQCkeFQ_kRcRdah5S2eseocwsHLc-4w@mail.gmail.com>
References: 
	<CA+wokJ_QejkPWUym3njMQCkeFQ_kRcRdah5S2eseocwsHLc-4w@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-2.fc40) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/6af4a50d5a942117f2c76add61c37d2a55810a3f.camel%40cybertec.at>
Precedence: bulk

On Sun, 2024-11-10 at 10:25 +1300, Edwin UY wrote:
> I am not sure how to explain this, but I believe this is something usuall=
y referred to in the JDBC world as a hung thread.
>=20
> This is an Aurora RDS PostgreSQL database that was patched in a supposed =
to be zero downtime kind of thing.
> After the patching, I have a user complaining that he has a job that norm=
ally gets run in the OS cron session that has been failing.
>=20
> Checking pg_stat_activity
>=20
> select pid as process_id,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0usename as username,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0datname as database_name,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0client_addr as client_address,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0application_name,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0now() - pg_stat_activity.query_start AS durati=
on,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0backend_start,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0backend_start at time zone 'Pacific/Auckland' =
as backend_start_localtime,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0state,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0state_change,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0state_change at time zone 'Pacific/Auckland' a=
s state_change_localtime
> from pg_stat_activity
> where usename =3D '[blah]'
> order by username asc
>=20
> I have the output as below:
>=20
> =C2=A0process_id | username =C2=A0| database_name | client_address | =C2=
=A0 =C2=A0application_name =C2=A0 =C2=A0| =C2=A0 =C2=A0duration =C2=A0 =C2=
=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 backend_start =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 | =C2=A0backend_start_localtime =C2=A0 | state | =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 state_change =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 state_change_lo=
caltime
> ------------+-----------+---------------+----------------+---------------=
---------+-----------------+-------------------------------+---------------=
-------------+-------+-------------------------------+---------------------=
-------
> =C2=A0 =C2=A0 =C2=A0 31947 | [blah] | [blah] =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
| [blah] =C2=A0=C2=A0 | PostgreSQL JDBC Driver | 00:47:21.838892 | 2024-11-=
06 10:44:53.309388+00 | 2024-11-06 23:44:53.309388 | idle =C2=A0| 2024-11-0=
8 02:00:06.005173+00 | 2024-11-08 15:00:06.005173
> (1 row)
>=20
> From the pg_stat_output, the backend_start has not changed for several da=
ys since the patching, so I am 'guessing'
> it may have gone lost/rogue already. Is there any way to check that this =
is the case before I kill it.
> The state_change date is getting updated though, I don't know if this is =
proof that it is not a hung thread.

You should look at the "state_change" column for the session to see how lon=
g it has been idle.

At any rate, an idle session is not hanging - at least not in the database.=
  Perhaps you got the
wrong session, or perhaps the client has got a problem.

I'd say that killing the session won't resolve any hanging thread on the cl=
ient side.
Still, if you kill it, you'd at worst cause an error on the client side.

Yours,
Laurenz Albe