Re: New significance of holdable result sets in Java 8

I’m inclined to agree with Dave, your usage of holdable cursors sounds like an extremely burdensome (server wise) solution to a “nice to have” application architecture solution.

Why not make a stream adapter that fetches results in groups using LIMIT/OFFSET. This would work in any situation regardless of cursors, transactions, etc and would *only* cost for large result sets that need to extra round trips.

That being said… pgjdbc-ng uses real cursors when asked for them and respects the foldability requirement. There are limitations based on postgres’s feature set but I believe what you are asking for works.

> On Nov 12, 2014, at 11:51 AM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>
> Marko,
>
> When you say holdable cursors are you referring to a holdable cursor outside of a transaction? It seems so because the transaction 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 <http://www.credativ.ca/>
>
> On 12 November 2014 10:22, Marko Topolnik <marko(dot)topolnik(at)gmail(dot)com <mailto:marko(dot)topolnik(at)gmail(dot)com>> wrote:
> As of the release of Java 8 and its Streams API a new door has opened for many things, including an important improvement in 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 method contacts the database and returns a Model-oriented representation of the response; the View layer then transforms it into 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-shaped response, you would return a List. This meant eager loading of all data needed for the response, which caused scalability 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 convenient to make this stream pull data directly from an underlying ResultSet, tronsforming each row on-the-fly into a Model object. This, however, calls for holdable result sets because the transaction commits when program control leaves the Service layer.
>
> The Spring team has recognized the relevance of the above use case and with release 4.1.2 they have introduced a specific enhancement 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 helper code 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 by native holdable cursors instead of the current client-side cursors, which don't allow the space complexity to be reduced from O(n) to O(1) on the JVM side. I am aware that this is not a trivial endeavor as it requires intervention into the FE/BE protocol, but I would nevertheless propose that this concern be reassessed in the light of new developments in the Java ecosystem.
>
> Regards,
> Marko Topolnik
>
>
>
> [1] https://jira.spring.io/browse/SPR-12349 <https://jira.spring.io/browse/SPR-12349>
> [2] https://jira.spring.io/browse/SPR-12388 <https://jira.spring.io/browse/SPR-12388>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org <mailto:pgsql-jdbc(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc <http://www.postgresql.org/mailpref/pgsql-jdbc>
>