Re: Hung JDBC connections

Thanks for replying Dave.

Is this setTimeout new in the 9.1 version of the driver? We have been
using the latest 9.0 driver. Does the user of the JDBC API need to set
this timeout manually somehow? Does the driver maintain some sort of
keepalive communication with the server?

Again, it is very strange that the OS (for all client applications) did
not detect that the socket closed despite the fact that the server
rebooted. That's why I was wondering if an application level keepalive
was being used. We are using Sun's JVM, 1.6.0_30.

Mike

On 06/08/2012 02:18 PM, Dave Cramer wrote:
> Mike,
>
> The latest version of the driver does have setTimeout implemented.
>
> Curious who's java implementation are you using ?
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Fri, Jun 8, 2012 at 10:20 AM, Mike Charnoky<noky(at)nextbus(dot)com> wrote:
>> Hi,
>>
>> Yesterday we experienced a problem whereby hundreds of JDBC client
>> connections remained in a hung state, despite the fact that the PostgreSQL
>> server was actually rebooted. This is disconcerting because our client
>> applications have a db failover facility, but that feature won't work unless
>> existing JDBC connections (most of which are pooled) fail in the first
>> place. We had to instead manually restart all client applications to
>> eliminate all the hung connections.
>>
>> Here are the details:
>> * server: PostgreSQL v8.3 on CentOS 5.4
>> * clients: JDBC v9.0-802.jdbc4, Java 1.6.0_30, on various CentOS 5.x
>> releases
>>
>> The strange thing is that on the client side, according to "netstat" the TCP
>> connections between the JDBC clients and PostgreSQL were still in the
>> ESTABLISHED state (even though the server was rebooted). A thread dump of
>> the a typical hung client (one performing DB updates) looked like this, it
>> was stuck in a socket read. I was further surprised that some test clients
>> (which I did not restart yesterday) are STILL in this hung state this
>> morning, 18 hours after the incident!
>>
>> "DBThrottle-" prio=10 tid=0xad1ff400 nid=0x24b8 runnable [0xad2ad000]
>> 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:407)
>> - locked<0xb243db20> (a org.postgresql.core.v3.QueryExecutorImpl)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2725)
>> at xxx.xxxxxxx.util.DBThrottle.sqlLoop(DBThrottle.java:401)
>> at xxx.xxxxxxx.util.DBThrottle.access$000(DBThrottle.java:59)
>> at xxx.xxxxxxx.util.DBThrottle$1.run(DBThrottle.java:199)
>>
>> Is there a way this hang can be addressed by the JDBC driver? Again, this is
>> very disconcerting that the clients did not detect the server failure. Does
>> the connection use any sort of timeout mechanism? Thank you for your help.
>>
>>
>> Mike
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc