Postgresql 8.4 occasional hang with JDBC access

Question

I have a very database-intensive application which runs for many hours and uses multiple threads, all talking to Postgresql via JDBC. The symptom I'm seeing is that occasionally (one to three times out of every "run") I wind up with one or more stuck JDBC connections, which seem to be waiting on a response from the database but seem to be waiting forever. The thread dump is as follows:

"Thread-4367355" daemon prio=6 tid=0x04920c00 nid=0x1e88 runnable [0x04bef000]
   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:135)
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)
    at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
    at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:255)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1165)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    - locked <0x2c023e10> (a org.postgresql.core.v3.QueryExecutorImpl)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:329)

I would have thought of some kind of locking issue except that many times only ONE thread is stuck. At least one of the queries I've seen in this state was a REINDEX, so it is possible that the query is taking a fair bit of time. In hopes of finding a solution, I upgraded the JDBC driver from 8.4 to 9.1 but the problem still occurs. There is nothing unusual in the Postgresql logs either. Any ideas for further diagnostics (other than using pg_locks)?

Answer 1

There is the obvious thing you could try: to update PostgreSQL itself to version 9.1, too.
You could also log all long running statements, that might give you a clue.

Set log_min_duration_statement = 2000

Or whatever threshold suits you.
I don't know how to interpret the thread dump, but this line looks peculiar:

• locked <0x2c023e10> (a org.postgresql.core.v3.QueryExecutorImpl)

What is locked? And you notice how it misspells "at a org.postgresql.cor...". Is that a copy-paste artefact or the original message? If so, might help to find the origin.