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
hasbeen 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
