From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>
cc: Rui DeSousa <rui@crazybean.net>,
        Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Handling idle connections
In-reply-to: 
 <CAJk5AtaSH0n_Uc3R2JpfirW0MhHt1SxZNkB-ZuJmTR6VcfALGw@mail.gmail.com>
References: 
 <CAJk5Ata=NXO46e7RcS-CxEXvroz=-fojS+XzpW-Bty+G-sx51A@mail.gmail.com>
 <AB0BD6D8-DFE1-4918-9AF6-0A9A1ED5CEBE@crazybean.net>
 <CAJk5AtaSH0n_Uc3R2JpfirW0MhHt1SxZNkB-ZuJmTR6VcfALGw@mail.gmail.com>
Comments: In-reply-to Rajesh Kumar <rajeshkumar.dba09@gmail.com>
	message dated "Mon, 25 Sep 2023 03:37:25 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <69503.1695612894.1@sss.pgh.pa.us>
Date: Sun, 24 Sep 2023 23:34:54 -0400
Message-ID: <69504.1695612894@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/69504.1695612894%40sss.pgh.pa.us>
Precedence: bulk

Rajesh Kumar <rajeshkumar.dba09@gmail.com> writes:
> 1. I see lot of idle connections where application_name is blank and also
> query is blank, i am identifying only with user. In this case how do I
> identify which application is using idle connection?

If pg_stat_activity.query is empty, then that is a connection that has
*never* issued a query (because once it has, the query column reflects
the last query even when the session is idle).  So apparently you have
a rather broken application that connects and then does nothing.

I'd suggest fixing the client side so that it sets application_name to
something that would help identify the situation.  If the client is
libpq-based, you might be able to do this by setting the PGAPPNAME
environment variable in an invoking script, rather than modifying
any low-level code.

			regards, tom lane