Re: New significance of holdable result sets in Java 8 - Mailing list pgsql-jdbc

Yes, that is correct, Dave. The transaction would have to commit and the cursor would have to live on until the JDBC
connectionis released back to the pool. I am aware that this transfers the cost from the JVM into PG; however I also
thinkthat PG is in a much better position to fine-tune its policies around this concern.  

Even if comparing RAM-only solutions at either end, PG would almost surely take less RAM to hold the data in its native
formthan the JVM uses to hold the client-side cursor; and further, PG would have much better opportunities to overflow
todisk than the JDBC driver, which probably doesn't have that option at all. 

--
Marko Topolnik


On 12. stu. 2014., at 19:51, Dave Cramer <pg@fastcrypt.com> wrote:

> Marko,
>
> When you say holdable cursors are you referring to a holdable cursor outside of a transaction? It seems so because
thetransaction commits after leaving the service layer ? 
>
> If so these are not without significant cost on the server side.
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
> On 12 November 2014 10:22, Marko Topolnik <marko.topolnik@gmail.com> wrote:
> As of the release of Java 8 and its Streams API a new door has opened for many things, including an important
improvementin the way RESTful services can be implemented. Let me briefly describe the architecture with Spring's REST
support:an MVC framework is used where the Controller dispatches the HTTP request to a Service method; the Service
methodcontacts the database and returns a Model-oriented representation of the response; the View layer then transforms
itinto the actual HTTP response bytes. 
>
> Data is passed from Controller to View as the return value of a method. Traditionally, if you wanted a
collection-shapedresponse, you would return a List. This meant eager loading of all data needed for the response, which
causedscalability issues related to the JVM heap space. 
>
> With the Streams API it is now very convenient to return a lazily-evaluated stream of Model objects. It is also very
convenientto make this stream pull data directly from an underlying ResultSet, tronsforming each row on-the-fly into a
Modelobject. This, however, calls for holdable result sets because the transaction commits when program control leaves
theService layer. 
>
> The Spring team has recognized the relevance of the above use case and with release 4.1.2 they have introduced a
specificenhancement needed to support result sets holdable into the View layer (albeit only when JDBC is used over
Hibernate).This is described in the issue SPR-12349 [1]. Spring also plans to support this use case with additional
helpercode which turns Hibernate's ScrollableResults into a Stream (SPR-12388 [2]). 
>
> The above could raise the level of interest of the PostgreSQL JDBC team in implementing holdable result sets backed
bynative holdable cursors instead of the current client-side cursors, which don't allow the space complexity to be
reducedfrom O(n) to O(1) on the JVM side. I am aware that this is not a trivial endeavor as it requires intervention
intothe FE/BE protocol, but I would nevertheless propose that this concern be reassessed in the light of new
developmentsin the Java ecosystem. 
>
> Regards,
> Marko Topolnik
>
>
>
>  [1] https://jira.spring.io/browse/SPR-12349
>  [2] https://jira.spring.io/browse/SPR-12388
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>