Re: New significance of holdable result sets in Java 8

Thanks for bringing this up! I'm not an expert on the PG FE/BE protocol, so please excuse my ignorance below :)

On Nov 12, 2014, at 7:22 AM, Marko Topolnik <marko(dot)topolnik(at)gmail(dot)com> wrote:
>
> 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.

We solved this problem (pre-Streams) by inverting the flow of control. In pseudo-JAX-RS with Jackson:

@Path("/objects")
public StreamingOutput getObjects() {
return os -> {
JsonGenerator jg = new JsonGenerator(os);
ResultSet rs = findObjects(); // make sure to do this in a txn with fetch size set sensibly or you will collect all rows eagerly
while (rs.hasNext()) { jg.writeObject(rs.getString("whatever")); }
jg.close(); rs.close();
};
}

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

Having a holdable result set escape the service layer feels like a similar layering violation as having an open transaction escape the service layer. Either way a heavyweight database object escapes and must be cleaned up at a later date. Naïvely I would expect the cost of holding the two objects to be similar, since the actual underlying transaction cannot commit/rollback until all results are read from the cursor?

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

As an alternate thought, Stream is AutoCloseable. What if we introduce a StatementStream, which takes a (Prepared)Statement, executes in a transaction with small fetch size, and then returns a Stream<DTO>. Again very pseudo-codey:

class DAO {
Stream<DTO> findObjects() {
return new StatementResultStream(conn, "SELECT * FROM objects"); // sets fetch size, ensure auto commit off
}
}

class JsonStreamOutput<T> implements StreamingOutput {
StreamOutput(Supplier<Stream<T>> streamSupplier) { this.streamSupplier = streamSupplier; }
public void write(OutputStream os) {
try (JsonGenerator jg = new JsonGenerator(os);
Stream<T> str = streamSupplier.get()) {
str.forEach(obj -> jg.writeObject(obj));
}
}
}

@Path("/objects")
class Resource {
@GET
public StreamOutput<TransformedDTO> getObjects() {
return new JsonStreamOutput(() -> dto.findObjects().filter(...).map(dto -> new TransformedDto(dto)));
}
}

The StatementResultStream and StreamOutput classes are entirely reusable. The DAO is no more complicated than before, except instead of using JDBC directly you punt the work until the Stream undergoes a terminal operation. The main difficulty here is figuring out a sensible API for StatementResultStream construction.

The only bit I'm not happy with is the fact that you have to lift your intermediate stream operations into a lambda. You might be able to avoid this by instead writing the whole bit in terms of a Spliterator instead and using StreamSupport.stream(spliterator), but that introduces questions about held resources since Spliterator is not AutoCloseable.

This has the advantage that it's implementable today without changes to either JDBC or PGJDBC, and introduces only reusable optional components to e.g. Spring or JDBI or whatever library wishes to do this.

Thoughts?
Steven