Re: 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 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(dot)toru(at)oss(dot)ntt(dot)co(dot)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(dot)gramlich(at)healthtrio(dot)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(dot)toru(at)oss(dot)ntt(dot)co(dot)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(dot)gramlich(at)healthtrio(dot)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(dot)gramlich(at)healthtrio(dot)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(at)ejurka(dot)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(dot)toru(at)gmail(dot)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(dot)toru(at)oss(dot)ntt(dot)co(dot)jp>
NTT Open Source Software Center

Thanks everyone.

Art Gramlich
Chief Application Architect
HealthTrio, LLC
art(dot)gramlich(at)healthtrio(dot)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(dot)toru(at)oss(dot)ntt(dot)co(dot)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(dot)toru(at)oss(dot)ntt(dot)co(dot)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