Обсуждение: JDBC 2 ResultSet and cursors
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
Hi Peter
> 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?
As far as I know the read-only result sets are working in the recent drivers.
for instance
Statement stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stm.executeQuery("SELECT * FROM foolist");
rs.afterLast();
System.out.println("Printing it reversed");
while (rs.previous()){
System.out.println(rs.getString("id")+", "+rs.getString("text"));
}
works just fine with the latest stable driver
the same is true for last()/first() relative() and absolute() methods.
Regards
--
Jakub Labath
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@postgresql.org) > -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
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@selectacast.net] > Sent: Tuesday, October 16, 2001 11:54 PM > To: Kovács Péter > Cc: pgsql-jdbc@postgresql.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@postgresql.org) > > > > > -- > Joseph Shraibman > jks@selectacast.net > Increase signal to noise ratio. http://www.targabot.com >
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@selectacast.net] >>Sent: Tuesday, October 16, 2001 11:54 PM >>To: Kovács Péter >>Cc: pgsql-jdbc@postgresql.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@postgresql.org) >> >> >>-- >>Joseph Shraibman >>jks@selectacast.net >>Increase signal to noise ratio. http://www.targabot.com >> >> -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
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@selectacast.net] > Sent: Wednesday, October 17, 2001 11:36 PM > To: Kovács Péter > Cc: pgsql-jdbc@postgresql.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@selectacast.net] > >>Sent: Tuesday, October 16, 2001 11:54 PM > >>To: Kovács Péter > >>Cc: pgsql-jdbc@postgresql.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@postgresql.org) > >> > >> > >>-- > >>Joseph Shraibman > >>jks@selectacast.net > >>Increase signal to noise ratio. http://www.targabot.com > >> > >> > > > -- > Joseph Shraibman > jks@selectacast.net > Increase signal to noise ratio. http://www.targabot.com >
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@selectacast.net] >>Sent: Wednesday, October 17, 2001 11:36 PM >>To: Kovács Péter >>Cc: pgsql-jdbc@postgresql.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@selectacast.net] >>>>Sent: Tuesday, October 16, 2001 11:54 PM >>>>To: Kovács Péter >>>>Cc: pgsql-jdbc@postgresql.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@postgresql.org) >>>> >>>> >>>>-- >>>>Joseph Shraibman >>>>jks@selectacast.net >>>>Increase signal to noise ratio. http://www.targabot.com >>>> >>>> >>>> >> >>-- >>Joseph Shraibman >>jks@selectacast.net >>Increase signal to noise ratio. http://www.targabot.com >> >> -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
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@selectacast.net] > Sent: Thursday, October 18, 2001 11:19 PM > To: Kovács Péter > Cc: pgsql-jdbc@postgresql.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@selectacast.net] > >>Sent: Wednesday, October 17, 2001 11:36 PM > >>To: Kovács Péter > >>Cc: pgsql-jdbc@postgresql.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@selectacast.net] > >>>>Sent: Tuesday, October 16, 2001 11:54 PM > >>>>To: Kovács Péter > >>>>Cc: pgsql-jdbc@postgresql.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@postgresql.org) > >>>> > >>>> > >>>>-- > >>>>Joseph Shraibman > >>>>jks@selectacast.net > >>>>Increase signal to noise ratio. http://www.targabot.com > >>>> > >>>> > >>>> > >> > >>-- > >>Joseph Shraibman > >>jks@selectacast.net > >>Increase signal to noise ratio. http://www.targabot.com > >> > >> > > > -- > Joseph Shraibman > jks@selectacast.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