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

On Nov 13, 2014, at 4:08 AM, Marko Topolnik <marko.topolnik@gmail.com> wrote:

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

This is entirely correct.

We decided that this was OK for our use case, as in general queries that returned enough data to be worth streaming
weresimple SELECTs which were not expected to commingle with UPDATEs or otherwise promise any transactional guarantees
(otherthan the read was a consistent snapshot). 

We also changed our API such that after all results streamed, it emitted a status report.  e.g.

HTTP 200 OK
{ "results" : [..., ...], "status": {"success": true} }

A little janky, but it worked well for us.

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