MIME-Version: 1.0
References: <CAAu_hGvBJXOkrjydkOXuDTX8eNMQn9+OMzPT_bcBcT-C9QsPVg@mail.gmail.com>
 <CANzqJaDVz6H=wUqXi72ZieNs3ivTSuYxnM6AYxEKKOMQigU_iw@mail.gmail.com>
In-Reply-To: <CANzqJaDVz6H=wUqXi72ZieNs3ivTSuYxnM6AYxEKKOMQigU_iw@mail.gmail.com>
From: =?UTF-8?B?U8OhbmRvciBEYWt1?= <daku.sandor@gmail.com>
Date: Tue, 25 Feb 2025 10:02:56 +0100
Message-ID: <CAKyoTgZuJe10da9uiG3_V5jZpD_JNw4U3bibn+AiU1=mdOgxOA@mail.gmail.com>
Subject: Re: Deleting idle connections
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ae1ad7062ef3b943"
Archived-At: <https://www.postgresql.org/message-id/CAKyoTgZuJe10da9uiG3_V5jZpD_JNw4U3bibn%2BAiU1%3DmdOgxOA%40mail.gmail.com>
Precedence: bulk

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

On Tue, 25 Feb 2025 at 00:12, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

> On Mon, Feb 24, 2025 at 5:50=E2=80=AFPM Yongye Serkfem <yserkfem@gmail.co=
m> wrote:
>
>> Hi Everyone!
>> I am having a series of idle connections and unable to delete them with =
a
>> single command. Any help in realizing this would be greatly appreciated.
>>
>
> This will kill idle connections older than two hours:
> select pid, pg_terminate_backend(pid)
> from pg_stat_activity
> where state =3D 'idle'
>   and (EXTRACT(epoch FROM now() - backend_start))/3600.0 > 2;
>
> Be warned that it might kill more than you want.  Add more WHERE
> predicates as filter.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>

You could use the state_change timestamp to be sure that the idle
connection is in that state for long enough to be considered really idle.
You can catch and terminate otherwise actively working connections
momentarily in the idle state, if you are not careful enough.

Regards,
S=C3=A1ndor

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, 25 Feb =
2025 at 00:12, Ron Johnson &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com">r=
onljohnsonjr@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Mon, Feb 24, 202=
5 at 5:50=E2=80=AFPM Yongye Serkfem &lt;<a href=3D"mailto:yserkfem@gmail.co=
m" target=3D"_blank">yserkfem@gmail.com</a>&gt; wrote:</div><div class=3D"g=
mail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"l=
tr">Hi Everyone!<div>I am having a series of idle connections and unable to=
 delete them with a single command. Any help in realizing this would be gre=
atly appreciated.</div></div></blockquote><div><br></div><div>This will kil=
l idle connections older than two hours:</div><div><font face=3D"monospace"=
>select pid, pg_terminate_backend(pid) <br>from pg_stat_activity <br>where =
state =3D &#39;idle&#39;<br>=C2=A0 and (EXTRACT(epoch FROM now() - backend_=
start))/3600.0 &gt; 2;</font><br></div><div><br></div><div>Be warned that i=
t might kill more than you want.=C2=A0 Add more WHERE predicates as filter.=
</div><div><br></div></div><span class=3D"gmail_signature_prefix">-- </span=
><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &=
lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still ali=
ve.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></d=
iv></blockquote><div><br></div><div>You could use the state_change timestam=
p to be sure that the idle connection is in that state for long enough to b=
e considered really idle.<br>You can catch and terminate otherwise actively=
 working connections momentarily in the idle state, if you are not=C2=A0car=
eful enough.</div><div><br></div><div>Regards,</div><div>S=C3=A1ndor</div><=
div><br></div><div><br></div></div></div>

--000000000000ae1ad7062ef3b943--