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

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

>
> On Nov 12, 2014, at 10:58 AM, Kevin Wooten <kdubb@me.com> wrote:
>
>> I’m inclined to agree with Dave, your usage of holdable cursors sounds like an extremely burdensome (server wise)
solutionto 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
regardlessof 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)
foranything that isn't reading rows in database order (unordered) or index order.  This is because you end up paying
thestartup cost once per batch instead of once per logical query.  Additionally it is now sensitive to concurrent
insertsand 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
arelimitations 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@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
veryconvenient to make this stream pull data directly from an underlying ResultSet, tronsforming each row on-the-fly
intoa Model object. This, however, calls for holdable result sets because the transaction commits when program control
leavesthe Service 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
>>>
>>
>