Hello everybody,
I am using jdbc to access a large database on a busy PostgreSQL server
(8.1) hosted on Windows, from several web applications.
Occasionally, my executeQuery hang forever. They block on a read
operation in SocketInputStream.socketRead0 (seen on the debugger) and
stay there for _days_. They disappear only by stopping the application
server (Tomcat).
This means that the corresponding thread in the application server stops
functioning, and after hours or days all threads are consumed and the
web applications stop working.
I tried to interrupt the threads, but the abovementioned operation is
not interruptible (neither by Thread.interrupt() nor Thread.stop() ).
After a lot of googling, I took all my courage, downloaded the jdbc
sources and recompiled the driver with the following patch:
== PGStream.java, from line 96 ==
public void changeSocket(Socket socket) throws IOException {
this.connection = socket;
// Submitted by Jason Venner <jason@idiom.com>. Disable Nagle
// as we are selective about flushing output only when we
// really need to.
connection.setTcpNoDelay(true);
// aspinelli@imteam.it 2007-08-09
// on slow servers connections used to hang on a read
connection.setSoTimeout( 120000 );
// Buffer sizes submitted by Sverre H Huseby <sverrehu@online.no>
pg_input = new
VisibleBufferedInputStream(connection.getInputStream(), 8192);
pg_output = new
BufferedOutputStream(connection.getOutputStream(), 8192);
if (encoding != null)
setEncoding(encoding);
}
====
... and all goes well!
Long queries (>2min) fail with a SQLException signalling a communication
problem with the server - which is what I want.
I think this could be reworked into one more connection parameter
"socketTimeout", where 0 means no timeout; the value of the parameter
would of course substitute the hard-coded 120000.
I've seen a feature request on GBorg about stopping long-running
queries, which could be satisfied by what I'm proposing. (actually, the
connection parameter works connection-wide, while the feature request
deals with single queries).
I have browsed the sources and I think I can volunteer the code, if the
developer team agrees. The core change is as simple as the lines above.
However, there is quite a bit of modifications needed to bring the value
of the parameter from org.postgresql.Driver down to PGStream, but I
think it is not terribly difficult. I really need this patch, so I
would have to apply it again at every new version of the driver - I
prefer to donate a hundred lines of code to the project!
Another question is - *why* do the queries hang? They are syntactically
correct - they almost always work. Probably one of the two parties
(driver and server) does some mistake which induces the driver to think
there is data available, but the data is not there; a protocol bug? But
this is outside my competence.
I would appreciate any comment - any developer there?
TIA
Andrea Spinelli