Re: New significance of holdable result sets in Java 8

On Nov 12, 2014, at 11:20 AM, Marko Topolnik <marko.topolnik@gmail.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
completelydispenses with the detailed and complex setup present in the View layer: the response format can be driven by
theAccept HTTP header, encoding may be adapted to the request, and so on. I did pretty much that in my Clojure projects
becauseClojure's ecosystem isn't as well-developed so roll-your-own solutions are the norm. With Spring, however, you
losea 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
advantagesare 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
sothat 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
beginstreaming out results until the materialize finishes (the holdable cursor will not be available until said
materializeis done, I believe?  or at least the transaction will not commit until that point?)  Therefore my proposal
issignificantly more efficient and will never surprisingly materialize gigabytes of temporary disk usage. 

Makes more sense?