Re: Disk buffering of resultsets

4 жовт. 2014 10:56, користувач "Craig Ringer" <craig@2ndquadrant.com> написав:
>
> On 09/23/2014 09:39 AM, Vitalii Tymchyshyn wrote:
> > Some more thoughts:
> > 1) is it really not possible to stream results in autocommit mode? The
> > only problem I can see is that connection can't be used (e.g. by other
> > statement objects) until after all the results are fetched. So what? In
> > many cases it's OK.
>
> It is possible to stream results in autocommit mode.
>
> You can't do so without an explicit transaction using SQL-level cursors
> (DECLARE and FETCH) unless you use "WITH HOLD" cursors, which have their
> own problems. But you can do so at the protocol level, so long as you
> don't send another query until you've consumed all the results you want
> or don't require any more results.
>
> For details, see the Execute message documentation in the v3 protocol,
> particularly the notes about the row-count limit. Basically, you send
> repeated Execute messages to fetch chunks of rows, until you don't get
> any more rows or don't want any more.
>
> Here's something I wrote on the topic recently, which is accurate to the
> best of my knowledge:
>
> http://stackoverflow.com/a/25907922/398670

Yes, thats more or less what I was thinking about. No caching is needed. Currently postgresql does not support it according to my knowledge and documentation (ch. 5).

>
> > 2) another alternative to temp files are direct buffers. They are not
> > exactly the same, but are not counted towards heap and can be swapped
> > out by system if needed.
>
> "Direct buffer" appears to referer to a Java NIO ByteBuffer. Right?
>
> They are indeed an alternative, something more like BigMemory.
>
> The number of options around are exactly why I think this needs to be a
> simple interface that you can plug implementations into. Start with a
> tempfile interface, but allow for future needs.

For me actually a Q if its needed at all, if we get rid of OOMs with (1).
>
> > 3) For any disk saving I'd
> > - postpone it as long as possible (e.g. until after connection is used
> > by another statement)
>
> You can't really do that. You could fetch and flush results just before
> issuing another statement, as part of the execute call, but not
> afterwards. I think that's what you meant, though.
>
> This could lead to surprises where the execution of the next statement
> throws an error that's really from the prior statement, though.

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.

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

>
> So your app would have to have already fetched the whole resultset.
>
> If you're talking about adding support for resultsets that aren't
> auto-closed when the next statement is run, that's a different matter
> entirely to what we've discussed so far. It might be useful, but it's
> not the same as just spilling big results to disk.
>
> > - do as little as possible (may be by saving frames from network as is
> > with little decoding)
>
> That really won't work well. We have to read the stream and parse the
> messages. Otherwise we'll fail to see asynchronous notifications, error
> messages, etc.

Thats why I said "little decoding", not "no decoding". But you still dont need to do all the field parsing.

>
> Not only that, PgJDBC doesn't have access to "network frames" anyway.
> It's working with a stream-oriented socket. The lowest level it can
> possibly work with is the byte stream.

That would be very compact already and good to be used in cache.

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

>
> Not only that, it's also pointless because there's only one TCP
> connection to work with. So while the "background" thread is saving the
> resultset, everything else that wants to use the connection has to wait
> anyway.

Yes, but row processing by application also takes time. And copying from network to some sort of cache (e.g. linear file write) would be fast and not cpu-consuming. Separate thread for network reading may even reduce the effect of network latencies and speed things up, esp. on high latency connections.

>
> > Anyway one needs to go through everything to issue next command, does
> > not he?
>
> Yes, per the protocol documentation.
>
> > The question is if all of this needs to be saved or simply
> > skipped (as soon as result set is closed).
>
> If we're fetching chunks of the resultset progressively, we can just
> close the resultset and close the server-side portal.
>
> 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 you might be trying to solve a different problem to what Denis
> is talking about.
>

Denis: any comments?