Re: JDBC 2 ResultSet and cursors

I understand your point now. But do not forget that in your servlet example
you're relying on the very feature of the current jdbc driver which consist
of caching the returned records in the ResultSet instance. With the
discussed backend-cursor-based implementation you could simply achieve the
exact same effect by implementing some kind of CachedResultSet and your code
would look like:

Connection myconnection = myconnectionpool.get();
Statement s = myconnection.createStatement();
// The constructor of CachedResultSet iterates through the rows of ResultSet
parameter
// calls the ResultSet.getObject(int) for each column and stores the values
in an internal list.
CachedResultSet crs = new CachedResultSet(s.executeQuery(String));
myconnectionpool.release(myconnection);
//use crs here as would use a cached ResultSet

Do not forget that you can use the connection the way you described (issuing
a new query in a new servlet invocation over the same connection while
you're still processing the result of a previous query) because the records
retrieved are cached.

On the other hand, I assume that you use some kind of connection pool anyway
(if you do not, I warmly recommend you to do so), so getting a new
connection will not effectively result in the backed forking a new physical
connection each time your servlet obtains a connection.

> -----Original Message-----
> From: Joseph Shraibman [mailto:jks(at)selectacast(dot)net]
> Sent: Thursday, October 18, 2001 11:19 PM
> To: Kovács Péter
> Cc: pgsql-jdbc(at)postgresql(dot)org
> Subject: Re: [JDBC] JDBC 2 ResultSet and cursors
>
>
> Right, but a single Connection should be able to be used
> concurently. For example if you
> were writing a servlet that handled incoming requests by doing:
> Statement s = myconnection.createStatement();
> ResultSet rs = s.doQuery();
> //use rs here
>
> You can't tie up the entire connection until the resultset is
> done with, because someone
> else may be hitting the servlet at the same time. You can't
> create a new Connection for
> each page load, because that would require the backend to fork.
>
> Kovács Péter wrote:
>
> > What do you mean by a connection being shared? What
> connection is shared
> > by/between whom? My understanding is that "physical"
> connections (at least
> > in the current jdbc driver coming with PostgreSQL) are NOT
> shared between
> > java.sql.Connection instances. (Each java.sql.Connection
> instance has the
> > exclusive use of a native connection to backend.) Am I
> missing something?
> > Are we talking about the same driver?
> >
> >
> >>-----Original Message-----
> >>From: Joseph Shraibman [mailto:jks(at)selectacast(dot)net]
> >>Sent: Wednesday, October 17, 2001 11:36 PM
> >>To: Kovács Péter
> >>Cc: pgsql-jdbc(at)postgresql(dot)org
> >>Subject: Re: JDBC 2 ResultSet and cursors
> >>
> >>
> >>To use a cursor behind the scenes would require the jdbc
> >>driver to have its own begin/end
> >>block. Because the connection is shared, there is nothing
> >>that prevents someone else from
> >>trying to execute a begin/end/commit/rollback statement that
> >>would screw up the driver.
> >>So it would need a seperate connection for each scrollable
> resultset.
> >>
> >>Kovács Péter wrote:
> >>
> >>
> >>>I am afraid I do not understand your explanation. What do
> >>>
> >>you mean by
> >>
> >>>"begin/end block"? I am thinking in terms of simple SQL
> >>>
> >>statements like:
> >>
> >>>begin;
> >>>declare myc cursor for select * from mm_history;
> >>>fetch forward 2 in myc;
> >>>close myc;
> >>>rollback; (or if you did some update in the same
> >>>
> >>transaction: commit;)
> >>
> >>>
> >>>
> >>>>-----Original Message-----
> >>>>From: Joseph Shraibman [mailto:jks(at)selectacast(dot)net]
> >>>>Sent: Tuesday, October 16, 2001 11:54 PM
> >>>>To: Kovács Péter
> >>>>Cc: pgsql-jdbc(at)postgresql(dot)org
> >>>>Subject: Re: JDBC 2 ResultSet and cursors
> >>>>
> >>>>
> >>>>Because the driver would have to dedicate a connection to the
> >>>>backend to the resultset to
> >>>>make sure nobody else tries to begin/end a block while it is
> >>>>trying to use a cursor.
> >>>>(that's the simple explanation) Since a connection to the
> >>>>backend currently requires a
> >>>>fork, it would be a real resource hog.
> >>>>
> >>>>Kovács Péter wrote:
> >>>>
> >>>>
> >>>>
> >>>>>Hi,
> >>>>>
> >>>>>I have a question for which I can think of an answer, but
> >>>>>
> >>still I am
> >>
> >>>>>uncertain about it.
> >>>>>
> >>>>>Why the scrollable result sets are not implemented in the
> >>>>>
> >>>>>
> >>>>current jdbc
> >>>>
> >>>>
> >>>>>driver? Is it technically impossible or just no one needed
> >>>>>
> >>>>>
> >>>>this feature yet?
> >>>>
> >>>>
> >>>>>The answer is probably that due to the lack of backend
> >>>>>
> >>>>>
> >>>>support for updatable
> >>>>
> >>>>
> >>>>>cursors the scrollable result set would not be fully
> >>>>>
> >>>>>
> >>>>functional. (On the
> >>>>
> >>>>
> >>>>>face of it, I think that it should be possible to build
> support for
> >>>>>read-only scrollable result set into the jdbc driver.) But
> >>>>>
> >>>>>
> >>>>would a halfway
> >>>>
> >>>>
> >>>>>solution not be better than nothing? You need to resort to
> >>>>>
> >>>>>
> >>>>workarounds
> >>>>
> >>>>
> >>>>>anyway, if you want to use cursor based data processing
> >>>>>
> >>>>>
> >>>>with PostgreSQL.
> >>>>
> >>>>
> >>>>>IMHO, a read-only scrollable result set would definitly be
> >>>>>
> >>>>>
> >>>>an important step
> >>>>
> >>>>
> >>>>>toward code portability.
> >>>>>
> >>>>>Any comments?
> >>>>>
> >>>>>Does anyone have info on whether there are plans to
> >>>>>
> >>>>>
> >>>>implement support for
> >>>>
> >>>>
> >>>>>updatable cursors in the backend? If there are, what are they?
> >>>>>
> >>>>>Thank you
> >>>>>
> >>>>>Peter
> >>>>>
> >>>>>---------------------------(end of
> >>>>>
> >>>>>
> >>>>broadcast)---------------------------
> >>>>
> >>>>
> >>>>>TIP 2: you can get off all lists at once with the
> >>>>>
> >>unregister command
> >>
> >>>>> (send "unregister YourEmailAddressHere" to
> >>>>>
> >>>>>
> >>>>majordomo(at)postgresql(dot)org)
> >>>>
> >>>>
> >>>>--
> >>>>Joseph Shraibman
> >>>>jks(at)selectacast(dot)net
> >>>>Increase signal to noise ratio. http://www.targabot.com
> >>>>
> >>>>
> >>>>
> >>
> >>--
> >>Joseph Shraibman
> >>jks(at)selectacast(dot)net
> >>Increase signal to noise ratio. http://www.targabot.com
> >>
> >>
>
>
> --
> Joseph Shraibman
> jks(at)selectacast(dot)net
> Increase signal to noise ratio. http://www.targabot.com
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
http://www.postgresql.org/users-lounge/docs/faq.html