public inbox for [email protected]
help / color / mirror / Atom feedFrom: Edwin UY <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: How to check if session is a hung thread/session
Date: Mon, 11 Nov 2024 16:05:21 +1300
Message-ID: <CA+wokJ9dEeYg0X15mVP+5RUkqTzQ6QU_OrOJ7o--esvnPVFSWA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CA+wokJ_QejkPWUym3njMQCkeFQ_kRcRdah5S2eseocwsHLc-4w@mail.gmail.com>
<[email protected]>
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 <[email protected]>
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
>
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], [email protected]
Subject: Re: How to check if session is a hung thread/session
In-Reply-To: <CA+wokJ9dEeYg0X15mVP+5RUkqTzQ6QU_OrOJ7o--esvnPVFSWA@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