Re: New significance of holdable result sets in Java 8

On 12. stu. 2014., at 20:19, Steven Schlansker <stevenschlansker(at)gmail(dot)com> wrote:

>
> On Nov 12, 2014, at 10:58 AM, Kevin Wooten <kdubb(at)me(dot)com> wrote:
>
>> 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.
>
> IMO LIMIT/OFFSET is generally strictly inferior to holding a transaction open (like I proposed in my earlier mail) for anything that isn't reading rows in database order (unordered) or index order. This is because you end up paying the startup cost once per batch instead of once per logical query. Additionally it is now sensitive to concurrent inserts and you may observe e.g. duplicated or missing rows unless you are *very* careful.

This is exactly the workaround I mention in my blog post, and those are exactly the concerns I raise about it :)

http://www.airpair.com/v1/posts/spring-streams-memory-efficiency

>
>>
>> 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
>>>
>>> On 12 November 2014 10:22, Marko Topolnik <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
>>> [2] https://jira.spring.io/browse/SPR-12388
>>>
>>> --
>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>>
>>
>