SEO

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



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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...2/msg00022.php

Best regards,

--
Toru SHIMOGAKI<shimogaki.toru@oss.ntt.co.jp>
NTT Open Source Software Center


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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...2/msg00022.php
>
> Best regards,
>
> --
> Toru SHIMOGAKI<shimogaki.toru@oss.ntt.co.jp>
> NTT Open Source Software Center
>


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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>

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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...2/msg00114.php

Kris Jurka

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc