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

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.topolnik@gmail.com> wrote:
>
> Data is passed from Controller to View as the return value of a method. Traditionally, if you wanted a
collection-shapedresponse, you would return a List. This meant eager loading of all data needed for the response, which
causedscalability 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
rowseagerly 
    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
convenientto make this stream pull data directly from an underlying ResultSet, tronsforming each row on-the-fly into a
Modelobject. This, however, calls for holdable result sets because the transaction commits when program control leaves
theService layer. 

Having a holdable result set escape the service layer feels like a similar layering violation as having an open
transactionescape the service layer.  Either way a heavyweight database object escapes and must be cleaned up at a
laterdate.  Naïvely I would expect the cost of holding the two objects to be similar, since the actual underlying
transactioncannot 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
bynative holdable cursors instead of the current client-side cursors, which don't allow the space complexity to be
reducedfrom O(n) to O(1) on the JVM side. I am aware that this is not a trivial endeavor as it requires intervention
intothe FE/BE protocol, but I would nevertheless propose that this concern be reassessed in the light of new
developmentsin 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,
exceptinstead of using JDBC directly you punt the work until the Stream undergoes a terminal operation.  The main
difficultyhere 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.
Youmight 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
reusableoptional components to e.g. Spring or JDBI or whatever library wishes to do this. 

Thoughts?
Steven