Thread: Patch to add a socketTimeout property.
Several jdbc drivers have recently added a way to set the SOTimeout on the connections socket. For those of us accessing databases over a unreliable connections, this can be quite helpful. Attached is a patch to add support for an additional url property "socketTimeout" and property in the DataSource classes. The property is in seconds (like jtds and IBM DB2's driver and unlike the Oracle driver). Art Gramlich Chief Application Architect HealthTrio, LLC art.gramlich@healthtrio.com
Attachment
Art Gramlich wrote: > Several jdbc drivers have recently added a way to set the SOTimeout on > the connections socket. For those of us accessing databases over a > unreliable connections, this can be quite helpful. Good. Although I submitted setKeepAlive patch to solve network down two month ago[1], it is not accepted so far. I think socket timeout is reasonable solution and easy to use. [1] http://archives.postgresql.org/pgsql-jdbc/2008-02/msg00022.php Best regards, -- Toru SHIMOGAKI<shimogaki.toru@oss.ntt.co.jp> NTT Open Source Software Center
Hopefully, it will work for your problem also. We access several remote databases on the other side of a vpn and network problems used to cause a lot of issues. With some other drivers, we have been able to set this property and when these occur, flag in our connection pools that there are network problems and shut them down until the problem is corrected. Art Gramlich Chief Application Architect HealthTrio, LLC art.gramlich@healthtrio.com On Apr 9, 2008, at 9:36 PM, Toru SHIMOGAKI wrote: > > Art Gramlich wrote: >> Several jdbc drivers have recently added a way to set the >> SOTimeout on the connections socket. For those of us accessing >> databases over a unreliable connections, this can be quite helpful. > > Good. Although I submitted setKeepAlive patch to solve network down > two month ago[1], it is not accepted so far. I think socket timeout > is reasonable solution and easy to use. > > [1] http://archives.postgresql.org/pgsql-jdbc/2008-02/msg00022.php > > Best regards, > > -- > Toru SHIMOGAKI<shimogaki.toru@oss.ntt.co.jp> > NTT Open Source Software Center >
On Thu, 10 Apr 2008, Toru SHIMOGAKI wrote: > > Art Gramlich wrote: >> Several jdbc drivers have recently added a way to set the SOTimeout on the >> connections socket. For those of us accessing databases over a unreliable >> connections, this can be quite helpful. > > Good. Although I submitted setKeepAlive patch to solve network down two month > ago[1], it is not accepted so far. I think socket timeout is reasonable > solution and easy to use. > It seems like keepalives are a better solution to dead server detection. Don't timeouts just cause problems when you have a long running query? Kris Jurka
They do. We usually have different connection pools for normal (short running) and long queries. Since almost all are short running, this has worked well for us. I actually haven't tried setting keepalive in this situation. so I can't say much, but the vpn solutions we have used have at times gotten into some pretty wierd states and sotimeouts could stop our pools from going crazy. Art Gramlich Chief Application Architect HealthTrio, LLC art.gramlich@healthtrio.com On Apr 10, 2008, at 11:31 AM, Kris Jurka wrote: > > > On Thu, 10 Apr 2008, Toru SHIMOGAKI wrote: > >> >> Art Gramlich wrote: >>> Several jdbc drivers have recently added a way to set the >>> SOTimeout on the connections socket. For those of us accessing >>> databases over a unreliable connections, this can be quite helpful. >> >> Good. Although I submitted setKeepAlive patch to solve network down >> two month ago[1], it is not accepted so far. I think socket timeout >> is reasonable solution and easy to use. >> > > It seems like keepalives are a better solution to dead server > detection. Don't timeouts just cause problems when you have a long > running query? > > Kris Jurka
On Thu, 10 Apr 2008, Art Gramlich wrote: > They do. We usually have different connection pools for normal (short > running) and long queries. Since almost all are short running, this has > worked well for us. I actually haven't tried setting keepalive in this > situation. so I can't say much, but the vpn solutions we have used have at > times gotten into some pretty wierd states and sotimeouts could stop our > pools from going crazy. > I can see how keepalives are generally useful while sotimeouts are only useful in the specific situation where you can set a global limit on your query times. On the other hand the sotimeout is configurable while the keepalive timeout isn't configurable (at least in Java). I'm not particularly excited about either option, but I don't need them either. I don't see a danger with exposing them as options as long as the pros and cons are clearly explained. Kris Jurka
I agree completely that keepalive is the more correct solution but that the ability to set the timeout to something reasonable is a plus for sotimeout. That said, I'll take a shot at listing a few pros and cons for allowing access to either. Cons: 1. It could be confusing to a developer when the connection if a connection is dropped (especially when doing a long running query). 2. I'm assuming (and please correct me if not) that postgresql will continue to run a long query until trying to access the socket again. If true, code could be written where it leads to a user submitting the same long running queries to the database thinking there is just a transient problem. Pros: 1. Either or both are useful in unusual/unreliable networking situations. 2. With correct settings and infrastructure, allowing the socket close itself, can keep a connection pool from filling and threads from hanging. 3. Setting sotimeout is available in some other major drivers (Oracle odbc5, IBM db2jcc and jdts). Art Gramlich Chief Application Architect HealthTrio, LLC art.gramlich@healthtrio.com On Apr 10, 2008, at 5:51 PM, Kris Jurka wrote: > > > On Thu, 10 Apr 2008, Art Gramlich wrote: > >> They do. We usually have different connection pools for normal >> (short running) and long queries. Since almost all are short >> running, this has worked well for us. I actually haven't tried >> setting keepalive in this situation. so I can't say much, but the >> vpn solutions we have used have at times gotten into some pretty >> wierd states and sotimeouts could stop our pools from going crazy. >> > > I can see how keepalives are generally useful while sotimeouts are > only useful in the specific situation where you can set a global > limit on your query times. On the other hand the sotimeout is > configurable while the keepalive timeout isn't configurable (at > least in Java). I'm not particularly excited about either option, > but I don't need them either. > I don't see a danger with exposing them as options as long as the > pros and cons are clearly explained. > > Kris Jurka
2008/4/11, Kris Jurka <books@ejurka.com>:
> I don't see a danger with exposing them as options as long as the pros and
> cons are clearly explained.
How about the following list to compare keepalive and socket timeout?
Please add other topics, if any.
keepalive socket timeout
------------------------------------------------------------------
Configurability No in Java Yes
(depend on OS setting)
Target All connections Each connection
Standard timer 2 hours Depend on users
setting
Influence All of applications Long running query
on a same OS using a same connection
Overhead Network for CPU for timer
keepalive proves
On the other hand, should we provide the both properties and users
chose either of them depend on their requirement?
--
Toru SHIMOGAKI <shimogaki.toru@gmail.com>
On Sat, 12 Apr 2008, Toru SHIMOGAKI wrote: > On the other hand, should we provide the both properties and users > chose either of them depend on their requirement? > My inclination is to apply the keepalive patch now and see where Oliver is with implementing setQueryTimeout. Right now keepalives provide functionality we can't get and isn't covered by the JDBC API while sotimeout is just a brute force global query timeout. If we're going to get real query timeout support soon then we don't need to bother with sotimeout. Oliver are you still planning to work on query timeouts as discussed here? http://archives.postgresql.org/pgsql-jdbc/2008-02/msg00114.php Kris Jurka
Kris Jurka wrote: > Oliver are you still planning to work on query timeouts as discussed here? > http://archives.postgresql.org/pgsql-jdbc/2008-02/msg00114.php It's still on my todo list but I've been too swamped with other work to make any progress on it yet :( -O
On Mon, 14 Apr 2008, Oliver Jowett wrote: > Kris Jurka wrote: > >> Oliver are you still planning to work on query timeouts as discussed here? >> http://archives.postgresql.org/pgsql-jdbc/2008-02/msg00114.php > > It's still on my todo list but I've been too swamped with other work to make > any progress on it yet :( > OK, then I'll hold the sotimeout patch until close to the 8.4 release. If query timeouts are still unimplemented (or we only have the soft version), we'll re-open the discussion for this patch. Kris Jurka
Kris, Thank you for applying the keepalive patch. Kris Jurka wrote: > OK, then I'll hold the sotimeout patch until close to the 8.4 release. > If query timeouts are still unimplemented (or we only have the soft > version), we'll re-open the discussion for this patch. Agreed. I hope the duscussion will go ahead. -- Toru SHIMOGAKI<shimogaki.toru@oss.ntt.co.jp> NTT Open Source Software Center
Thanks everyone. Art Gramlich Chief Application Architect HealthTrio, LLC art.gramlich@healthtrio.com On Apr 13, 2008, at 10:28 PM, Toru SHIMOGAKI wrote: > > Kris, > > Thank you for applying the keepalive patch. > > Kris Jurka wrote: > >> OK, then I'll hold the sotimeout patch until close to the 8.4 >> release. If query timeouts are still unimplemented (or we only >> have the soft version), we'll re-open the discussion for this patch. > > Agreed. I hope the duscussion will go ahead. > > -- > Toru SHIMOGAKI<shimogaki.toru@oss.ntt.co.jp> > NTT Open Source Software Center >
Hi, In the following thread: http://archives.postgresql.org/pgsql-jdbc/2008-04/msg00080.php Kris Jurka wrote: > OK, then I'll hold the sotimeout patch until close to the 8.4 release. > If query timeouts are still unimplemented (or we only have the soft > version), we'll re-open the discussion for this patch. The 8.4 will be released soon, so I think it's time to re-open the discussion; Does anyone try to implement setQueryTimeout()? Should we commit the sotimeout patch? regards, -- Toru SHIMOGAKI<shimogaki.toru@oss.ntt.co.jp> NTT Open Source Software Center
On Thu, 28 May 2009, Toru SHIMOGAKI wrote: >> OK, then I'll hold the sotimeout patch until close to the 8.4 release. If >> query timeouts are still unimplemented (or we only have the soft version), >> we'll re-open the discussion for this patch. > > The 8.4 will be released soon, so I think it's time to re-open the > discussion; Does anyone try to implement setQueryTimeout()? Should we commit > the sotimeout patch? No progress has been maded on setQueryTimeout, so I've commited the sotimeout patch. Kris Jurka