public inbox for [email protected]
help / color / mirror / Atom feedFrom: Edwin UY <[email protected]>
To: [email protected] <[email protected]>
Subject: How to check if session is a hung thread/session
Date: Sun, 10 Nov 2024 10:25:42 +1300
Message-ID: <CA+wokJ_QejkPWUym3njMQCkeFQ_kRcRdah5S2eseocwsHLc-4w@mail.gmail.com> (raw)
--000000000000ac2c2a0626818454
Content-Type: text/plain; charset="UTF-8"
Hi,
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.
When I check the database, it
=> SELECT pg_postmaster_start_time(),
-> pg_postmaster_start_time() at time zone 'utc' at time zone
'Pacific/Auckland' as "Pacific/Auckland TIMEZONE",
-> date_trunc( 'second', current_timestamp -
pg_postmaster_start_time() ) as uptime;
-[ RECORD 1 ]-------------+------------------------------
pg_postmaster_start_time | 2024-11-06 10:44:51.832663+00
Pacific/Auckland TIMEZONE | 2024-11-05 21:44:51.832663+00
uptime | 3 days 10:29:08
So, in theory it really wasn't zero downtime, not sure Aurora RDS takes
care of it but from the output above, it was re-started although
pg_stat_activity shows it is still there.
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)
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: How to check if session is a hung thread/session
In-Reply-To: <CA+wokJ_QejkPWUym3njMQCkeFQ_kRcRdah5S2eseocwsHLc-4w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox