Re: Out of memory error on huge resultset - Mailing list pgsql-jdbc

>The problem here is that we would then need two completely different
>implementations for jdbc1 and jdbc2/3 since List is not part of
>jdk1.1.  We could build our own List implementation that works on jdk1.1,
>but I am not sure the gain in performance is worth it.  If you could do
>some testing and come back with some numbers of the differences in
>performance between ResultSets implemented with Vectors and Lists that
>would probably give us enough information to guage how to proceed on this
>suggested improvement.

In the past, I have done this sort of thing.

The "synchronized" overhead of a "synchronized method" is about 7 times the
overhead of a regular method call. I did many empirical tests of this on
JDK 1.3 and 1.4 on Linux (2.2 and 2.4) due to the high performance demands
of the software my firm uses. Now, that all depends on how many times you
invoke those methods and how fast they are otherwise. I'm unwilling to do
that for PostgreSQL, but I have to imagine that scrapping JDK 1.1 support
would not be a bad thing and may even be a good thing. Anyone still using
JDK 1.1 is also probably using it in conjunction with other products from
that era, so having a modern product compatible with a very out of date
product makes no sense in my estimation.

I don't make policy, though - that seems to be your job generally, Barry. :)

>This had actually been tried in the past (just getting the records from
>the server connection as requested), but this behavior violates the spec
>and broke many peoples applications.  The problem is that if you don't use
>cursors, you end up tying up the connection until you finish fetching all
>rows.  So code like the following no longer works:
>
>get result set
>while (rs.next()) {
>   get some values from the result
>   use them to update/insert some other table using a preparedstatement
>}
>
>Since the connection is locked until all the results are fetched, you
>can't use the connection to perform the update/insert you want to do for
>each itteration of the loop.

Agreed on this point. However, nonetheless, and regardless of the fact that
this may break the spec and should not be the default behavior, this should
be an option, because the current way of the driver working is a horror for
anyone who has to deal with large result sets (such as I do on a regular
basis).

I don't mind keeping a connection locked for ages. I do mind running out of
VM space for a large result set which is streamed FROM THE DATABASE SERVER.
Result sets should have the ability to be streamed end to end, IMO - even
if it's a non-standard extension or an option to the connection when
created or the statement when created.

Again, I don't make policy, and I'm insufficiently motivated to do it
myself. Don't think it invalidates my opinion, but I won't kvetch about it
either. I just designed a class which does the same thing by taking a query
and turning it into a cursor-based query.

Cheers,

Doug