postgres connections in IDLE state..

From: "Rajagopalan, Jayashree" <Jayashree(dot)Rajagopalan(at)emc(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: postgres connections in IDLE state..
Date: 2013-10-03 14:00:59
Message-ID: A7A2044B8A59DA4F9F07599B3B1CA1210E4CA0F7@MX101CL02.corp.emc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all:

Our application uses Jboss-hibernate with Postgres as the backend.

I'm seeing intermittently - the DB connections getting stale - and not getting returned to the Hibernate session pool. Some of the connections are as old as 9 days.

The pg_stat shows the connection as IDLE in the DB.
datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port | backend_start | xact_start | query_start | waiting | current_query | ?column?
-------+-----------+---------+----------+---------+------------------+-------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+-----------------------------------------------------------------------------------+-------------------------
22372 | voyencedb | 15874 | 16384 | voyence | | 127.0.0.1 | 54754 | 2013-09-11 15:38:43.706149-05 | | 2013-10-02 11:43:44.410593-05 | f | <IDLE> | 20 days 20:05:00.704444
22372 | voyencedb | 15875 | 16384 | voyence | | 127.0.0.1 | 54755 | 2013-09-11 15:38:43.706275-05 | | 2013-10-02 11:43:44.410764-05 | f | <IDLE> | 20 days 20:05:00.704489

Is there anyway to correlate the Process id - the query that is used - or map it to the threads in the Java thread dumps? Need some lead to debug these IDLE connections.

Any help in this regard is highly appreciated.

Regards
Jayashree

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David Johnston 2013-10-03 14:14:24 Re: postgres connections in IDLE state..
Previous Message Jaime Casanova 2013-10-03 04:47:15 Re: Database replication to 50-80 nodes