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.
