Re: New significance of holdable result sets in Java 8

On Nov 12, 2014, at 11:20 AM, Marko Topolnik <marko(dot)topolnik(at)gmail(dot)com> wrote:
>
> I am aware of this approach; I even discuss it as a typical workaround in my blog post [1]. The problem is, this completely dispenses with the detailed and complex setup present in the View layer: the response format can be driven by the Accept HTTP header, encoding may be adapted to the request, and so on. I did pretty much that in my Clojure projects because Clojure's ecosystem isn't as well-developed so roll-your-own solutions are the norm. With Spring, however, you lose a great deal when you give your Controller method access to the raw response.
>
> I have already developed a Stream-based REST service along the lines I discuss in the mentioned blog post and the advantages are quite clear to me. It is a direction that Spring would probably like to move in as well.
>
> [1] http://www.airpair.com/v1/posts/spring-streams-memory-efficiency
> ...
> You still seem to dictate the output format directly from the Controller, the avoidance of which is motivating my approach.
>

Indeed, after reading your blog post, it looks like we have 95% the exact same solution.

The essential difference I propose is that instead of having a holdable ResultSet (which requires PG-JDBC changes)
you hold the Connection/Statement/ResultSet with transaction open for the duration.

This way the transaction stays open for the duration of your request. So you establish a stream all the way through
from end client -> PG.

With your suggestion, a holdable ResultSet, you run into what Dave mentioned:

http://www.postgresql.org/docs/9.3/static/sql-declare.html
> In the current implementation, the rows represented by a held cursor are copied into a temporary file or memory area so that they remain available for subsequent transactions.

So in fact you are just materializing the result set server side. This IMO defeats the entire purpose - you cannot begin streaming out results until the materialize finishes (the holdable cursor will not be available until said materialize is done, I believe? or at least the transaction will not commit until that point?) Therefore my proposal is significantly more efficient and will never surprisingly materialize gigabytes of temporary disk usage.

Makes more sense?