Обсуждение: Disk buffering of resultsets

Enrico Olivelli - Diennea wrote on 19.09.2014 12:44:
> Hi,
>
> we are moving from Oracle to PostgreSQL and we hit a serious
> problem.
>
> When executing a query with a large result set your driver loads each
> record in memory and this is a blocking problem for us.
>
> The work-around is to use server-side cursors
> (http://jdbc.postgresql.org/documentation/head/query.html) but in
> order to do this we have to change autocommit mode of the transaction
> and this will change transaction semantics of out code.
>
> Many JDBC drivers provide some swap-to-disk function in oredr to deal
> with huge result sets
>
> Something like “if we are loading in RAM more than X MBs than swap on
> disk”
>
> or  “if we are loading in RAM more than X rows than swap on disk”
> (more simple to implement)
>

Wouldn't it make more sense to allow for a non-buffered result when using auto-commit
(something like "bufferResults=false") instead?

Or is that technically not possible due to the Postgres wire-protocol?

Thomas

On 09/21/2014 11:24 AM, Lussier, Denis wrote:
> This does seem very worthwhile.  Can someone please sketch out a
> mini-design and see if it makes sense to the pgjdbc core?   I'd be
> willing to hack some code, but, I'd want the design to be pre-vetted.
>
> Here's my first quick strawman:
>
> 1.) Get the TEMP directory (may be OS specific).

Use Java's tempfile handling, let it use the default temporary
directory, but give the user the option of overriding it with a JDBC
driver parameter.

Java 7, use the Path and Files classes for tempfile handling.

Java 6 and below, use File.createTempFile(...).

Both let you override the target directory.

> 2.) After reading more than 100 rows, determine the average size per row
> and check the available JVM memory.

I'm not sure you can portably check available JVM memory.

I'd prefer to see if we can use an existing, mature library that
transparently serializes resultsets to disk under memory pressure.
Possibly including use of soft references to load results from the
on-disk store and let them be discarded by the JVM under pressure.

Rather than reinventing the wheel, lets see what already exists in this
problem space.

> 3.) Flush the rows to disk once more than 65% of JVM memory is exhausted
> OR according to a parameter that can be set.

I'd rather have low-cost spill to disk that we can use earlier and more
aggressively when we're reading whole tuple sets at once, rather than
use up lots of JVM memory and possibly unnecessarily evict soft
reference based caches from applications.

On Linux, if it wasn't Java code I'd want to just create an mmap()ed
file and start writing to it for result sets of almost any size. Let the
OS deal with whether to actually flush it to disk or not. I'm not sure
if that's practical from Java though, and IIRC Windows has more
aggressive writing of memory mapped files that could cause performance
issues.




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

On 09/21/2014 03:06 PM, Thomas Kellerer wrote:
> Wouldn't it make more sense to allow for a non-buffered result when
> using auto-commit
> (something like "bufferResults=false") instead?
>
> Or is that technically not possible due to the Postgres wire-protocol?

If you expect the statement to have committed when the execute returns,
then it's not practical.

You could use a WITH HOLD cursor, but that'd be horribly inefficient; it
basically forces the server to buffer the result instead.

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

On 09/21/2014 11:24 AM, Lussier, Denis wrote:
> This does seem very worthwhile.  Can someone please sketch out a
> mini-design and see if it makes sense to the pgjdbc core?   I'd be
> willing to hack some code, but, I'd want the design to be pre-vetted.
>
> Here's my first quick strawman:
>
> 1.) Get the TEMP directory (may be OS specific).

Actually, on second thought, if we're going to do this we'd be silly to
restrict it to spilling to disk.

What we should have is the ability to flush a resultset to non-memory
storage that provides a given interface when it exceeds a given size.

That non-memory storage might be disk, it might be
Redis/memcached/EHCache/BigCache/BigMemory/GemFire .../ whatever. In
fact, it's more likely to be one of those than it is to be a simple
on-disk cache for people who care about performance and have big result
sets.


All we need from a resultset storage layer is the ability to:

* Register a new result set with the storage

* Append a tuple to the storage

* Fetch a tuple from the storage

* Reliably destroy all storage associated with a resultset when the
resultset is closed, the JVM exits, or the JVM/host crash. For crash,
this cleanup might be a clean sweep.

* Copy an existing, possibly incomplete result set to a new storage
location (e.g. copy an in-memory resultset to disk).

* ... and do this in a manner that makes the storage location
unpredictable and minimises risk of attacks that aren't already possible
using reflection against in-memory result sets.


An API like that is a good fit for K/V stores like Redis, as well as for
Memcached, and for disk storage.

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

On Sep 21, 2014, at 12:35 AM, Craig Ringer <craig@2ndquadrant.com> wrote:

> On 09/21/2014 11:24 AM, Lussier, Denis wrote:
>> This does seem very worthwhile.  Can someone please sketch out a
>> mini-design and see if it makes sense to the pgjdbc core?   I'd be
>> willing to hack some code, but, I'd want the design to be pre-vetted.
>
> Actually, on second thought, if we're going to do this we'd be silly to
> restrict it to spilling to disk.
>
> What we should have is the ability to flush a resultset to non-memory
> storage that provides a given interface when it exceeds a given size.

This all sounds very interesting, but should it really be baked into the driver?
Shouldn’t such a mechanism be built on top of the JDBC API?  Then it could work
independently of the Driver implementation.

Additionally, if this does get implemented, please leave it off by default.  We
have many SSDs backing our database server and very little space / IOPS on
application nodes (intentionally, and I’m not sure we are the only ones) so
suddenly spilling to disk could be disastrous for our performance.

Atenciosamente,

Edson Carlos Ericksson Richter

On 22-09-2014 14:16, Steven Schlansker wrote:
> On Sep 21, 2014, at 12:35 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
>
>> On 09/21/2014 11:24 AM, Lussier, Denis wrote:
>>> This does seem very worthwhile.  Can someone please sketch out a
>>> mini-design and see if it makes sense to the pgjdbc core?   I'd be
>>> willing to hack some code, but, I'd want the design to be pre-vetted.
>> Actually, on second thought, if we're going to do this we'd be silly to
>> restrict it to spilling to disk.
>>
>> What we should have is the ability to flush a resultset to non-memory
>> storage that provides a given interface when it exceeds a given size.
> This all sounds very interesting, but should it really be baked into the driver?
> Shouldn’t such a mechanism be built on top of the JDBC API?  Then it could work
> independently of the Driver implementation.
>
> Additionally, if this does get implemented, please leave it off by default.  We
> have many SSDs backing our database server and very little space / IOPS on
> application nodes (intentionally, and I’m not sure we are the only ones) so
> suddenly spilling to disk could be disastrous for our performance.

Seems obvious, because new features like this proposal should always be
off by default.
+1. I've designed application architecture to overcome such limitations,
and I'm really not interested in having disk cache slowing down my App
server...

Thanks,

Edson.


>
>
>

On 9/22/2014 10:16 AM, Steven Schlansker wrote:
> Additionally, if this does get implemented, please leave it off by default.  We
> have many SSDs backing our database server and very little space / IOPS on
> application nodes (intentionally, and I’m not sure we are the only ones) so
> suddenly spilling to disk could be disastrous for our performance.

we have a high volume messaging subsystem that normally uses no disk,
but if a subscriber goes offline, it spools to disk, then when the
subscriber comes back, it plays back those spools.  its designed this
way, its advertised as working this way, we require high performance
dedicated RAID storage on the messaging box for these spools.... time
and time again operations has seen it happily hum along for 6 months
with no disk IO usage, and push it onto 2nd/3rd tier storage to free up
the 1st tier for something else.   then a subscriber goes offline for 14
hours, and the system just croaks and can't keep up.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 9/22/2014 2:04 PM, Lussier, Denis wrote:
> I like Craig's idea to use the jdk7 temp file handling.   I propose to
> this project keep it simple (at least at first) and start with this as
> a jdbc41 only feature.
>
> Many people use pgjdbc for OLTP style transactions and wouldn't ever
> want to spill to disk.   This is why (IMHO) the algorithm that
> determines under which stress
> condition this feature uses (assuming it is enabled) is so critical.
>

this still won't address the issue that the postgresql server itself
ALSO marshals the entire result set into ITS memory before sending it to
the client.   Really, using cursors for large result-sets is the correct
way to go.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

John R Pierce <pierce@hogranch.com> writes:
> this still won't address the issue that the postgresql server itself
> ALSO marshals the entire result set into ITS memory before sending it to
> the client.

If it actually did that, then there would be an issue ... but it never
has, and very likely never will.  The server sends rows on-the-fly as
they're computed.  That is indeed the very reason that client libraries
tend to want to accumulate full resultsets: they're hiding that behavior
from applications, so as to make it look like you get either an error or
a full resultset, not some rows and then an error.

            regards, tom lane

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.
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.
3) For any disk saving I'd
- postpone it as long as possible (e.g. until after connection is used by another statement)
- do as little as possible (may be by saving frames from network as is with little decoding)
- 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.
Anyway one needs to go through everything to issue next command, does not he? The question is if all of this needs to be saved or simply skipped (as soon as result set is closed).

Best regards, Vitalii Tymchyshyn

On 09/23/2014 05:17 AM, John R Pierce wrote:
>>
>
> this still won't address the issue that the postgresql server itself
> ALSO marshals the entire result set into ITS memory before sending it to
> the client.   Really, using cursors for large result-sets is the correct
> way to go.

Er, it what?

It does no such thing.

For some queries it must prepare the entire result before it can send it
- e.g. if there's a top-level sort node that can't be satisfied by an
index-scan of the table in the desired sort order. It'll marshal such
results to disk if they don't fit within working memory, though. (Look
into the tuplestore code for details).

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

On 09/23/2014 09:55 AM, Lussier, Denis wrote:
> Wow...  really glad Tom chimed in on this.    I've been promoting/using
> PG as an enterprise-class database for over a decade and I was
> struggling with the "fact" that the server doesn't iterate thru a cursor
> without bringing it all into memory.

Well, that assertion would've failed the common-sense sanity test anyway.

You can `SELECT * FROM my_100GB_table` on a machine with 1GB of RAM.
Clearly this is impossible if Pg must marshal all the results into RAM
first.

I think John's misapprehension probably stemmed from the fact that libpq
and many other clients *default* to fetching the whole result from the
server into RAM before reporting success to the caller. This makes it
*seem* like the server must marshal the whole result in memory, but it's
the client doing that, not the server.

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

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

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

> 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. It also
makes no sense, as when you execute a new statement, the resultset of
the prior statement is automatically closed.

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.

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.

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

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.

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

I think you might be trying to solve a different problem to what Denis
is talking about.

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

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?

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

On 10/06/2014 10:22 AM, Vitalii Tymchyshyn wrote:
> Hello.
>
> As of routing I meant exactly attaching to original statement result
> belongs to and throwing an error as soon as someone get to correct point
> calling this original statement.
> As of threading I tend to agree with you. It can be revised in Java 8
> where there is a default executor that can be used.
>
> The primary problem as I can see it are OOMs on large result sets.
> Currently the workaround is to use fetchSize, but it works only in very
> limited scenarios.

Specifically, autocommit=off and only one active Statement. Right?

The PostgreSQL backend its self supports multiple open portals, but only
within the same open transaction, and if any statement causes an error
all portals are invalidated. So we can't really use that if you want
multiple *independent* statements.

The usual answer would be WITH HOLD cursors (or a portal-level
equivalent). There's a server-side impact to them, but it's probably the
first thing to try.

Failing that, as you say, we'd have to fetch the whole result set to the
client and store it off-heap, which is where the abstraction I've talked
about on this mailing list thread comes in.

An issue here is that PgJDBC currently has some restrictions around
multiple application threads using a single Connection at the same time.
If you're working with multiple statements you're quite possibly also
working with multiple threads, right?

> I can see two ways here: improve fetchSize handling (potentially setting
> default fetchSize to some value like 10000) or storing result sets out
> of heap.
> One more thing to remember is time to get first row. It would be great
> to have first fast enough  without reading, parsing and storing
> somewhere all the 1000000 rows.

... which is where the issues with threading come in, because you want
to fetch some results, return them to the client, and continue
processing the rest of the results in a helper thread in the driver.

I think we'd have to get the client app involved more directly in that,
by exposing partial fetches more directly to the client and allowing it
to ask us (possibly via a separate thread) to consume more results and
append them to the result set. We'd have to deal with the resulting
locking issues carefully.

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

On 10/05/2014 11:24 AM, Vitalii Tymchyshyn wrote:
> Hello, all
>
> I've made a draft implementation of setFetchSize in autocommit mode
> here: https://github.com/tivv/pgjdbc/tree/autocommit_fetchsize
> Tests look good.
> Note that it does not try to limit Execute size as I don't think I
> understand protocol fully.

I really recommend addressing that part, then.

The protocol is pretty well documented; it doesn't make sense
implementing a new and somewhat complex approach if a simpler one is
possible and might be cleaner.

> The only thing it does is that it does not
> read everything before returning to the caller. Instead it reads
> response rows in fetchSize batches. If another statement tries to use
> the same connection, full result is read immediatelly (this part is not
> tested yet).

I'm still not convinced that reading the result set when the next
statement is run is the right answer.

I'd really like to be able to have the driver return a Runnable or
Future that lets the application assign the reading of the rest of the
result set to a thread, so it can be completed _before_ the app needs to
run another query.

If the current query isn't fully fetched when the next one is run, I'd
prefer to just throw an exception indicating that the previous query
must be fetched. Let the application deal with it. I really don't like
muddling queries together like this.

(That also means exceptions get dispatched in the right place - the
fetch of the prior query).

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

On 10/21/2014 07:06 PM, Dave Cramer wrote:
> Hi Vitalii,
>
> Thanks for this. I am headed for pgconf, hopefully I will have time to
> look at it on the plane.

Great that you're coming.

I'm still concerned by this patch, particularly the fact that it doesn't
properly use partial result set fetching at the protocol level, or
didn't last time I looked.

I really think it should be restricted to v3 protocol support and use
partial EXECUTE.

I'm generally opposed to the idea of the next query incurring the cost
of fetching remaining results from the prior one, and possibly throwing
an unrelated exception. That's IMO just wrong.

I'm not at all convinced we shouldn't be using server-side portals, and
possibly a client-supplied helper thread pool. I really don't like
hijacking a possibly unrelated thread (that's trying to run the next
statement) to do work deferred by a prior connection.

Instead we should probably:

    WARNING: Discarding unfetched results from prior connection
    HINT: To avoid this warning, explicitly close() the result set or
          fully read it before running a new statement.

or even make it an ERROR.

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

On 10/21/2014 10:19 AM, Vitalii Tymchyshyn wrote:
> Hello, all.
>
> Basically, alpha of p.1 is available
> here: https://github.com/tivv/pgjdbc/tree/offloading
> It passes all the tests of cursor-based implementation, but I want to
> add some more (mostly with multiple open statements).

I'm still not sure I fully see the benefits of this, or at least see
them as worth the complexity introduced.

I'm strongly opposed to the idea of finishing fetching when the next
statement is created, then stashing any resulting exception in the prior
statement so it's handled at close time or on the next resultset fetch.

If you don't do that, and instead require that the caller fully fetch
the resultset before starting the next statement (possibly via a helper
thread) then it might be more OK.

Overall though, I'm finding it hard to understand the use case for this.
Why use this instead of multiple portals in an open transaction, or
multiple connections?

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

On 10/30/2014 07:27 AM, Vitalii Tymchyshyn wrote:
> The fetching time is fully defined by a strategy that will be introduced
> after step (2) is complete. Currently it's fully fetched on execute of
> the first query, the main difference is that it's copied into a file and
> not heap.

OK, that's a bit saner, at least if you fetch small results to the heap
then switch to file storage at some threshold.

> As of multiple portals, we are talking about autocommit mode. It's
> mostly about supporting this mode and not forcing user to start a
> transaction or opening another connection. And I am against the idea of
> driver making multiple connections as it will screw up a lot of
> connection pooling scenarios and setups.

What I'm asking is why the *application* doesn't just use multiple
connections or use a transaction with portals.

It's not clear to me why it's worth the complexity in the driver to work
around what seems like problematic application behaviour.

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

Sure, it's fetching fetchSize into heap before switching to a file.
As of application, well, JDBC provides autocommit mode, so there are applications using it.

As of the second connection, I've got a funny deadlock once with the double connection architecture. N threads of application got N connections from a N-limited connection pool and were waiting each for another connection that just was not available.

Best regards, Vitalii Tymchyshyn