Re: Disk buffering of resultsets

On 10/05/2014 03:16 AM, Vitalii Tymchyshyn wrote:
> Well, the exception in this case should be "routed" to the statement
> that run the problematic query. Next one should get something only if
> connection became ususable as a result.

You can't do that, the logic flow and timing are all wrong.

The opportunity to throw an exception at the right place is gone and
past by the time you get to this point. You can't go back in the code
and throw an exception at some prior point of execution.

If the statement still exists you can attach the exception that would be
thrown to it such that you throw it next time somebody calls a method on
that statement or its result set though.

>> It also
>> makes no sense, as when you execute a new statement, the resultset of
>> the prior statement is automatically closed.
>
> Do they? I think they are closed only for the same statement object.
> Different statement may try to reuse the connection.

Yay, specification reading time.

https://jcp.org/aboutJava/communityprocess/final/jsr221/

The JDBC spec is one of the less awful Java specs, thankfully. (Whatever
you do, do not attempt to read the JSF2 specification).

Short version: you're right, you can have multiple open statements, each
with a valid open resultset. The JDBC implementation is free to decide
how it does this based on the capabilities of the database.

We're allowed to close all statements and result sets at commit time,
either implicit autocommit or explicit commit. If a user wants to keep a
resultset past that time they must set it as a holdable resultset with
the HOLD_CURSORS_OVER_COMMIT flag.






Detail of relevant spec sections:

13.1.1 "Creating statements":

> Each Connection object can create multiple Statement objects that may
> be used concurrently by the program.

 13.1.4 (pdf page 117)

> An application calls the method Statement.close to indicate that it has finished
> processing a statement. All Statement objects will be closed when the connection
> that created them is closed. [...]
>
> Closing a Statement object will close and invalidate any instances of ResultSet
> produced by that Statement object. [...]

and 15.1 "Result Sets", particularly 15.1.3 "Resultset Holdablity"

> Calling the method Connection.commit can close the ResultSet objects >
that have been created during the current transaction.

and 15.2.5 "Closing a resultset object":

A ResultSet object is explicitly closed when
* The close method on the ResultSet is executed, thereby releasing any
external resources
* The Statement or Connection object that produced the ResultSet is
explictly closed
A ResultSet object is implicitly closed when
* The associated Statement object is re-executed
* The ResultSet is created with a Holdability of
CLOSE_CURSORS_AT_COMMIT and an implicit or explicit commit occurs

Also, note that PgJDBC declares that:

"For Select statements, the statement is complete when the associated
result set is closed."

so in autocommit we're allowed to keep a transaction open with a cursor
streaming results until the resultset is closed.


>> > - do it in background (a little conflicting to postponing, but more
>> > thinking is needed). Return first fetchSize rows and start copying
>> > network to disk in background thread.
>>
>> You don't want to go there. It's horribly complicated to work with
>> background threads portably in the JDBC driver. For an example, see the
>> recent discussion of Timer handling.
>
> But we've already got separate threads. Why can't we have some more?

The threading we're already doing is causing issues.

Threading is very different in Java SE and Java EE environments. Lots of
care is required to cope with driver unloads/reloads, avoiding
classloader leaks, etc.

>> I'm pretty sure this is already possible in PgJDBC (when you set a fetch
>> size) though I'd need to write a test case and do some code reading to
>> be totally sure.
>
> As far as I understand it's not, and by implementing this we could solve
> a lot of issues for large result sets.

I think we might have a different idea of what "this" is.

Perhaps it would be helpful if you described the underlying problem
you're trying to solve? We've kind of started at the solution, without
really defining the problem the solution is for.

From what I can tell I think you might be trying to make holdable
resultsets in autocommit mode more efficient by implementing lazy
resultset fetching.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services