Re: New significance of holdable result sets in Java 8

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 as many concurrent requests as the CPU can take.

--
Marko Topolnik

On 12. stu. 2014., at 20:18, Dave Cramer <pg(at)fastcrypt(dot)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 where N is some non-trivial number and the cursors are large. This would potentially be catastrophic since PostgreSQL will spill 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(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.
>
> 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
>>
>
>