Thread: JDBC gripe list

JDBC gripe list

From
Dave Cramer
Date:
Just got back from pgeast. Seems everyone seems to have a gripe about
the JDBC driver.

My sense is that for basic read/write/update/delete the driver works
just fine. However I'd like to compile a list of everyone's gripe.

Dave

Re: JDBC gripe list

From
dmp
Date:
Dave Cramer wrote:
> Just got back from pgeast. Seems everyone seems to have a gripe about
> the JDBC driver.
>
> My sense is that for basic read/write/update/delete the driver works
> just fine. However I'd like to compile a list of everyone's gripe.
>
> Dave

Perhaps some elaboration on the types of gripes Dave would be more
informative on what's going on.

The only comment I would have to say I guess is the notice in the last
year or so is an expansion of consistent contributors at the database
server project. The same does not appear to be happening with the JDBC.
Looks like reports are coming in and source modification request, but
no real additional consistent people to help out in the day to day
activities. It appears that Kris is the only one left to guardian the
JDBC through from release to release. Perhaps my assessment is wrong,
but when a project gets so big then it takes almost one or two people
full time, or that number of man hours, to insure it stays on track
with all the basic upkeep.

Dana M. Proctor
MyJSQLView Project Manager



Re: JDBC gripe list

From
Simon Riggs
Date:
On Fri, Mar 25, 2011 at 12:35 AM, Dave Cramer <pg@fastcrypt.com> wrote:
> Just got back from pgeast. Seems everyone seems to have a gripe about
> the JDBC driver.
>
> My sense is that for basic read/write/update/delete the driver works
> just fine. However I'd like to compile a list of everyone's gripe.

Funny that, I just got back from a different conference where people
were griping about it also.

Didn't get a detailed list but numbers of less impressed people all
active Postgres users and supporters.

JDBC4 was mentioned.

Will look to collect more info.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: JDBC gripe list

From
Dave Cramer
Date:
On Thu, Mar 24, 2011 at 9:38 PM, dmp <danap@ttc-cmc.net> wrote:
> Dave Cramer wrote:
>>
>> Just got back from pgeast. Seems everyone seems to have a gripe about
>> the JDBC driver.
>>
>> My sense is that for basic read/write/update/delete the driver works
>> just fine. However I'd like to compile a list of everyone's gripe.
>>
>> Dave
>
> Perhaps some elaboration on the types of gripes Dave would be more
> informative on what's going on.
>
> The only comment I would have to say I guess is the notice in the last
> year or so is an expansion of consistent contributors at the database
> server project. The same does not appear to be happening with the JDBC.
> Looks like reports are coming in and source modification request, but
> no real additional consistent people to help out in the day to day
> activities. It appears that Kris is the only one left to guardian the
> JDBC through from release to release. Perhaps my assessment is wrong,
> but when a project gets so big then it takes almost one or two people
> full time, or that number of man hours, to insure it stays on track
> with all the basic upkeep.
>
> Dana M. Proctor
> MyJSQLView Project Manager
>


There are 3 committers on the JDBC project. Currently Kris has been
shouldering the load, but both Oliver and I are still here.

As far as the types of gripes I heard, there was nothing really
specific except perhaps statement timeout. There was one comment about
"when were we going to get a good JDBC driver". So this is an attempt
to build a list so that we can see the magnitude


Dave

Re: JDBC gripe list

From
Dave Cramer
Date:
On Fri, Mar 25, 2011 at 4:07 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Fri, Mar 25, 2011 at 12:35 AM, Dave Cramer <pg@fastcrypt.com> wrote:
>> Just got back from pgeast. Seems everyone seems to have a gripe about
>> the JDBC driver.
>>
>> My sense is that for basic read/write/update/delete the driver works
>> just fine. However I'd like to compile a list of everyone's gripe.
>
> Funny that, I just got back from a different conference where people
> were griping about it also.
>
> Didn't get a detailed list but numbers of less impressed people all
> active Postgres users and supporters.
>
> JDBC4 was mentioned.

So thinking about this. JDBC4 does nothing to really add to the 4
basic operations. What I am thinking is that many other tools require
JDBC4 and as a result ours doesn't work well ?

>
> Will look to collect more info.

Thanks!

Dave
http://www.credativ.ca

Re: JDBC gripe list

From
Oliver Jowett
Date:
On 25 March 2011 23:28, Dave Cramer <pg@fastcrypt.com> wrote:
> There are 3 committers on the JDBC project. Currently Kris has been
> shouldering the load, but both Oliver and I are still here.

FWIW I don't really have any time to work on the driver these days
unfortunately - I try to answer the occasional email, but I don't have
the time to do much beyond that.

Oliver

Re: JDBC gripe list

From
Dave Cramer
Date:
Scott,

Thanks I actually think this one is tractable.

Dave

On Fri, Mar 25, 2011 at 9:46 AM, Mackay, Scott <scott.mackay@progeny.net> wrote:
> Unless it has changed, my only real gripe is the lack of batch insert capabilities.  We have to do some ugly things
sincewe cannot get IDs returned from batch inserts. 
>
> -Scott
>
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org on behalf of Dave Cramer
> Sent: Fri 3/25/2011 6:30 AM
> To: Simon Riggs
> Cc: List
> Subject: (nwl)  Re: [JDBC] JDBC gripe list
>
> On Fri, Mar 25, 2011 at 4:07 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> On Fri, Mar 25, 2011 at 12:35 AM, Dave Cramer <pg@fastcrypt.com> wrote:
>>> Just got back from pgeast. Seems everyone seems to have a gripe about
>>> the JDBC driver.
>>>
>>> My sense is that for basic read/write/update/delete the driver works
>>> just fine. However I'd like to compile a list of everyone's gripe.
>>
>> Funny that, I just got back from a different conference where people
>> were griping about it also.
>>
>> Didn't get a detailed list but numbers of less impressed people all
>> active Postgres users and supporters.
>>
>> JDBC4 was mentioned.
>
> So thinking about this. JDBC4 does nothing to really add to the 4
> basic operations. What I am thinking is that many other tools require
> JDBC4 and as a result ours doesn't work well ?
>
>>
>> Will look to collect more info.
>
> Thanks!
>
> Dave
> http://www.credativ.ca
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

Re: JDBC gripe list

From
"Mackay, Scott"
Date:
Unless it has changed, my only real gripe is the lack of batch insert capabilities.  We have to do some ugly things
sincewe cannot get IDs returned from batch inserts. 

-Scott


-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org on behalf of Dave Cramer
Sent: Fri 3/25/2011 6:30 AM
To: Simon Riggs
Cc: List
Subject: (nwl)  Re: [JDBC] JDBC gripe list

On Fri, Mar 25, 2011 at 4:07 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Fri, Mar 25, 2011 at 12:35 AM, Dave Cramer <pg@fastcrypt.com> wrote:
>> Just got back from pgeast. Seems everyone seems to have a gripe about
>> the JDBC driver.
>>
>> My sense is that for basic read/write/update/delete the driver works
>> just fine. However I'd like to compile a list of everyone's gripe.
>
> Funny that, I just got back from a different conference where people
> were griping about it also.
>
> Didn't get a detailed list but numbers of less impressed people all
> active Postgres users and supporters.
>
> JDBC4 was mentioned.

So thinking about this. JDBC4 does nothing to really add to the 4
basic operations. What I am thinking is that many other tools require
JDBC4 and as a result ours doesn't work well ?

>
> Will look to collect more info.

Thanks!

Dave
http://www.credativ.ca

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


Re: JDBC gripe list

From
"MauMau"
Date:
Hello,

----- Original Message -----
From: "Dave Cramer" <pg@fastcrypt.com>
> My sense is that for basic read/write/update/delete the driver works
> just fine. However I'd like to compile a list of everyone's gripe.

Thank you for giving us an excellent JDBC driver. I've been learning
PostgreSQL JDBC driver and PostgreSQL internals these days. Please let me
add some items.

1. Better JDBC4 support
I'd like the driver to support more JDBC4 features, regardless of whether
they are actually useful. Just lack of support could lead to underevaluation
when compared to other databases such as Oracle and HSQLDB (IIRC, HSQLDB
provides better JDBC4 support). I love PostgreSQL and don't want my
colleagues to underevaluate PostgreSQL.

2. Ability to run regression tests against the driver located in an
arbitorary place
The current regression test builds the driver and uses it. I want to run the
regression test against an installed driver like "make installcheck" of
PostgreSQL. With that feature, I can make sure that the installation media
contains a correct driver and we performed packaging procedure with no
problem.

Regards
Maumu


Re: JDBC gripe list

From
Dave Cramer
Date:
On Fri, Mar 25, 2011 at 4:37 PM, MauMau <maumau307@gmail.com> wrote:
> Hello,
>
> ----- Original Message ----- From: "Dave Cramer" <pg@fastcrypt.com>
>>
>> My sense is that for basic read/write/update/delete the driver works
>> just fine. However I'd like to compile a list of everyone's gripe.
>
> Thank you for giving us an excellent JDBC driver. I've been learning
> PostgreSQL JDBC driver and PostgreSQL internals these days. Please let me
> add some items.
>
> 1. Better JDBC4 support
> I'd like the driver to support more JDBC4 features, regardless of whether
> they are actually useful. Just lack of support could lead to underevaluation
> when compared to other databases such as Oracle and HSQLDB (IIRC, HSQLDB
> provides better JDBC4 support). I love PostgreSQL and don't want my
> colleagues to underevaluate PostgreSQL.

So looking at some of the stuff in JDBC4 one simple thing is that many
times we throw not implemented when we should be throwing
SqlFeatureNotSupported.

There are some things that seem intractable such as getRowID I guess
we could try to return oid if it were there.


>
> 2. Ability to run regression tests against the driver located in an
> arbitorary place
> The current regression test builds the driver and uses it. I want to run the
> regression test against an installed driver like "make installcheck" of
> PostgreSQL. With that feature, I can make sure that the installation media
> contains a correct driver and we performed packaging procedure with no
> problem.

This is in the ant build file and probably fairly easy to do.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
>
> Regards
> Maumu
>
>

Re: JDBC gripe list

From
Mike Fowler
Date:
On 26/03/11 14:35, Dave Cramer wrote:
> On Fri, Mar 25, 2011 at 4:37 PM, MauMau<maumau307@gmail.com>  wrote:
>> 1. Better JDBC4 support
>> I'd like the driver to support more JDBC4 features, regardless of whether
>> they are actually useful. Just lack of support could lead to underevaluation
>> when compared to other databases such as Oracle and HSQLDB (IIRC, HSQLDB
>> provides better JDBC4 support). I love PostgreSQL and don't want my
>> colleagues to underevaluate PostgreSQL.
> So looking at some of the stuff in JDBC4 one simple thing is that many
> times we throw not implemented when we should be throwing
> SqlFeatureNotSupported.

That may very well help, I did a grep last month of all the places where
the not implemented exception is as a way to work out what work could be
done (see
http://archives.postgresql.org/pgsql-jdbc/2011-02/msg00087.php). Though
I've only had a little time to look since, there were some features that
I thought just weren't possible with Postgres so perhaps it's worth
discussing what features can be supported and need developing and which
methods should be changed to throw the better exception.

One feature that I noticed that isn't implemented is named parameters on
prepared statements. From a cursory glance I believe that it is possible
to that with Postgres, but I note that it's never been implemented for
JDBC 2 and upwards. Unless someone tells me it's technically not
possible I'd be prepared to invest some time over the coming weeks to
add that feature.

Regards,

--
Mike Fowler
Registered Linux user: 379787


Re: JDBC gripe list

From
Dave Cramer
Date:
On Sat, Mar 26, 2011 at 5:35 PM, Mike Fowler <mike@mlfowler.com> wrote:
> On 26/03/11 14:35, Dave Cramer wrote:
>>
>> On Fri, Mar 25, 2011 at 4:37 PM, MauMau<maumau307@gmail.com>  wrote:
>>>
>>> 1. Better JDBC4 support
>>> I'd like the driver to support more JDBC4 features, regardless of whether
>>> they are actually useful. Just lack of support could lead to
>>> underevaluation
>>> when compared to other databases such as Oracle and HSQLDB (IIRC, HSQLDB
>>> provides better JDBC4 support). I love PostgreSQL and don't want my
>>> colleagues to underevaluate PostgreSQL.
>>
>> So looking at some of the stuff in JDBC4 one simple thing is that many
>> times we throw not implemented when we should be throwing
>> SqlFeatureNotSupported.
>
> That may very well help, I did a grep last month of all the places where the
> not implemented exception is as a way to work out what work could be done
> (see http://archives.postgresql.org/pgsql-jdbc/2011-02/msg00087.php). Though
> I've only had a little time to look since, there were some features that I
> thought just weren't possible with Postgres so perhaps it's worth discussing
> what features can be supported and need developing and which methods should
> be changed to throw the better exception.
>
> One feature that I noticed that isn't implemented is named parameters on
> prepared statements. From a cursory glance I believe that it is possible to
> that with Postgres, but I note that it's never been implemented for JDBC 2
> and upwards. Unless someone tells me it's technically not possible I'd be
> prepared to invest some time over the coming weeks to add that feature.
>
> Regards,
>
> --
> Mike Fowler
> Registered Linux user: 379787


Thanks Mike, that would be great!


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

Re: JDBC gripe list

From
"Kevin Grittner"
Date:
Dave Cramer  wrote:

> My sense is that for basic read/write/update/delete the driver
> works just fine. However I'd like to compile a list of everyone's
> gripe.

At the top of my list is the need to use a cursor to avoid
materializing the entire result set in heap during execution of an
execute method.  Pulling data off the wire should be done in
ResultSet.next() method.  Yes I know this is not a trivial change,
and yes I know that it means that you can get errors during the
next() method which currently happen during execute.

There are several reasons this can improve performance, as well as
eliminating a commonly reported problem with OutOfMemoryError
exceptions when people don't know about the issue or accidentally
miss one of the requirements.

-Kevin

Re: JDBC gripe list

From
Oliver Jowett
Date:
On 28 March 2011 02:31, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
> Dave Cramer  wrote:
>
>> My sense is that for basic read/write/update/delete the driver
>> works just fine. However I'd like to compile a list of everyone's
>> gripe.
>
> At the top of my list is the need to use a cursor to avoid
> materializing the entire result set in heap during execution of an
> execute method.  Pulling data off the wire should be done in
> ResultSet.next() method.

Do you mean something beyond the batching you can do by setting a
suitable fetchsize?
Or do you mean you want to lift the restrictions on when that batching
can actually be used?
Or something else?

Oliver

Re: JDBC gripe list

From
Dave Cramer
Date:
Adding to the list is statement timeout. I seem to recall the issue is
we do not want to spawn a thread for each statement which seems to be
the only way to do this. Alternatively we could use timers, but I
presume they just spawn threads as well.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca




On Thu, Mar 24, 2011 at 8:35 PM, Dave Cramer <pg@fastcrypt.com> wrote:
> Just got back from pgeast. Seems everyone seems to have a gripe about
> the JDBC driver.
>
> My sense is that for basic read/write/update/delete the driver works
> just fine. However I'd like to compile a list of everyone's gripe.
>
> Dave
>

Re: JDBC gripe list

From
Thomas Kellerer
Date:
Oliver Jowett wrote on 27.03.2011 16:15:
>> At the top of my list is the need to use a cursor to avoid
>> materializing the entire result set in heap during execution of an
>> execute method.  Pulling data off the wire should be done in
>> ResultSet.next() method.
>
> Do you mean something beyond the batching you can do by setting a
> suitable fetchsize?
> Or do you mean you want to lift the restrictions on when that batching
> can actually be used?
> Or something else?
>
I agree with Dave here.

Having a default that prevent the entire result to be loaded into the client memory does make a lot of sense to me.

Regards
Thomas

Re: JDBC gripe list

From
"Kevin Grittner"
Date:
> Oliver Jowett  wrote:
> Kevin Grittner  wrote:
>> Dave Cramer wrote:
>>
>>> My sense is that for basic read/write/update/delete the driver
>>> works just fine. However I'd like to compile a list of everyone's
>>> gripe.
>>
>> At the top of my list is the need to use a cursor to avoid
>> materializing the entire result set in heap during execution of an
>> execute method. Pulling data off the wire should be done in
>> ResultSet.next() method.
>
> Do you mean something beyond the batching you can do by setting a
> suitable fetchsize?
> Or do you mean you want to lift the restrictions on when that
> batching can actually be used?

I'm talking about doing this even when a cursor isn't used, no
fetchSize is set, and without the restrictions (like autoCommit being
off and the query string being a single statement).  Unfortunately I
didn't see the other thread on this topic before I responded to this
thread, so we should probably let the point die on this thread.

-Kevin

Re: JDBC gripe list

From
"Kevin Grittner"
Date:
> Dave Cramer  wrote:
>
> Adding to the list is statement timeout. I seem to recall the issue
> is we do not want to spawn a thread for each statement which seems
> to be the only way to do this. Alternatively we could use timers,
> but I presume they just spawn threads as well.

No, see the java.util.Timer docs:

http://download.oracle.com/javase/6/docs/api/java/util/Timer.html

Each Timer object uses one thread and can handle thousands of pending
timers.  If you're using JDBC you're on a JVM, and those all know how
to multi-thread gracefully.  A daemon thread for a group of timers
should be no big deal.

We had a patch submitted for this, which looked to me like it was
pretty close to committable.  I raised some concerns and was
intending to look at it again, but haven't gotten the requisite round
tuits yet.

-Kevin

Re: JDBC gripe list

From
"MauMau"
Date:
Hello,

I read the JDBC driver's source code, documentation, and the project web
site to compile the missing features and wanted improvements. Attached file
is the list of things I noticed. Some of the items may be on the project web
site. I hope this will be a bit useful.

The driver version I investigated is postgresql-jdbc-9.0-801.

I'm interested in discussing which features can be feasible with the current
PostgreSQL server and which features require new functionality of the
server. However, as I'm relatively new to PostgreSQL, I may fail to keep up
with your level of discussions.

Regards
MauMau


Attachment

Re: JDBC gripe list

From
Dave Cramer
Date:
MauMau,

That's quite a list! Thanks.

So in general I'm having trouble understanding things where the
implementation says we can do whatever we want you have a requirement
? For example getGeneratedKeys from a batch statement? What is it you
are looking for ? AFAICT we are implementing the spec here. The fact
that mysql does something else is irrelevant. Please note this is just
a talking point since I do believe it is possible for us to return the
generated keys here.


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

Re: JDBC gripe list

From
"MauMau"
Date:
Hello, Dave,

From: "Dave Cramer" <pg@fastcrypt.com>
> So in general I'm having trouble understanding things where the
> implementation says we can do whatever we want you have a requirement
> ? For example getGeneratedKeys from a batch statement? What is it you
> are looking for ? AFAICT we are implementing the spec here. The fact
> that mysql does something else is irrelevant. Please note this is just
> a talking point since I do believe it is possible for us to return the
> generated keys here.
>
>
> Dave Cramer

Thank you for your quick reply. I'm sorry, but please let me reconsider and
tell my opinion tomorrow. I want to practice my presentation for tomorrow's
important meeting.

MauMau


Re: JDBC gripe list

From
Craig Ringer
Date:
On 03/25/2011 04:07 PM, Simon Riggs wrote:
> On Fri, Mar 25, 2011 at 12:35 AM, Dave Cramer<pg@fastcrypt.com>  wrote:
>> Just got back from pgeast. Seems everyone seems to have a gripe about
>> the JDBC driver.
>>
>> My sense is that for basic read/write/update/delete the driver works
>> just fine. However I'd like to compile a list of everyone's gripe.
>
> Funny that, I just got back from a different conference where people
> were griping about it also.
>
> Didn't get a detailed list but numbers of less impressed people all
> active Postgres users and supporters.

Personally, I think the JDBC driver is fine. It's not perfect, but I
hardly feel justified "demanding" a perfect driver when I haven't
contributed anything to its creation. People appear to be very happy to
complain, but rather less willing to knuckle down and help!

I'm not using the JDBC driver heavily and generally find that it does
the job quite well. Also, a least I put in the effort to fix one issue -
the core code's handling of client certs requests when multiple certs
are in the Keystore. Not that anyone seems to _use_ X.509 client
certificates...

The only actual issues I've run into, none of which were exactly
showstoppers though all of which cost me significant time finding
workarounds, are:

- (biggest problem) The JDBC driver interacts poorly with PostgreSQL 8.3
and above's removal of implicit casts when using extended Pg types,
user-defined types, etc. It's often desirable to represent a type in
Java as String, BigDecimal, etc, though it's backed by a domain or a
custom type on the Pg side. The most painful time I've had with this was
working with Pg's xml type, which I needed to represent Java-side as a
string. This is ... painful ... with PgJDBC, especially if you're using
a midlayer like JPA2 so you can't use setObject(...) directly. When it's
a user-defined type you can define implicit casts, but it's IMO
unreasonable to have to go messing with the system catalog just to be
able to convert from xml <-> string for example.

Try writing a simple test program that handles the 'xml' type from
Hibernate or JPA to see what I mean.

IMO the server shouldn't be being so strict about implicit conversions
*from* string to certain string-like types like xml. Since that's
apparently not going to change, it'd be wonderful if the JDBC driver had
a way to detect the destination types and send data with the right type
when passed a string representation of the type.



- Inability to reliably receive notifications without risking blocking.
It's not possible to check for notifications w/o risking blocking on SSL
connections, and there's no way to ask the driver if the connection is
SSL or request that it only check for notifications if it can do so
safely without blocking indefinitely. Needs a new notification checking
method that, if it cannot guarantee that it's using a non-ssl
connection, sends a minimum client/server round trip and reads the response.


- Notifications, warnings, notices, etc can flood the receive buffer,
leading to deadlock if progress requires that more data be sent before a
read is attempted. This has been widely discussed, and the general
consensus appears to be that a worker thread for the driver would be
required in order to provide a general and reliable solution.



--
Craig Ringer

Re: JDBC gripe list

From
Craig Ringer
Date:
On 03/27/2011 11:14 PM, Dave Cramer wrote:
> Adding to the list is statement timeout. I seem to recall the issue is
> we do not want to spawn a thread for each statement which seems to be
> the only way to do this. Alternatively we could use timers, but I
> presume they just spawn threads as well.

Timers can use one shared thread for all timers, either a generic timer
thread provided by the JVM or (IMO better in this case) one dedicated to
that timer pool. I think it'd be an ideal way to do it personally - the
JDBC driver has several issues that'd be solvable by adding a single
thread to use for various timers, etc.

If there are concerns about the driver spawning a thread, it wouldn't be
too tricky to make timer-requiring features conditional on a connection
param, so if no connections that were going to use timer-based features
were made, no thread would be spawned. Personally I don't think a single
shared thread is worth worrying about, though. Have you *seen* the
thread lists in a modern Java app? Threads are extremely low cost when
idle, and are already heavily used throughout Java and the JVM.

--
Craig Ringer

Re: JDBC gripe list

From
Vitalii Tymchyshyn
Date:
29.03.11 05:18, Craig Ringer написав(ла):
> On 03/27/2011 11:14 PM, Dave Cramer wrote:
>> Adding to the list is statement timeout. I seem to recall the issue is
>> we do not want to spawn a thread for each statement which seems to be
>> the only way to do this. Alternatively we could use timers, but I
>> presume they just spawn threads as well.
>
> Timers can use one shared thread for all timers, either a generic
> timer thread provided by the JVM or (IMO better in this case) one
> dedicated to that timer pool. I think it'd be an ideal way to do it
> personally - the JDBC driver has several issues that'd be solvable by
> adding a single thread to use for various timers, etc.
>
> If there are concerns about the driver spawning a thread, it wouldn't
> be too tricky to make timer-requiring features conditional on a
> connection param, so if no connections that were going to use
> timer-based features were made, no thread would be spawned. Personally
> I don't think a single shared thread is worth worrying about, though.
> Have you *seen* the thread lists in a modern Java app? Threads are
> extremely low cost when idle, and are already heavily used throughout
> Java and the JVM.
Please note that you will get into problems with classloaders and
different containers. E.g. running a thread from classloader will
prevent this classloader to be GCd. Tomcat handles this (with a lot of
warnings), but it is not recommended practice. See
http://www.theserverside.com/discussions/thread.tss?thread_id=44353 or
http://stackoverflow.com/questions/533783/why-spawning-threads-in-j2ee-container-is-discouraged

Best regards, Vitalii Tymchyshyn

Re: JDBC gripe list

From
Craig Ringer
Date:
On 29/03/2011 4:36 PM, Vitalii Tymchyshyn wrote:

> Please note that you will get into problems with classloaders and
> different containers. E.g. running a thread from classloader will
> prevent this classloader to be GCd. Tomcat handles this (with a lot of
> warnings), but it is not recommended practice. See
> http://www.theserverside.com/discussions/thread.tss?thread_id=44353 or
> http://stackoverflow.com/questions/533783/why-spawning-threads-in-j2ee-container-is-discouraged

Good point. When running under a container, it'd be best to get a thread
pool from the container via JNDI or using resource injection. that
should be fairly easily handled using a connection param.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

Re: JDBC gripe list

From
Vitalii Tymchyshyn
Date:
29.03.11 05:18, Craig Ringer написав(ла):
> On 03/27/2011 11:14 PM, Dave Cramer wrote:
>
> Timers can use one shared thread for all timers, either a generic
> timer thread provided by the JVM or (IMO better in this case) one
> dedicated to that timer pool. I think it'd be an ideal way to do it
> personally - the JDBC driver has several issues that'd be solvable by
> adding a single thread to use for various timers, etc.
>
> If there are concerns about the driver spawning a thread, it wouldn't
> be too tricky to make timer-requiring features conditional on a
> connection param, so if no connections that were going to use
> timer-based features were made, no thread would be spawned. Personally
> I don't think a single shared thread is worth worrying about, though.
> Have you *seen* the thread lists in a modern Java app? Threads are
> extremely low cost when idle, and are already heavily used throughout
> Java and the JVM.
BTW: May be a wrapper over Socket can be created that will allow
"hand-made" timers with socket channel selectors? In this case no new
thread will be required.

Best regards, Vitalii Tymchyshyn


Re: JDBC gripe list

From
"Kevin Grittner"
Date:
Vitalii Tymchyshyn <tivv00@gmail.com> wrote:

> Please note that you will get into problems with classloaders and
> different containers. E.g. running a thread from classloader will
> prevent this classloader to be GCd.

Would this be a problem if closing the last open connection in the
driver instance caused a call to Timer.cancel()?

-Kevin

Re: JDBC gripe list

From
Vitalii Tymchyshyn
Date:
29.03.11 17:11, Kevin Grittner написав(ла):
> Vitalii Tymchyshyn<tivv00@gmail.com>  wrote:
>
>> Please note that you will get into problems with classloaders and
>> different containers. E.g. running a thread from classloader will
>> prevent this classloader to be GCd.
>
> Would this be a problem if closing the last open connection in the
> driver instance caused a call to Timer.cancel()?
I think this may help. There are a lot of tricks and confusion with
classloaders and I am not an expert in it.
Some more things to check are:
1) Thread creation may be prohibited by SecurityManager. I'd expect J2EE
containers prohibit such a thing since EJBs are prohibited to create
it's own threads.
2) Postgresql driver may be located in "global" classloader, but used
from "local" one. I am not sure, which classloader will new thread
receive. If it will be "local" one, this will mean global driver will
hold reference to classloader (application) from which it were used for
the first time. "Clever" container may kill such a thread when
application is shut down.

Best regards, Vitalii Tymchyshyn


Re: JDBC gripe list

From
"MauMau"
Date:
Hello,


----- Original Message -----
From: "Dave Cramer" <pg@fastcrypt.com>
> So in general I'm having trouble understanding things where the
> implementation says we can do whatever we want you have a requirement
> ? For example getGeneratedKeys from a batch statement? What is it you
> are looking for ? AFAICT we are implementing the spec here. The fact
> that mysql does something else is irrelevant. Please note this is just
> a talking point since I do believe it is possible for us to return the
> generated keys here.

I think the most common use case is, or possibly the only real use case is,
to get auto-generated primary keys when you insert multiple rows at once
into a table which has a sequence. An example is shown in "14.1.4
PreparedStatement Objects" of JDBC 4.0 specification. The following code is
a slightly modified version of that example to use auto-generated keys.

CODE EXAMPLE 14-2 Creating and executing a batch of prepared statements
(slightly modified)
--------------------------------------------------
String[] cols = {"emp_id"};

// turn off autocommit
con.setAutoCommit(false);

PreparedStatement stmt = con.prepareStatement(
"INSERT INTO employees VALUES (?, ?)", cols);

stmt.setInt(1, 2000);
stmt.setString(2, "Kelly Kaufmann");
stmt.addBatch();

stmt.setInt(1, 3000);
stmt.setString(2, "Bill Barnes");
stmt.addBatch();

// submit the batch for execution
int[] updateCounts = stmt.executeBatch();
--------------------------------------------------


Though the table definition is not shown in the spec, assume the one below:

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    salary INT,
    name VARCHAR(20)
);

Then, pstmt.getGeneratedKeys() is expected to return a ResultSet that
contains two rows which consist only of emp_id column. The order of those
two rows should correspond to the order of employee records added in the
batch with addBatch().

My opinion is that the users should explicitly specify the columns they want
to be returned. I don't find it useful to specify
Statement.RETURN_GENERATED_KEYS, because the JDBC spec says that JDBC
drivers decide which columns are returned (i.e. implementation defined
behavior). Please keep this in mind when reading the next.

So which columns should be returned when you pass
Statement.RETURN_GENERATED_KEYS instead of "cols" to
Connection.prepareStatement() in the above example code? What if the table
does not have an auto-generated primary key?

I think it would be reasonable to return "ctid" system column when the user
specifies Statement.RETURN_GENERATED_KEYS, because he/she does not show any
intention about what he/she wants. For example, Oracle returns ROWID in this
case, as described as follows in the manual:

--------------------------------------------------
The getGeneratedKeys() method enables you to retrieve the auto-generated key
fields. The auto-generated keys are returned as a ResultSet object.
If key columns are not explicitly indicated, then Oracle JDBC drivers cannot
identify which columns need to be retrieved. When a column name or column
index array is used, Oracle JDBC drivers can identify which columns contain
auto-generated keys that you want to retrieve. However, when the
Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers
cannot identify these columns. When the integer flag is used to indicate
that auto-generated keys are to be returned, the ROWID pseudo column is
returned as key. The ROWID can be then fetched from the ResultSet object and
can be used to retrieved other columns.
--------------------------------------------------

What do you think?

Regards
MauMau



Re: JDBC gripe list

From
"Kevin Grittner"
Date:
Vitalii Tymchyshyn <tivv00@gmail.com> wrote:

> 1) Thread creation may be prohibited by SecurityManager. I'd
> expect J2EE containers prohibit such a thing since EJBs are
> prohibited to create it's own threads.

I've never used EJBs, as the description of the technology sounded
awful to me up front.  If they prohibit this, it would be a problem.
Can the security policy be adjusted to allow this specific
exception without too much pain?

> 2) Postgresql driver may be located in "global" classloader, but
> used from "local" one. I am not sure, which classloader will new
> thread receive. If it will be "local" one, this will mean global
> driver will hold reference to classloader (application) from which
> it were used for the first time.

I don't think we would have a problem here if we cancel the Timer
when the last connection closes.  My recollection from working on
our own framework is that an object is blocked from garbage
collection as long as there is a chain of references to it from an
active thread where all references in that chain are stronger than a
WeakReference.  A Timer's thread obviously needs strong references
both its own classloader and all objects queued for execution.
Since Timer.cancel() gracefully stops its worker thread, it would no
longer prevent cleanup.

-Kevin

Re: JDBC gripe list

From
Quartz
Date:
addBatch()/executeBatch() is broken under autocommit=true.

Every statement is clearly supposed to be independant.
Example: 5 insert statements, let's say the 2th and the 4th are on duplicate of primary key. All 3 others should still
beperformed but they aren't. 

This breaks our application. We migrated from mysql, and BOOM...


Re: JDBC gripe list

From
Dave Cramer
Date:
On Tue, Mar 29, 2011 at 11:21 AM, MauMau <maumau307@gmail.com> wrote:
> Hello,
>
>
> ----- Original Message ----- From: "Dave Cramer" <pg@fastcrypt.com>
>>
>> So in general I'm having trouble understanding things where the
>> implementation says we can do whatever we want you have a requirement
>> ? For example getGeneratedKeys from a batch statement? What is it you
>> are looking for ? AFAICT we are implementing the spec here. The fact
>> that mysql does something else is irrelevant. Please note this is just
>> a talking point since I do believe it is possible for us to return the
>> generated keys here.
>
> I think the most common use case is, or possibly the only real use case is,
> to get auto-generated primary keys when you insert multiple rows at once
> into a table which has a sequence. An example is shown in "14.1.4
> PreparedStatement Objects" of JDBC 4.0 specification. The following code is
> a slightly modified version of that example to use auto-generated keys.
>
> CODE EXAMPLE 14-2 Creating and executing a batch of prepared statements
> (slightly modified)
> --------------------------------------------------
> String[] cols = {"emp_id"};
>
> // turn off autocommit
> con.setAutoCommit(false);
>
> PreparedStatement stmt = con.prepareStatement(
> "INSERT INTO employees VALUES (?, ?)", cols);
>
> stmt.setInt(1, 2000);
> stmt.setString(2, "Kelly Kaufmann");
> stmt.addBatch();
>
> stmt.setInt(1, 3000);
> stmt.setString(2, "Bill Barnes");
> stmt.addBatch();
>
> // submit the batch for execution
> int[] updateCounts = stmt.executeBatch();
> --------------------------------------------------
>
>
> Though the table definition is not shown in the spec, assume the one below:
>
> CREATE TABLE employees (
>   emp_id SERIAL PRIMARY KEY,
>   salary INT,
>   name VARCHAR(20)
> );
>
> Then, pstmt.getGeneratedKeys() is expected to return a ResultSet that
> contains two rows which consist only of emp_id column. The order of those
> two rows should correspond to the order of employee records added in the
> batch with addBatch().
>
> My opinion is that the users should explicitly specify the columns they want
> to be returned. I don't find it useful to specify
> Statement.RETURN_GENERATED_KEYS, because the JDBC spec says that JDBC
> drivers decide which columns are returned (i.e. implementation defined
> behavior). Please keep this in mind when reading the next.
>
> So which columns should be returned when you pass
> Statement.RETURN_GENERATED_KEYS instead of "cols" to
> Connection.prepareStatement() in the above example code? What if the table
> does not have an auto-generated primary key?
>
> I think it would be reasonable to return "ctid" system column when the user
> specifies Statement.RETURN_GENERATED_KEYS, because he/she does not show any
> intention about what he/she wants. For example, Oracle returns ROWID in this
> case, as described as follows in the manual:
>
> --------------------------------------------------
> The getGeneratedKeys() method enables you to retrieve the auto-generated key
> fields. The auto-generated keys are returned as a ResultSet object.
> If key columns are not explicitly indicated, then Oracle JDBC drivers cannot
> identify which columns need to be retrieved. When a column name or column
> index array is used, Oracle JDBC drivers can identify which columns contain
> auto-generated keys that you want to retrieve. However, when the
> Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers
> cannot identify these columns. When the integer flag is used to indicate
> that auto-generated keys are to be returned, the ROWID pseudo column is
> returned as key. The ROWID can be then fetched from the ResultSet object and
> can be used to retrieved other columns.
> --------------------------------------------------
>
> What do you think?
>
> Regards
> MauMau
>

Well we already have a workable solution to this, we simply return all
the columns in this case

Dave

Re: JDBC gripe list

From
Quartz
Date:
And I would add, the batch is considered a single transaction even though autocommit is true, which causes deadlock
whenyou set a batch of updates in an order that will cause a deadlock. 

Example: make a batch of updates for pk ID=1 to 10 while you make a 2nd batch from id=10 to 1. To increase the chance
ofsuccess with a deadlock, you can toss in a pg_sleep() call to slow things down. 

You will see a deadlock even though you never intended to use a lock.
As you might guess, this break our application ported from mysql...

The workaround is very bad: either sorting updates batch by PK which is costly, and sometimes not possible unless you
totallyknow the semantic of the sql you are attempting, or interlacing a hack "COMMIT" as a sql statement in the batch,
whichis not legal since there was no "BEGIN" (but triggers something in postgres). 



--- On Tue, 3/29/11, Quartz <quartz12h@yahoo.com> wrote:

> From: Quartz <quartz12h@yahoo.com>
> Subject: Re: [JDBC] JDBC gripe list
> To: pgsql-jdbc@postgresql.org
> Received: Tuesday, March 29, 2011, 3:29 PM
> addBatch()/executeBatch() is broken
> under autocommit=true.
>
> Every statement is clearly supposed to be independant.
> Example: 5 insert statements, let's say the 2th and the 4th
> are on duplicate of primary key. All 3 others should still
> be performed but they aren't.
>
> This breaks our application. We migrated from mysql, and
> BOOM...
>
>

Re: JDBC gripe list

From
Dave Cramer
Date:
On Tue, Mar 29, 2011 at 3:29 PM, Quartz <quartz12h@yahoo.com> wrote:
> addBatch()/executeBatch() is broken under autocommit=true.
>
> Every statement is clearly supposed to be independant.
> Example: 5 insert statements, let's say the 2th and the 4th are on duplicate of primary key. All 3 others should
stillbe performed but they aren't. 
>
> This breaks our application. We migrated from mysql, and BOOM...
>

I would think the concept of execute batch would infer that they
should all commit or none should. This line from the API seems to
infer that

"Submits a batch of commands to the database for execution and if all
commands execute successfully, returns an array of update counts."

Where it states "if all commands execute successfully" implies a
transaction in the postgresql world.

Dave

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

Re: JDBC gripe list

From
Quartz
Date:
Keep reading:
http://download.oracle.com/javase/6/docs/api/java/sql/Statement.html#executeBatch%28%29

"If one of the commands in a batch update fails to execute properly, this method throws a BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch. However, the driver's behavior must be consistent with a particular DBMS, either always continuing to process commands or never continuing to process commands. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will contain as many elements as there are commands in the batch, and at least one of the elements will be the following[...]"

"
The possible implementations and return values have been modified in the Java 2 SDK, Standard Edition, version 1.3 to accommodate the option of continuing to proccess commands in a batch update after a BatchUpdateException obejct has been thrown. "

For those who believe the driver is fine when breaking the batch after the 1st error, then in my example of 5 inserts with pk id=1 to 5 (with id 2 and 4 already present) the table won't contain at least the id=1 (which didn't fail).

The postgres driver is partly at fault, but the server is too, for making a transaction whene there is none. Try to get any of the 2 sides to work on it and they just throw the problem the other way:
"[server guys]: oh its a driver issue then!..."
"[driver guys]: oh, its a server issue then!..."
Nothing gets done, and we are stuck.



--- On Tue, 3/29/11, Dave Cramer <pg@fastcrypt.com> wrote:

> From: Dave Cramer <pg@fastcrypt.com>
> Subject: Re: [JDBC] JDBC gripe list
> To: "Quartz" <quartz12h@yahoo.com>
> Cc: pgsql-jdbc@postgresql.org
> Received: Tuesday, March 29, 2011, 4:17 PM
> On Tue, Mar 29, 2011 at 3:29 PM,
> Quartz <quartz12h@yahoo.com>
> wrote:
> > addBatch()/executeBatch() is broken under
> autocommit=true.
> >
> > Every statement is clearly supposed to be
> independant.
> > Example: 5 insert statements, let's say the 2th and
> the 4th are on duplicate of primary key. All 3 others should
> still be performed but they aren't.
> >
> > This breaks our application. We migrated from mysql,
> and BOOM...
> >
>
> I would think the concept of execute batch would infer that
> they
> should all commit or none should. This line from the API
> seems to
> infer that
>
> "Submits a batch of commands to the database for execution
> and if all
> commands execute successfully, returns an array of update
> counts."
>
> Where it states "if all commands execute successfully"
> implies a
> transaction in the postgresql world.
>
> Dave
>
> >
> > --
> > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-jdbc
> >
>

Re: JDBC gripe list

From
"Kevin Grittner"
Date:
Quartz <quartz12h@yahoo.com> wrote:

> "... the driver's behavior must be consistent with a particular
> DBMS, either always continuing to process commands or never
> continuing to > process commands.,,"

> "The possible implementations and return values have been modified
> in the Java 2 SDK, Standard Edition, version 1.3 to accommodate
> the option of continuing to proccess commands in a batch update
> after a BatchUpdateException obejct has been thrown. "

Unfortunately, the combination of these two requirements with the
fact that the PostgreSQL driver supports JDK 1.2 or higher means
that this option must not be allowed.  We'd have to require a Java
version from this century to support that.  Maybe it's time to think
about that, but we would need to commit to that before we could move
to the behavior you want.

-Kevin

Re: JDBC gripe list

From
John R Pierce
Date:
On 03/29/11 1:49 PM, Kevin Grittner wrote:
> Unfortunately, the combination of these two requirements with the
> fact that the PostgreSQL driver supports JDK 1.2 or higher means
> that this option must not be allowed.  We'd have to require a Java
> version from this century to support that.  Maybe it's time to think
> about that, but we would need to commit to that before we could move
> to the behavior you want.

indeed, I personally think there's ZERO point in supporting anything
older than JDK 1.4 going forward. In the java world, 1.2 is akin to PG
continuing to support 7.2





Re: JDBC gripe list

From
Dave Cramer
Date:


For those who believe the driver is fine when breaking the batch after the 1st error, then in my example of 5 inserts with pk id=1 to 5 (with id 2 and 4 already present) the table won't contain at least the id=1 (which didn't fail).

The postgres driver is partly at fault, but the server is too, for making a transaction whene there is none. Try to get any of the 2 sides to work on it and they just throw the problem the other way:
"[server guys]: oh its a driver issue then!..."
"[driver guys]: oh, its a server issue then!..."
Nothing gets done, and we are stuck.


Well in the postgresql world every statement is a transaction. That being said  the concept of batch processing in postgres is that it would be done in a transaction otherwise what is the point ? If you agree with that then in the postgres world it would be natural for all of it to fail. At least thats how I would expect postgres to act.

Dave


Re: JDBC gripe list

From
"Kevin Grittner"
Date:
John R Pierce <pierce@hogranch.com> wrote:

> In the java world, 1.2 is akin to PG continuing to support 7.2

PostgreSQL 7.2 was released 2002-02-04.

Java 1.2 was released 1998-12-08, which puts it before PG 6.4.1.

The feature being requested became available in Java in 2000-05-08,
the very same day that PG 7.0 was released.

-Kevin

Re: JDBC gripe list

From
"Kevin Grittner"
Date:
Dave Cramer <pg@fastcrypt.com> wrote:

> it would be done in a transaction otherwise what is the point ?

That's a fair question.  A batch will almost always run faster if
done as a single transaction which either runs in its entirety or
fails in its entirety.  What is the reason for wanting to use batch
processing if not for speed?  I think we need to see a reasonable
use case from someone advocating this change in order to justify it.
(Note: "It works in MySQL" won't cut it.)

-Kevin

Re: JDBC gripe list

From
Dave Cramer
Date:
On Tue, Mar 29, 2011 at 5:32 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Dave Cramer <pg@fastcrypt.com> wrote:
>
>> it would be done in a transaction otherwise what is the point ?
>
> That's a fair question.  A batch will almost always run faster if
> done as a single transaction which either runs in its entirety or
> fails in its entirety.  What is the reason for wanting to use batch
> processing if not for speed?  I think we need to see a reasonable
> use case from someone advocating this change in order to justify it.
> (Note: "It works in MySQL" won't cut it.)
>

And I think this brings up an important point.
In general the driver does what makes sense to do in postgres where
given the flexibility in the spec.
Sometimes this is not what the rest of the world does.


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

Re: JDBC gripe list

From
"Donald Fraser"
Date:
I thought I would add my comments regarding Timer and TimerTask.
The following comments are taken from the Java docs for Timer.

<JavaDoc snipet...>
Corresponding to each <tt>Timer</tt> object is a single background
thread that is used to execute all of the timer's tasks, sequentially.
Timer tasks should complete quickly. If a timer task takes excessive time
to complete, it "hogs" the timer's task execution thread. This can, in
turn, delay the execution of subsequent tasks, which may "bunch up" and
execute in rapid succession when (and if) the offending task finally
completes.
</JavaDoc snipet...>

What this means is that if your TimerTask execution time is unknown or not
quantifiable then you cannot reliably use a TimerTask without creating a
worker thread to do the work, otherwise you break the reliability of the
time between events. Effectively you need a second thread to do the work!
In short you are better to create your own single thread to manage such
events.

I already have a lot of experience with a modified PosgreSQL 7.4 driver and
notification threads. Our company has been using it since 2005.

The most reliable approach we came up with is:

1) Modify the driver such that you can synchronize separately on
PGStream.pg_input
and PGStream.pg_output.
We did this such that to synchronize on pg_input you should first
synchronize on pg_output. This is a programming procedure that cannot be
enforced by the language or compilers. This procedure ensures no synchronize
deadlocks. The only code that does not adhere to this procedure is the
notification thread.

2) Create a single notification thread.
Basic processing logic is as follows;
    i) Start process loop.
    ii) Synchronize on PGStream.pg_input.
    iii) stream.Mark(1).
    iv) stream.ReceiveChar().
    v) is Character notification? Yes - get notification data, No - reset
        stream.
    vi) exit synchronize on PGStream.pg_input.
    vii) If have notification data - do fire notification event, else wait x
        time.
    viii) restart process loop.

If you would like me to modify the current driver to show how this works I
am willing to do so.
However it still does not solve the thread instantiation problem.

Regards
Donald

----- Original Message -----
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Vitalii Tymchyshyn" <tivv00@gmail.com>
Cc: "Dave Cramer" <pg@fastcrypt.com>; "List" <pgsql-jdbc@postgresql.org>;
"Craig Ringer" <craig@postnewspapers.com.au>
Sent: Tuesday, March 29, 2011 4:55 PM
Subject: Re: [JDBC] JDBC gripe list


> Vitalii Tymchyshyn <tivv00@gmail.com> wrote:
>
>> 1) Thread creation may be prohibited by SecurityManager. I'd
>> expect J2EE containers prohibit such a thing since EJBs are
>> prohibited to create it's own threads.
>
> I've never used EJBs, as the description of the technology sounded
> awful to me up front.  If they prohibit this, it would be a problem.
> Can the security policy be adjusted to allow this specific
> exception without too much pain?
>
>> 2) Postgresql driver may be located in "global" classloader, but
>> used from "local" one. I am not sure, which classloader will new
>> thread receive. If it will be "local" one, this will mean global
>> driver will hold reference to classloader (application) from which
>> it were used for the first time.
>
> I don't think we would have a problem here if we cancel the Timer
> when the last connection closes.  My recollection from working on
> our own framework is that an object is blocked from garbage
> collection as long as there is a chain of references to it from an
> active thread where all references in that chain are stronger than a
> WeakReference.  A Timer's thread obviously needs strong references
> both its own classloader and all objects queued for execution.
> Since Timer.cancel() gracefully stops its worker thread, it would no
> longer prevent cleanup.
>


Re: JDBC gripe list

From
Oliver Jowett
Date:
On 30 March 2011 09:34, Quartz <quartz12h@yahoo.com> wrote:
Keep reading:
http://download.oracle.com/javase/6/docs/api/java/sql/Statement.html#executeBatch%28%29

Keep reading:


"The commit occurs when the statement completes. The time when the statement completes depends on the type of SQL Statement: [...] For CallableStatement objects or for statements that return multiple results, the statement is complete when all of the associated result sets have been closed, and all update counts and output parameters have been retrieved."

Also, the spec says you should always turn off autocommit before batch updates, because the commit behavior in this case is implementation-defined.

Both of these points were raised the last time we discussed this on the list, but I didn't see a reply from you addressing them: http://archives.postgresql.org/pgsql-jdbc/2011-01/msg00055.php. Could you respond to the points in that post if you disagree?

Otherwise, I don't think it's very useful to rehash old issues as if they hadn't been discussed before.

Oliver

Re: JDBC gripe list

From
"Kevin Grittner"
Date:
"Donald Fraser" <postgres@kiwi-fraser.net> wrote:

> I already have a lot of experience with a modified PosgreSQL 7.4
> driver and notification threads. Our company has been using it
> since 2005.

Are you talking about modifying the driver to generate and deliver
events when there is a notification (as in PostgreSQL LISTEN/NOTIFY)
on a connection?  If so, that's an interesting feature and it would
be interesting to see a patch.

It's not what we've been discussing on this thread, though,
regarding Timer and threads.  We're talking about implementing this:

http://download.oracle.com/javase/6/docs/api/java/sql/Statement.html#setQueryTimeout%28int%29

Have you implemented something for that?  I don't think your
concerns about timer events bunching up really applies a whole lot
to this -- these would be one-shot timers; we're not talking about
using an interval.

-Kevin

Re: JDBC gripe list

From
Kris Jurka
Date:

On Tue, 29 Mar 2011, Kevin Grittner wrote:

> Unfortunately, the combination of these two requirements with the
> fact that the PostgreSQL driver supports JDK 1.2 or higher means
> that this option must not be allowed.

Support for JDK1.2 and 1.3 was dropped starting with the 8.4 release.

http://jdbc.postgresql.org/download.html#supported

Kris Jurka

Re: JDBC gripe list

From
Kris Jurka
Date:

On Fri, 25 Mar 2011, Dave Cramer wrote:

> There are 3 committers on the JDBC project. Currently Kris has been
> shouldering the load, but both Oliver and I are still here.
>

Having commit privileges and actually committing aren't exactly the same
thing.  Your last commit was over three years ago.  Oliver's was more than
five.  The mailing list discussions that you contribute to are certainly
valuable, but let's not pretend this is a thriving development community
here...

Kris Jurka


Re: JDBC gripe list

From
Craig Ringer
Date:
On 03/29/2011 11:55 PM, Kevin Grittner wrote:
> Vitalii Tymchyshyn<tivv00@gmail.com>  wrote:

 >  If they prohibit this, it would be a problem.
 > Can the security policy be adjusted to allow this specific
 > exception without too much pain?

It's probably not a good idea to directly create threads from within a
container anyway.

"Ideally" (from the container app purist and container designer's point
of view, not reality) you ask the container for a thread from a managed
pool, either by requesting one from JNDI or through @Resource injection.
Of course, the pre-defined pools are vendor-specific (argh!) so an
additional connection param specifying the jndi name of the connection
pool to use would probably be necessary.

>> 1) Thread creation may be prohibited by SecurityManager. I'd
>> expect J2EE containers prohibit such a thing since EJBs are
>> prohibited to create it's own threads.
>
> I've never used EJBs, as the description of the technology sounded
> awful to me up front.

EJBs prior to EJB3.1 were indeed awful.

With EJB3.1 and CDI/Weld, EJBs and containerized applications are
starting to become quite nice. Unfortunately all the dependency
injection stuff is still excitingly buggy and the container
configuration mechanisms are both vendor specific and rather stupid,
limiting the utility of the whole thing a bit.

Give it another five to ten years at Java's almost C++-esque rate of
development these days and it'll be really nice to use :S

( Stupid configuration mechanism example: JAAS separates the concept of
"role" from "user" and "group". Roles are application-defined and are
mapped onto container-defined local users and groups. Great idea, except
that the role-to-user/group mapping is defined in a vendor-specific xml
deployment descriptor THAT IS BUNDLED INSIDE THE APPLICATION! The app
has no way of knowing about the users and groups of any given deployment
site, so the whole role/user/group separation is rendered useless in one
incredible master stroke of pain. At least in Glassfish there's no way
to override the bundled descriptor (glassfish-web.xml) during deployment
or edit the role mappings after deployment. )

--
Craig Ringer

Re: JDBC gripe list

From
Oliver Jowett
Date:
On 30 March 2011 12:43, Kris Jurka <books@ejurka.com> wrote:
>
>
> On Tue, 29 Mar 2011, Kevin Grittner wrote:
>
>> Unfortunately, the combination of these two requirements with the
>> fact that the PostgreSQL driver supports JDK 1.2 or higher means
>> that this option must not be allowed.
>
> Support for JDK1.2 and 1.3 was dropped starting with the 8.4 release.
>
> http://jdbc.postgresql.org/download.html#supported

What do you think about dropping support for 1.4 and requiring 1.5?

 * the issues with generics in JDBC interfaces would hopefully go away
 * many of the backwards compatibility issues with older JDBC versions
(e.g. BIT vs BOOLEAN) go away
 * we can use generics in the driver implementation
 * we can use java.util.concurrent
 * many 3rd party libraries become useful (e.g. Netty) - there aren't
many things that support 1.4 left

I have a hard time dealing with the driver code these days because
first, I have to forget half of the language tools I usually use ..

Oliver

Re: JDBC gripe list

From
Dave Cramer
Date:
On Tue, Mar 29, 2011 at 9:09 PM, Oliver Jowett <oliver@opencloud.com> wrote:
> On 30 March 2011 12:43, Kris Jurka <books@ejurka.com> wrote:
>>
>>
>> On Tue, 29 Mar 2011, Kevin Grittner wrote:
>>
>>> Unfortunately, the combination of these two requirements with the
>>> fact that the PostgreSQL driver supports JDK 1.2 or higher means
>>> that this option must not be allowed.
>>
>> Support for JDK1.2 and 1.3 was dropped starting with the 8.4 release.
>>
>> http://jdbc.postgresql.org/download.html#supported
>
> What do you think about dropping support for 1.4 and requiring 1.5?
>
>  * the issues with generics in JDBC interfaces would hopefully go away
>  * many of the backwards compatibility issues with older JDBC versions
> (e.g. BIT vs BOOLEAN) go away
>  * we can use generics in the driver implementation
>  * we can use java.util.concurrent
>  * many 3rd party libraries become useful (e.g. Netty) - there aren't
> many things that support 1.4 left
>
> I have a hard time dealing with the driver code these days because
> first, I have to forget half of the language tools I usually use ..
>
> Oliver
>

Is there any way to get statistics on downloads of jars ? Might be
useful to see how many times those drivers get downloaded ?


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

Re: JDBC gripe list

From
Kris Jurka
Date:

On Tue, 29 Mar 2011, Dave Cramer wrote:

> Is there any way to get statistics on downloads of jars ? Might be
> useful to see how many times those drivers get downloaded ?
>

I only have FTP access to the website, so I don't have any access to the
logs.  I have no problem dropping support for 1.4, it's been dead for long
enough.  This relatively recent survey may be informative:

http://www.theserverside.com/discussions/thread.tss?thread_id=61645

Question four: At work, what JVM do you target compilation for?

Java 6 won again, with 247 responses (58%); Java 1.4 got 33 (8%), Java 5
got 146 (34%). One person said they used Retroweaver for clients with 1.4.

Kris Jurka

Re: JDBC gripe list

From
Simon Riggs
Date:
On Tue, Mar 29, 2011 at 1:05 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 03/25/2011 04:07 PM, Simon Riggs wrote:
>>
>> On Fri, Mar 25, 2011 at 12:35 AM, Dave Cramer<pg@fastcrypt.com>  wrote:
>>>
>>> Just got back from pgeast. Seems everyone seems to have a gripe about
>>> the JDBC driver.
>>>
>>> My sense is that for basic read/write/update/delete the driver works
>>> just fine. However I'd like to compile a list of everyone's gripe.
>>
>> Funny that, I just got back from a different conference where people
>> were griping about it also.
>>
>> Didn't get a detailed list but numbers of less impressed people all
>> active Postgres users and supporters.
>
> Personally, I think the JDBC driver is fine. It's not perfect, but I hardly
> feel justified "demanding" a perfect driver when I haven't contributed
> anything to its creation. People appear to be very happy to complain, but
> rather less willing to knuckle down and help!

I was passing on feedback from users, not my own thoughts.

We should at least give people the benefit of the doubt before we
condemn their willingess to contribute. I remain hopeful there are
people out there with a genuine desire to assist.

The first step is to come up with a reasonable list of tasks that need work.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: JDBC gripe list

From
"Donald Fraser"
Date:
----- Original Message -----
Sent: Wednesday, March 30, 2011 12:22 AM
Subject: Re: [JDBC] JDBC gripe list


> "Donald Fraser" wrote:
>
>> I already have a lot of experience with a modified PosgreSQL 7.4
>> driver and notification threads. Our company has been using it
>> since 2005.
>
> Are you talking about modifying the driver to generate and deliver
> events when there is a notification (as in PostgreSQL LISTEN/NOTIFY)
> on a connection?  If so, that's an interesting feature and it would
> be interesting to see a patch.

Yes. I will work on producing a patch for review.

> It's not what we've been discussing on this thread, though,
> regarding Timer and threads.  We're talking about implementing this:
>
> http://download.oracle.com/javase/6/docs/api/java/sql/Statement.html#setQueryTimeout%28int%29
>
> Have you implemented something for that?  I don't think your
> concerns about timer events bunching up really applies a whole lot
> to this -- these would be one-shot timers; we're not talking about
> using an interval.

Agreed, one-shot timers would not be a problem.

Apologies, some how (probably the time of night I was reading the thread)
and all the different subjects within this topic, I miss-read the thread
with regards to Timer.
We have implemented timeouts, however it was done outside the driver and
heavily intergrated with other code and uses 1.5's concurrent thread API.

Regards
Donald


Re: JDBC gripe list

From
Quartz
Date:
> > it would be done in a transaction otherwise what is
> the point ?
>
> That's a fair question.  A batch will almost always
> run faster if
> done as a single transaction which either runs in its
> entirety or
> fails in its entirety.  What is the reason for wanting
> to use batch
> processing if not for speed?  I think we need to see a
> reasonable
> use case from someone advocating this change in order to
> justify it.
> (Note: "It works in MySQL" won't cut it.)
>
> -Kevin
>


I wrote earlier that the batch is independant form the will to make it a transaction for a few reasons:

a) you can't control the row locks involved in the batch, and you could end up in a deadlock. This is frequent when you
justintended to pile up statements for another thread to call executebatch. 

b) you just want to run a playlist of independant SQLs not related to each other and you don't expect some failures to
preventthe others from running. 

The batch works independantly from a transaction. I can't stress this differently. Maybe you have never used it this
way,or learned it with a biased perspective that batches is only for transaction. I hope I gave a fresh perspective to
makea better driver. 


Re: JDBC gripe list

From
Basil Bourque
Date:
Another gripe: "InValid" method

"IsValid" method in JDBC spec is not functional (simply throws an exception).
http://download.oracle.com/javase/6/docs/api/java/sql/Connection.html#isValid(int)

This means users have to write their own routine to test if the connection is still successfully running. Example, run
aquery like "SELECT 1 = 1;" where you ignore the results -- you just look to see if errors occurred. 

Spec:
-----
Returns true if the connection has not been closed and is still valid. The driver shall submit a query on the
connectionor use some other mechanism that positively verifies the connection is still valid when this method is
called.
-----

Current code in "AbstractJdbc4Connection.java" of "postgresql-jdbc-9.0-801.src":
-----
    public boolean isValid(int timeout) throws SQLException
    {
        checkClosed();
        throw org.postgresql.Driver.notImplemented(this.getClass(), "isValid(int)");
    }
-----

--Basil Bourque



Re: JDBC gripe list

From
Oliver Jowett
Date:
On 31 March 2011 08:51, Basil Bourque <basil.list@me.com> wrote:
> Another gripe: "InValid" method
>
> "IsValid" method in JDBC spec is not functional (simply throws an exception).
> http://download.oracle.com/javase/6/docs/api/java/sql/Connection.html#isValid(int)

This would be another JDBC4 thing that hasn't been implemented yet.
Seems simple enough to add.

Oliver

Re: JDBC gripe list

From
Achilleas Mantzios
Date:
Hello,
what i think that it would be nice to have is being able to couple users in the app server
with postgresql users.
Currently most people define an app server (usually super) user to access the database,
and the connections in the pool are created with this db user connecting to the db.
Numerous times I encountered situations where I would like to link entries
from the web/app server access log (where app server usernames are logged)
with entries from the postgresql log, where only the app server db user is logged.
In order to to this corelation, or even worse to *prove* that app server user (lets call him Uapp)
caused or did not cause this erroneous/malicious DB statement, one has to grep for SQL stmts
through the source, then idntify some apps as candidated for having been run,
and then try to find from the web/app server access logs who those people are.
This does not work nice and is not accurate.

What would be ideal is to have the app/web server users get connections from the pool
authenticated against their app server users.
There are ways to do that, I have implemented this in our installation in a test system.
However, the problem is that the pool is per user, the connections are named and each pool
can have e.g. max connections.
This does not scale well, since it requires
max_connections >= MAX_CONNECTIONS_PER_USER_DEFINED_IN_APP_SERVER * MAX_APP_SERVER_CONCURRENT_USERS

From what i played a little bit with SET SESSION AUTHORIZATION, SET ROLE,
those did not seem to affect the actual user (%u) in logging in postgresql logs.
The aim was to have one common pool with "unnamed" connections and assign them to a user
after/on geting the connection.
If that was possible, and if JDBC supported that, then this problem would be easily solved.

I know pgsql JDBC driver is the last part of the puzzle, but i just thought to mention it here
since the context seems right.

Στις Friday 25 March 2011 02:35:48 ο/η Dave Cramer έγραψε:
> Just got back from pgeast. Seems everyone seems to have a gripe about
> the JDBC driver.
>
> My sense is that for basic read/write/update/delete the driver works
> just fine. However I'd like to compile a list of everyone's gripe.
>
> Dave
>



--
Achilleas Mantzios

Re: JDBC gripe list

From
Thomas Kellerer
Date:
Achilleas Mantzios, 31.03.2011 09:29:
> Currently most people define an app server (usually super) user to access the database,
> and the connections in the pool are created with this db user connecting to the db.
> Numerous times I encountered situations where I would like to link entries
> from the web/app server access log (where app server usernames are logged)
> with entries from the postgresql log, where only the app server db user is logged.
> In order to to this corelation, or even worse to *prove* that app server user (lets call him Uapp)
> caused or did not cause this erroneous/malicious DB statement, one has to grep for SQL stmts
> through the source, then idntify some apps as candidated for having been run,
> and then try to find from the web/app server access logs who those people are.
> This does not work nice and is not accurate.
>
If you are on 9.0 and have control over the connection initialization in the pool, then using 9.0's "application_name"
mightbe a solution to this. 

If you can configure the pool to run

SET application_name = 'app_user_name'

when a connection is taken out of the pool, then this name can be part of the log message in the PostgreSQL logfile.

I don't think the standard pools that are available in Java (C3P0 or DBCP) can use a "dynamic" statement like this (as
itrequires injecting a value that changes during time), but maybe there is a way... 

Regards
Thomas

Re: JDBC gripe list

From
Achilleas Mantzios
Date:
Στις Thursday 31 March 2011 10:42:50 ο/η Thomas Kellerer έγραψε:
> Achilleas Mantzios, 31.03.2011 09:29:
> > Currently most people define an app server (usually super) user to access the database,
> > and the connections in the pool are created with this db user connecting to the db.
> > Numerous times I encountered situations where I would like to link entries
> > from the web/app server access log (where app server usernames are logged)
> > with entries from the postgresql log, where only the app server db user is logged.
> > In order to to this corelation, or even worse to *prove* that app server user (lets call him Uapp)
> > caused or did not cause this erroneous/malicious DB statement, one has to grep for SQL stmts
> > through the source, then idntify some apps as candidated for having been run,
> > and then try to find from the web/app server access logs who those people are.
> > This does not work nice and is not accurate.
> >
> If you are on 9.0 and have control over the connection initialization in the pool, then using 9.0's
"application_name"might be a solution to this. 
>
> If you can configure the pool to run
>
> SET application_name = 'app_user_name'
>
> when a connection is taken out of the pool, then this name can be part of the log message in the PostgreSQL logfile.
>

Yes, sure, thanx for sharing this. One could indeed do this by hacking/subclassing the relevant pool classes
in the app server. But that would still be a work around.
I dont know why SET application ='' is reflected in the log files, but SET ROLE is not.
Is it intentional ? Anyways this question should be targeted to the backend guys rather than here.

> I don't think the standard pools that are available in Java (C3P0 or DBCP) can use a "dynamic" statement like this
(asit requires injecting a value that changes during time), but maybe there is a way... 
>
> Regards
> Thomas
>
>



--
Achilleas Mantzios

Re: JDBC gripe list

From
Oliver Jowett
Date:
On 31 March 2011 20:29, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

> From what i played a little bit with SET SESSION AUTHORIZATION, SET ROLE,
> those did not seem to affect the actual user (%u) in logging in postgresql logs.
> The aim was to have one common pool with "unnamed" connections and assign them to a user
> after/on geting the connection.
> If that was possible, and if JDBC supported that, then this problem would be easily solved.

Not sure why %u isn't changing, but if it's mostly logging you are
worried about, have you tried "SET application_name" and %a as an
alternative way of getting the app-level user info into the logs?

Oliver

Re: JDBC gripe list

From
Thomas Kellerer
Date:
Achilleas Mantzios, 31.03.2011 09:58:
>> If you are on 9.0 and have control over the connection
>> initialization in the pool, then using 9.0's "application_name"
>> might be a solution to this.
>>
>> If you can configure the pool to run
>>
>> SET application_name = 'app_user_name'
>>
>> when a connection is taken out of the pool, then this name can be
>> part of the log message in the PostgreSQL logfile.
>>
>
> Yes, sure, thanx for sharing this. One could indeed do this by
> hacking/subclassing the relevant pool classes in the app server. But
> that would still be a work around. I dont know why SET application
> ='' is reflected in the log files, but SET ROLE is not. Is it
> intentional ? Anyways this question should be targeted to the backend
> guys rather than here.

The actual SET application_name is not logged directly, but you can change the log configuration to include the name
thatis set with that statement. 

Regards
Thomas



Re: JDBC gripe list

From
Achilleas Mantzios
Date:
Στις Thursday 31 March 2011 13:06:04 ο/η Thomas Kellerer έγραψε:
> Achilleas Mantzios, 31.03.2011 09:58:
> >> If you are on 9.0 and have control over the connection
> >> initialization in the pool, then using 9.0's "application_name"
> >> might be a solution to this.
> >>
> >> If you can configure the pool to run
> >>
> >> SET application_name = 'app_user_name'
> >>
> >> when a connection is taken out of the pool, then this name can be
> >> part of the log message in the PostgreSQL logfile.
> >>
> >
> > Yes, sure, thanx for sharing this. One could indeed do this by
> > hacking/subclassing the relevant pool classes in the app server. But
> > that would still be a work around. I dont know why SET application
> > ='' is reflected in the log files, but SET ROLE is not. Is it
> > intentional ? Anyways this question should be targeted to the backend
> > guys rather than here.
>
> The actual SET application_name is not logged directly, but you can change the log configuration to include the name
thatis set with that statement. 
>

You mean log_line_prefix parameter. Ok but a
log_line_prefix = '%d %a %u %p %c %m '
while it prints correctly %a (application) (as set by SET application_name),
it does not print correctly %u (user) (as set by SET ROLE).

> Regards
> Thomas
>
>
>
>



--
Achilleas Mantzios

Re: JDBC gripe list

From
"Marc Mamin"
Date:
> > Achilleas Mantzios, 31.03.2011 09:58:
> > >> If you are on 9.0 and have control over the connection
> > >> initialization in the pool, then using 9.0's "application_name"
> > >> might be a solution to this.
> > >>
> > >> If you can configure the pool to run
> > >>
> > >> SET application_name = 'app_user_name'
> > >>
> > >> when a connection is taken out of the pool, then this name can be
> > >> part of the log message in the PostgreSQL logfile.
> > >>
> > >
> > > Yes, sure, thanx for sharing this. One could indeed do this by
> > > hacking/subclassing the relevant pool classes in the app server. But
> > > that would still be a work around. I dont know why SET application
> > > ='' is reflected in the log files, but SET ROLE is not. Is it
> > > intentional ? Anyways this question should be targeted to the backend
> > > guys rather than here.
> > 
> > The actual SET application_name is not logged directly, but you can change the log configuration to include the
namethat is set with that statement.
 
> > 
> 
> You mean log_line_prefix parameter. Ok but a 
> log_line_prefix = '%d %a %u %p %c %m '
> while it prints correctly %a (application) (as set by SET application_name), 
> it does not print correctly %u (user) (as set by SET ROLE).

Hello,

I would not say that %u return an incorrect user as the info stay relevant after changing the connection role, 
but an additional parameter to log the current role would be indeed interesting.

regards,

Marc Mamin

Re: JDBC gripe list

From
Kris Jurka
Date:

On Thu, 31 Mar 2011, Marc Mamin wrote:

>
>>> Achilleas Mantzios, 31.03.2011 09:58:
>> You mean log_line_prefix parameter. Ok but a
>> log_line_prefix = '%d %a %u %p %c %m '
>> while it prints correctly %a (application) (as set by SET application_name),
>> it does not print correctly %u (user) (as set by SET ROLE).
>
> I would not say that %u return an incorrect user as the info stay
> relevant after changing the connection role, but an additional parameter
> to log the current role would be indeed interesting.
>

A patch was worked on for the server for 9.1 to implement this, but it
didn't make it in, perhaps for 9.2...

https://commitfest.postgresql.org/action/patch_view?id=524

Kris Jurka

Re: JDBC gripe list

From
Achilleas Mantzios
Date:
Στις Thursday 31 March 2011 17:47:36 ο/η Kris Jurka έγραψε:
>
> On Thu, 31 Mar 2011, Marc Mamin wrote:
>
> >
> >>> Achilleas Mantzios, 31.03.2011 09:58:
> >> You mean log_line_prefix parameter. Ok but a
> >> log_line_prefix = '%d %a %u %p %c %m '
> >> while it prints correctly %a (application) (as set by SET application_name),
> >> it does not print correctly %u (user) (as set by SET ROLE).
> >
> > I would not say that %u return an incorrect user as the info stay
> > relevant after changing the connection role, but an additional parameter
> > to log the current role would be indeed interesting.
> >
>
> A patch was worked on for the server for 9.1 to implement this, but it
> didn't make it in, perhaps for 9.2...
>
> https://commitfest.postgresql.org/action/patch_view?id=524

Cool, thanks Kris.

>
> Kris Jurka
>



--
Achilleas Mantzios

Re: JDBC gripe list

From
Radosław Smogura
Date:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> Sunday 27 March 2011 18:55:58
> > Dave Cramer  wrote:
> >
> > Adding to the list is statement timeout. I seem to recall the issue
> > is we do not want to spawn a thread for each statement which seems
> > to be the only way to do this. Alternatively we could use timers,
> > but I presume they just spawn threads as well.
>
> No, see the java.util.Timer docs:
>
> http://download.oracle.com/javase/6/docs/api/java/util/Timer.html
>
> Each Timer object uses one thread and can handle thousands of pending
> timers.  If you're using JDBC you're on a JVM, and those all know how
> to multi-thread gracefully.  A daemon thread for a group of timers
> should be no big deal.
>
> We had a patch submitted for this, which looked to me like it was
> pretty close to committable.  I raised some concerns and was
> intending to look at it again, but haven't gotten the requisite round
> tuits yet.
>
> -Kevin

If I good catched thread.

I experimented with cursors, it's dead corner. Actually, if you have fetch
size setted then cursors are used, but PG doesn't support scrollable &
updatable cursors. From other hand about background fetching, You will need to
fetch some good amount of rows, because of, mainly, network latency. Let's
assume your latency is 2ms, and I'm looking for row witch id or something
(number value) is 2, Java construct if (rs.getInt(1) == 2) takes less then 2
ms. Query will take much longer. If you ask for 10 rows, query time is at
least 20ms - too bad.

If you wan't to add positioned updates, then it's same problem you may move
only forward.

The fetch size is for databases that supports, hmm, prefetach, when you may
download next xxx rows without moving cursor. I think PG can do it, but only
in PgSQL.

Regards,
Radek