MIME-Version: 1.0
References: 
 <CA+wokJ_QejkPWUym3njMQCkeFQ_kRcRdah5S2eseocwsHLc-4w@mail.gmail.com>
 <CAKFQuwZunKoiM4AO0BBAbRc73_unJoMJA5WmPFWYnUWj+s0Q_Q@mail.gmail.com>
 <CA+wokJ96V5UuHhPStt+eMigkXsn7RJbMaiL-aK1K=S5aFQg66g@mail.gmail.com>
 <216310.1731300338@sss.pgh.pa.us>
In-Reply-To: <216310.1731300338@sss.pgh.pa.us>
From: Edwin UY <edwin.uy@gmail.com>
Date: Mon, 11 Nov 2024 19:45:29 +1300
Message-ID: 
 <CA+wokJ8VFQ1AkaqaCuND9A-+Xgq1rhdXioXNzQm7CSGPA5x2EQ@mail.gmail.com>
Subject: Re: How to check if session is a hung thread/session
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
	"pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000007b3d4d06269d74f1"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BwokJ8VFQ1AkaqaCuND9A-%2BXgq1rhdXioXNzQm7CSGPA5x2EQ%40mail.gmail.com>
Precedence: bulk

--0000000000007b3d4d06269d74f1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thanks Tom,

OK, I've decided to 'painfully' look at the PostgreSQL RDS logs and it is
showing something like below.
There seems to be a locking/deadlock issue of some sort somewhere.
I have checked the other days' log prior to the patching and these appear
to be a 'normal' occurrence for this database and it wasn't affecting the
application nonetheless.
After the patching, it starts affecting the application. Not sure what else
I can check on the Aurora PostgreSQL RDS end. I may request them to restart
the app server.

[25751]:LOG:  process 25751 still waiting for ShareLock on transaction
114953443 after 1000.054 ms
[25751]:DETAIL:  Process holding the lock: 22297. Wait queue: 25751.
[25751]:CONTEXT:  while locking tuple (1,17) in relation "[table_name]"
[25751]:STATEMENT:  [SQL_STATEMENT] for update
[25751]:LOG:  process 25751 acquired ShareLock on transaction 114953443
after 4756.967 ms
[25751]:CONTEXT:  while locking tuple (1,17) in relation " [table_name] "


On Mon, Nov 11, 2024 at 5:45=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Edwin UY <edwin.uy@gmail.com> writes:
> > I thought it could be the backend has sent something back to the client
> but
> > it never received it and it just kept on doing the same at some
> intervals.
>
> Your pg_stat_activity output shows the backend is idle, meaning it's
> waiting for a client command.  While the session has been around for
> days, we can see the last client command was about 47 minutes ago,
> from your "now() - pg_stat_activity.query_start AS duration" column.
> I see no reason to think there is anything interesting here at all,
> except for a client that is sitting doing nothing for long periods.
>
>                         regards, tom lane
>

--0000000000007b3d4d06269d74f1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div>Thanks Tom,</div><d=
iv><br></div><div>OK, I&#39;ve decided to &#39;painfully&#39; look at the P=
ostgreSQL RDS logs and it is showing something like below. <br></div><div>T=
here seems to be a locking/deadlock issue of some sort somewhere. <br></div=
><div>I have checked the other days&#39; log prior to the patching and thes=
e appear to be a &#39;normal&#39; occurrence for this database and it wasn&=
#39;t affecting the application nonetheless.</div><div>After the patching, =
it starts affecting the application. Not sure what else I can check on the =
Aurora PostgreSQL RDS end. I may request them to restart the app server.<br=
></div><div><br></div><div><span style=3D"font-family:monospace"></span></d=
iv><div><span style=3D"font-family:monospace">[25751]:LOG: =C2=A0process 25=
751 still waiting for ShareLock on transaction 114953443 after 1000.054 ms<=
/span></div><div><span style=3D"font-family:monospace"></span></div><div><s=
pan style=3D"font-family:monospace"></span></div><div><span style=3D"font-f=
amily:monospace"></span></div><div><span style=3D"font-family:monospace"></=
span></div><div><span style=3D"font-family:monospace">[25751]:DETAIL: =C2=
=A0Process holding the lock: 22297. Wait queue: 25751.</span></div><div><sp=
an style=3D"font-family:monospace"></span></div><div><span style=3D"font-fa=
mily:monospace"></span></div><div><span style=3D"font-family:monospace">[25=
751]:CONTEXT: =C2=A0while locking tuple (1,17) in relation &quot;[table_nam=
e]&quot;</span></div><div><span style=3D"font-family:monospace"></span></di=
v><span style=3D"font-family:monospace">[25751]:STATEMENT:=C2=A0 [SQL_STATE=
MENT] for update<br>[25751]:LOG: =C2=A0process 25751 acquired ShareLock on =
transaction 114953443 after 4756.967 ms<br>[25751]:CONTEXT: =C2=A0while loc=
king tuple (1,17) in relation &quot;</span>
<span style=3D"font-family:monospace">[table_name]</span>

<span style=3D"font-family:monospace">&quot;</span><br><div><br></div></div=
><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Mo=
n, Nov 11, 2024 at 5:45=E2=80=AFPM Tom Lane &lt;<a href=3D"mailto:tgl@sss.p=
gh.pa.us" target=3D"_blank">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><bloc=
kquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:=
1px solid rgb(204,204,204);padding-left:1ex">Edwin UY &lt;<a href=3D"mailto=
:edwin.uy@gmail.com" target=3D"_blank">edwin.uy@gmail.com</a>&gt; writes:<b=
r>
&gt; I thought it could be the backend has sent something back to the clien=
t but<br>
&gt; it never received it and it just kept on doing the same at some interv=
als.<br>
<br>
Your pg_stat_activity output shows the backend is idle, meaning it&#39;s<br=
>
waiting for a client command.=C2=A0 While the session has been around for<b=
r>
days, we can see the last client command was about 47 minutes ago,<br>
from your &quot;now() - pg_stat_activity.query_start AS duration&quot; colu=
mn.<br>
I see no reason to think there is anything interesting here at all,<br>
except for a client that is sitting doing nothing for long periods.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div></div>
</div>

--0000000000007b3d4d06269d74f1--