Re: Hung thread - Mailing list pgsql-jdbc

Dave Cramer <pg@fastcrypt.com> wrote:
> On 28 October 2014 07:34, dhaval jaiswal <dhavallj@hotmail.com> wrote:

>> java.lang.Thread.State: RUNNABLE
>>        at java.net.SocketInputStream.socketRead0(Native Method)
>>        at java.net.SocketInputStream.read(SocketInputStream.java:129)
>>        at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:145)
>>        at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:114)
>>        at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
>>        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:274)
>>        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1660)
>>        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
>>        - locked <0x00002aaaf6282d00> (a org.postgresql.core.v3.QueryExecutorImpl)
>>        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
>>        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
>>        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
>>        at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
>>        at org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:84)
>>        at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96)
>>        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
>>        at com.eos.hotels.viahotel.manager.ViaHotelDatabaseManager.getConnection(ViaHotelDatabaseManager.java:74)
[ snip ]

> Given the little bit of information available my guess would be
> that since this is using dbcp, and the connections are never really
> closed.

The stack trace shows that the getConnection() method of dbcp is
trying to re-use a connection in the pool, and is first testing the
connection by invoking our JDBC driver's executeQuery() method.

> I believe once a connection is made we are always listening
> on the socket.

I don't follow that; it is listening for the response to a query.
What thread would be "always listening", anyway?  The driver is not
multi-threaded.

> However I will say I haven't really spent much time on this. Either
> way we would need much more information in order to be able to help
> you.

It would be interesting to know what query dbcp is using to test
whether the connection is still alive.  Also, if the client port
can be discovered, looking for it in the pg_stat_activity view
would be interesting.

I can think of one halfway plausible explanation, though.  A
firewall between the machine running dbcp and the database server
might be blocking traffic after some idle timeout, possibly
resulting in a hang like this when the pooler eventually tries to
use the connection again.  You might want to try configuring TCP
timeouts on both the client and the server.  It wouldn't hurt to
closely review the network path between the machines.

Logging connections and disconnections and closely reviewing the
server logs could provide insights.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company