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

Dave,

when you say this, is the hat you are wearing that of a database implementor or that of a system designer?

A system designer may very reasonably want his system designed along the following guidelines:

1. the database is about storage: give it plenty of that, and make it fast (SSD);
2. the middle tier is about business logic: focus on CPU power and internet bandwith, dimension RAM as needed to serve
asmany concurrent requests as the CPU can take. 

--
Marko Topolnik

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

> Marko,
>
> While PG might take less RAM per cursor than the client side, we will very quickly run out if there are N clients
whereN is some non-trivial number and the cursors are large. This would potentially be catastrophic since PostgreSQL
willspill to disk and potentially run out of disk space. 
>
> You may argue that it might not happen but I'd prefer the client crashed than the server.
>
> Dave
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
> On 12 November 2014 13:58, 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. 
>
> That being said… pgjdbc-ng uses real cursors when asked for them and respects the foldability requirement.  There are
limitationsbased 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 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
>>
>
>