Обсуждение: Proposal for a configurable ResultSet implementation

Hallo,

1.)
I think that the implementation of the class ResultSet is not consistent
with the intention of the JDBC2 API designer. The intention of the API
designer was clearly to use a server side cursor for the ResultSet, this is
why the ResultSet.getCursorName() exists. Also, look at methods like
java.sql.ResultSet.absolute(int) for example. The API Spec. says: "Moves the
cursor to the given row number in the result set." and similar. (The current
implementation caches the whole result set on the client side.)

2.)
The PostgreSQL backend has the very nice feature that the cursor can be
moved back and forth. (With Oracle and Sybase you can move the cursor only
forward [I was told that ORA9i supports backward cursor movement, but I did
not check].)

3.)
There may exist a number of applications relying on the current behaviour
(full client side caching by the jdbc-driver). Based on mails on this list I
know of at least one implementation that keeps the result set returned from
Statement.execute() and uses it later, while the connection itself is
already being used by some other thread.

Proposal:
Let's provide two implementations for the ResultSet: the default would be
the current implementation, and another optional (activated through
properties or something) using server side cursor. I have not yet looked
very closely at the feasibility of my proposal, but on the face of it, it
can be implemented. First I'd like to monitor people's opinion about this.


Peter

PS:
I have to go off line for a few days next week, but I will (I hope) return.

Peter,

This is already listed on the todo list.  I agree that this
functionality is useful and necessary under some circumstances, and I
don't think it will be too hard to implement.  I was planning on doing
this for 7.3, but if you want to work on this, I can work on other todo
items.

There are some interesting cases to look at.  For example how would you
handle the following:

ResultSet l_rset = l_stmt.executeQuery(
    "insert into foo values(1,2,3); " +
    "select * from foo; ");

You can do this sort of bunching of multiple sql statements into one
call today and it will work (in fact I use this often to reduce network
roundtrips).  The result set returned will be for the last statement
executed in the bunch.  How do you implicitly turn this into something
that usese cursors, without needing to implement a parser in the jdbc code?

The complexity of this feature isn't in making the functionality work,
but in understanding when it can or should be used and when it can't or
shouldn't be used.

thanks,
--Barry

Kovács Péter wrote:

> Hallo,
>
> 1.)
> I think that the implementation of the class ResultSet is not consistent
> with the intention of the JDBC2 API designer. The intention of the API
> designer was clearly to use a server side cursor for the ResultSet, this is
> why the ResultSet.getCursorName() exists. Also, look at methods like
> java.sql.ResultSet.absolute(int) for example. The API Spec. says: "Moves the
> cursor to the given row number in the result set." and similar. (The current
> implementation caches the whole result set on the client side.)
>
> 2.)
> The PostgreSQL backend has the very nice feature that the cursor can be
> moved back and forth. (With Oracle and Sybase you can move the cursor only
> forward [I was told that ORA9i supports backward cursor movement, but I did
> not check].)
>
> 3.)
> There may exist a number of applications relying on the current behaviour
> (full client side caching by the jdbc-driver). Based on mails on this list I
> know of at least one implementation that keeps the result set returned from
> Statement.execute() and uses it later, while the connection itself is
> already being used by some other thread.
>
> Proposal:
> Let's provide two implementations for the ResultSet: the default would be
> the current implementation, and another optional (activated through
> properties or something) using server side cursor. I have not yet looked
> very closely at the feasibility of my proposal, but on the face of it, it
> can be implemented. First I'd like to monitor people's opinion about this.
>
>
> Peter
>
> PS:
> I have to go off line for a few days next week, but I will (I hope) return.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>

Barry,

> -----Original Message-----
> From: Barry Lind [mailto:barry@xythos.com]
> Sent: Friday, January 11, 2002 6:46 PM
> To: Kovács Péter
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: Proposal for a configurable ResultSet implementation
>
>
> Peter,
>
> This is already listed on the todo list.  I agree that this
> functionality is useful and necessary under some circumstances, and I
> don't think it will be too hard to implement.  I was planning
> on doing
> this for 7.3, but if you want to work on this, I can work on
> other todo
> items.

As long as I do not have an estimation about the amount of work involved in
the (design and) implementation of this item, I would be very cautious to
make any commitments. (If I happen to decide that I can implement something
reasonable within a reasonable time frame, I will contact you before I start
any sizeable implementation work.)

>
> There are some interesting cases to look at.  For example how
> would you
> handle the following:
>
> ResultSet l_rset = l_stmt.executeQuery(
>     "insert into foo values(1,2,3); " +
>     "select * from foo; ");
>
> You can do this sort of bunching of multiple sql statements into one
> call today and it will work (in fact I use this often to
> reduce network
> roundtrips).  The result set returned will be for the last statement
> executed in the bunch.  How do you implicitly turn this into
> something
> that usese cursors, without needing to implement a parser in
> the jdbc code?

Is the parameter passed to Statement.executeQuery(String) in your example
one statement or two. Instinctively, I would say that it's two statements
(but I am not sure). The JDBC API doc says this about the method
Statement.executeQuery(String): "Executes a SQL statement that returns a
single ResultSet." If my assumption is correct and one "insert ..." plus one
"select ..." are two statements, then the API is not correctly used in your
example and as such I would be inclined to dismiss the problem.

A related (but not necessarily equivalent) question is: Is the syntax used
in your example to concatenate several SQL statements (or --if the parameter
can be considered as one SQL statement-- to concatenate several components
of the same SQL statement) portable across the JDBC drivers of the currently
popular RDBMSs? (In my view, one of the advantages of PosgtreSQL over MySQL
is that [to my knowledge] PostgreSQL implements significantly more of the
functionality offered by the SQL spec. Though our views over the importance
of portability may differ.)

Instinctively, in most cases I would prefer to use functions (aka stored
procedures) over concatenation to reduce network roundtrips. Functions make
database product specificities more transparent to the application (at
practically no cost).

> The complexity of this feature isn't in making the
> functionality work,
> but in understanding when it can or should be used and when
> it can't or
> shouldn't be used.

I am afraid I can only guess your point here: Do you mean that the gains
using a cursor for a query do not always justify the overhead of creating
the cursor?

Thank you

Peter