MIME-Version: 1.0
References: 
 <CA+wokJ_QejkPWUym3njMQCkeFQ_kRcRdah5S2eseocwsHLc-4w@mail.gmail.com>
 <6af4a50d5a942117f2c76add61c37d2a55810a3f.camel@cybertec.at>
In-Reply-To: <6af4a50d5a942117f2c76add61c37d2a55810a3f.camel@cybertec.at>
From: Edwin UY <edwin.uy@gmail.com>
Date: Mon, 11 Nov 2024 16:05:21 +1300
Message-ID: 
 <CA+wokJ9dEeYg0X15mVP+5RUkqTzQ6QU_OrOJ7o--esvnPVFSWA@mail.gmail.com>
Subject: Re: How to check if session is a hung thread/session
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002f0bac06269a617d"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BwokJ9dEeYg0X15mVP%2B5RUkqTzQ6QU_OrOJ7o--esvnPVFSWA%40mail.gmail.com>
Precedence: bulk

--0000000000002f0bac06269a617d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Laurenz

Thanks for your reply.

When you say "You should look at the "state_change" column for the session
to see how long it has been idle."

Do you mean compare the time between state_change whether they change or
that? Will have to script that somehow.


On Sun, Nov 10, 2024 at 6:29=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec=
.at>
wrote:

> 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
> usually referred to in the JDBC world as a hung thread.
> >
> > This is an Aurora RDS PostgreSQL database that was patched in a suppose=
d
> to be zero downtime kind of thing.
> > After the patching, I have a user complaining that he has a job that
> normally gets run in the OS cron session that has been failing.
> >
> > Checking pg_stat_activity
> >
> > select pid as process_id,
> >        usename as username,
> >        datname as database_name,
> >        client_addr as client_address,
> >        application_name,
> >        now() - pg_stat_activity.query_start AS duration,
> >        backend_start,
> >        backend_start at time zone 'Pacific/Auckland' as
> backend_start_localtime,
> >        state,
> >        state_change,
> >        state_change at time zone 'Pacific/Auckland' as
> state_change_localtime
> > from pg_stat_activity
> > where usename =3D '[blah]'
> > order by username asc
> >
> > I have the output as below:
> >
> >  process_id | username  | database_name | client_address |
>  application_name    |    duration     |         backend_start         |
>  backend_start_localtime   | state |         state_change          |
> state_change_localtime
> >
> ------------+-----------+---------------+----------------+---------------=
---------+-----------------+-------------------------------+---------------=
-------------+-------+-------------------------------+---------------------=
-------
> >       31947 | [blah] | [blah]         | [blah]    | PostgreSQL JDBC
> Driver | 00:47:21.838892 | 2024-11-06 10:44:53.309388+00 | 2024-11-06
> 23:44:53.309388 | idle  | 2024-11-08 02:00:06.005173+00 | 2024-11-08
> 15:00:06.005173
> > (1 row)
> >
> > From the pg_stat_output, the backend_start has not changed for several
> days since the patching, so I am 'guessing'
> > it may have gone lost/rogue already. Is there any way to check that thi=
s
> is the case before I kill it.
> > The state_change date is getting updated though, I don't know if this i=
s
> proof that it is not a hung thread.
>
> You should look at the "state_change" column for the session to see how
> long 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
> client side.
> Still, if you kill it, you'd at worst cause an error on the client side.
>
> Yours,
> Laurenz Albe
>

--0000000000002f0bac06269a617d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi Laurenz</div><div><br></div><div>
Thanks for your reply.=C2=A0</div><div><br></div><div>When you say &quot;Yo=
u should look at the &quot;state_change&quot; column for the session to see=
 how long it has been idle.&quot;</div><div><br></div><div>Do you mean comp=
are the time between state_change whether they change or that? Will have to=
 script that somehow. <br></div><div><br></div></div><br><div class=3D"gmai=
l_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, Nov 10, 2024 at 6:29=
=E2=80=AFPM Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">la=
urenz.albe@cybertec.at</a>&gt; wrote:<br></div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex">On Sun, 2024-11-10 at 10:25 +1300, Edwin UY wrote:<br=
>
&gt; I am not sure how to explain this, but I believe this is something usu=
ally referred to in the JDBC world as a hung thread.<br>
&gt; <br>
&gt; This is an Aurora RDS PostgreSQL database that was patched in a suppos=
ed to be zero downtime kind of thing.<br>
&gt; After the patching, I have a user complaining that he has a job that n=
ormally gets run in the OS cron session that has been failing.<br>
&gt; <br>
&gt; Checking pg_stat_activity<br>
&gt; <br>
&gt; select pid as process_id,<br>
&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0usename as username,<br>
&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0datname as database_name,<br>
&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0client_addr as client_address,<br>
&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0application_name,<br>
&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0now() - pg_stat_activity.query_start AS dur=
ation,<br>
&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0backend_start,<br>
&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0backend_start at time zone &#39;Pacific/Auc=
kland&#39; as backend_start_localtime,<br>
&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0state,<br>
&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0state_change,<br>
&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0state_change at time zone &#39;Pacific/Auck=
land&#39; as state_change_localtime<br>
&gt; from pg_stat_activity<br>
&gt; where usename =3D &#39;[blah]&#39;<br>
&gt; order by username asc<br>
&gt; <br>
&gt; I have the output as below:<br>
&gt; <br>
&gt; =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=
_localtime<br>
&gt; ------------+-----------+---------------+----------------+------------=
------------+-----------------+-------------------------------+------------=
----------------+-------+-------------------------------+------------------=
----------<br>
&gt; =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-08 02:00:06.005173+00 | 2024-11-08 15:00:06.005173<br>
&gt; (1 row)<br>
&gt; <br>
&gt; From the pg_stat_output, the backend_start has not changed for several=
 days since the patching, so I am &#39;guessing&#39;<br>
&gt; it may have gone lost/rogue already. Is there any way to check that th=
is is the case before I kill it.<br>
&gt; The state_change date is getting updated though, I don&#39;t know if t=
his is proof that it is not a hung thread.<br>
<br>
You should look at the &quot;state_change&quot; column for the session to s=
ee how long it has been idle.<br>
<br>
At any rate, an idle session is not hanging - at least not in the database.=
=C2=A0 Perhaps you got the<br>
wrong session, or perhaps the client has got a problem.<br>
<br>
I&#39;d say that killing the session won&#39;t resolve any hanging thread o=
n the client side.<br>
Still, if you kill it, you&#39;d at worst cause an error on the client side=
.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--0000000000002f0bac06269a617d--