Thread: IDLE queries taking up space

On Aug 30, 2011, at 10:03 AM, JD Wong wrote:

> How can I set postgres to qutomatically close connections that have finished their queries and now sit idle?

They haven't finished their queries. They've opened transactions, and then are sitting there doing nothing. In other
words,this is a bug in your clients, and no, you really would not want PG automatically terminating connections
mid-transactionjust because it thought the client was taking too long to get to the next step. 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

On 08/30/2011 02:13 PM, Scott Ribe wrote:
> On Aug 30, 2011, at 10:03 AM, JD Wong wrote:
>
>> How can I set postgres to qutomatically close connections that have finished their queries and now sit idle?
AFAIK you can't, you should check |pg_terminate_backend function and see
if it is useful for you
|
> They haven't finished their queries. They've opened transactions, and then are sitting there doing nothing. In other
words,this is a bug in your clients, and no, you really would not want PG automatically terminating connections
mid-transactionjust because it thought the client was taking too long to get to the next step. 
<IDLE> in transaction is what you mean...
<IDLE> are clients connected but not running any query
>

On Tue, Aug 30, 2011 at 11:03 AM, JD Wong <jdmswong@gmail.com> wrote:
> Hi,
> When I run select datname, procpid, current_query from pg_stat_activity; I
> get 26 rows of <IDLE> queries.  How can I set postgres to qutomatically
> close connections that have finished their queries and now sit idle?

you don't. this should be managed from the client, or you can use a
connection pool.

merlin

On 31/08/2011 12:03 AM, JD Wong wrote:
> Hi,
>
> When I run select datname, procpid, current_query from
> pg_stat_activity; I get 26 rows of <IDLE> queries.  How can I set
> postgres to qutomatically close connections that have finished their
> queries and now sit idle?

If they're not idle in transaction, they don't matter much.

Try filtering the list based on last activity, so you only see those
connections that have been idle for a while. Short periods of idle are
normal for many applications because they perform a query then process
its results and issue other queries based on the results, or because
they keep a connection around between requests from users.

If the idle connections are actually causing an issue - for example, if
they're preventing the release of non-trivial amounts of backend private
memory back to the OS - you can tweak the client to disconnect after a
certain idle time, or you can use a connection pool. Connection pools
may be inside the client (for example, in Java EE application servers)
or between the client and the server using tools like pgbouncer and
PgPool-II.

--
Craig Ringer