Re: How to check if session is a hung thread/session

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 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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 supposed
> 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 = '[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 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
> 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
>