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

Yes, your approach makes perfect sense---as long as you don't care about transactional semantics.

If the transaction boundary is moved all the way to the end of serving the response, the following happens:

1. 200 OK status code reported, but transaction still not committed;
2. transaction may roll back due to errors in transfer;
3. thanks to buffering, the transfer may also fail _after_ the transaction has committed.

So, not only have you established quite messy and illogical transaction semantics for your client (for example, a
typicalonSuccess callback on the HTTP request cannot assume the transaction succeeded); the client has no idea _in
principle_what happened to the transaction if transfer is aborted for any reason. 

In an earlier incarnation of my project I did indeed have setup exactly as you describe it, but after realizing the
aboveproblems, I had to back off and revert to standard transaction boundaries. 


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

>
> 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?
>