Hi,
I have an application that aggregates a variety of (unfortunately
predefined) database schemas into a postgres via JDBC. Some of the
tables are quite large, and some are defined to include hundreds of
columns. I seem reliably to be hitting a bug discussed earlier this year
on the pgsql-jdbc list -- see the threads called "Deadlock detection"
and "Connection hanging on INSERT apparently due to large batch size and
4 CPU cores".
Basically, the Threads that perform my batch inserts get picked off one
by one, and hang forever in a stack that looks like...
> "Thread-3" daemon prio=10 tid=0x00007fc230047000 nid=0x4f48 runnable [0x00007fbcf7370000]
> java.lang.Thread.State: RUNNABLE
> at java.net.SocketOutputStream.socketWrite0(Native Method)
> at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
> at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
> at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
> at java.io.BufferedOutputStream.write(BufferedOutputStream.java:126)
> - locked <0x00007fbdadb57288> (a java.io.BufferedOutputStream)
> at java.io.FilterOutputStream.write(FilterOutputStream.java:97)
> at org.postgresql.core.PGStream.SendInteger4(PGStream.java:189)
> at org.postgresql.core.v3.QueryExecutorImpl.sendBind(QueryExecutorImpl.java:856)
> at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1053)
> at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:644)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:345)
> - locked <0x00007fbdadb5b500> (a org.postgresql.core.v3.QueryExecutorImpl)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2674)
> ...
No clients show up as waiting in pg_stat_activity.
In some of the discussion, it looks as though developers tested a
solution to the problem, but it wasn't clear to me whether any fix made
it to the driver. (I'm running postgres 8.3.8 under 64-bit Fedora 11,
using postgresql-jdbc 8.3.603-2.1.) Is there a fix?
I guess for now I'm going to turn off batches for tables with many
columns, but that will slow things down considerably. Any help or
suggestions would be very gratefully appreciated.
many thanks,
Steve