Skip site navigation (1) Skip section navigation (2)

executeQuery hangs on busy server - PROPOSAL: socketTimeout parameter

From: Andrea Spinelli <aspinelli(at)imteam(dot)it>
To: pgsql-jdbc(at)postgresql(dot)org
Cc: Alessandro Magnolo <amagnolo(at)imteam(dot)it>, Valerio Mazzoleni <vmazzoleni(at)imteam(dot)it>
Subject: executeQuery hangs on busy server - PROPOSAL: socketTimeout parameter
Date: 2007-08-10 08:24:22
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-jdbc
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:

==, from line 96  ==
    public void changeSocket(Socket socket) throws IOException {
        this.connection = socket;

        // Submitted by Jason Venner <jason(at)idiom(dot)com>. Disable Nagle
        // as we are selective about flushing output only when we
        // really need to.
        // aspinelli(at)imteam(dot)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(at)online(dot)no>
        pg_input = new 
VisibleBufferedInputStream(connection.getInputStream(), 8192);
        pg_output = new 
BufferedOutputStream(connection.getOutputStream(), 8192);

        if (encoding != null)

... 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?

   Andrea Spinelli


pgsql-jdbc by date

Next:From: Kris JurkaDate: 2007-08-10 09:43:47
Subject: Re: executeQuery hangs on busy server - PROPOSAL: socketTimeout parameter
Previous:From: László HornyákDate: 2007-08-10 08:04:39
Subject: Re: statement caching patch from Laszlo Hornyak for review

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group