Thread: JDBC connections very occasionally hang

Hi folks,

I'm seeing something that on the face of it sounds very similar to the
issue reported at
http://archives.postgresql.org/pgsql-general/2011-10/msg00570.php.  I
am using Postgresql 8.4, and the problem occurs with both the 8.4 JDBC
type-3 driver and the 9.1 JDBC type-3 driver.  The test I have that
causes the failure runs for about 3 hours and is highly multithreaded.
 By the end of that time I usually see between one and three "stuck"
threads, all waiting inside the JDBC driver for a response from the
postgresql server.  I can provide a stack trace if requested.  The
actual queries it locks up on differ from run to run; I've seen it
hang on longer-running queries such as a REINDEX, or very basic
queries such as an update, or even on a BEGIN TRANSACTION.  Locking is
not likely to be the problem since the issue occurs with only one
thread involved with fair frequency.  The database is also running on
the same machine as the test client, so that would appear to rule out
network glitches.  Upon failure, there are no errors or warnings
recorded in the postgresql logs either.

Because of the volume of queries it will be difficult to determine by
simply turning on logging whether all the queries are in fact making
it to the server or not.  Is there any other diagnostics you could
recommend?

FWIW, this behavior seems to be new to 8.4; the same software ran
flawlessly and reliably on Postgresql 8.2 and 8.3.

Karl

Karl Wright <daddywri@gmail.com> writes:
> ... By the end of that time I usually see between one and three "stuck"
> threads, all waiting inside the JDBC driver for a response from the
> postgresql server.  I can provide a stack trace if requested.

How about a stack trace from the connected backend?  And what is its
state as shown by the pg_stat_activity and pg_locks views?  It's hard
to tell from what you say here whether the problem is on the server or
client side, which is surely the first thing to isolate.

            regards, tom lane