Think about asynchronous operations

[krlmlr]

• Long-running queries should not block R
  • Test with repeated cross-join of table with two rows: select count(*) from a cross join a cross join a ...
• Blocking vs. non-blocking dbFetch() and/or dbGetQuery()
  • Support timeout
• Waiting (default implementation: busy waiting with Sys.sleep())
• Query cancellation (now in Query cancellation #63)
• One active result set per connection
  • Support easy way to clone a connection (Default connection #57)

Important for "big data" backends.

[hadley]

Also need to make sure everything is cleaned up correctly if there is an interrupt. This is quite hard.

[krlmlr]

To me, it looks like we need to add the following methods to DBI:

• dbWait(DBIResult)
  • Can wait for completion, with an optional timeout
• dbFetchAsync(DBIResult)
  • Can fetch as many rows as there are available, or with a timeout, without blocking

dbSendQuery() and dbSendUpdate() (#20) can be non-blocking by default at the backend's choice, only dbFetch() and dbWait() and perhaps dbGetRowsAffected() are blocking. Query cancellation would be done by dbClearResult().

I think this is backwards-compatible to the current specification.

[krlmlr]

On second thought, it's safer to be explicit and define dbSendQueryAsync() and dbSendUpdateAsync(). The default DBI implementation of dbSendQuery() could be:

res <- dbSendQueryAsync(con, sql)
dbWait(res)
res

Similar for dbSendUpdate().

[happyshows]

@krlmlr any update on the asynchronous methods?

[krlmlr]

I think in practice it's important to ensure at least that dbFetch() and dbGetRowsAffected() are safely interruptible. This means to me:

• Database driver functions that may block should be called repeatedly with a timeout, interspersed with Rcpp::checkInterrupt() calls.
• Interrupting a call to dbFetch() and then calling dbFetch() again should return the same results as a single call to dbFetch(). Once data collection has started, the data should be part of the result object until returned.

Once this is in place, adding a timeout means checking elapsed time with each checkInterrupt() call, or perhaps scheduling a signal with timer_create().

[krlmlr]

This issue is about safe interruption of fetching, once the database has started delivering data. r-dbi/DBI#290 is about query cancellation, which looks like an entirely different problem.

[krlmlr]

Let's flesh this out in RPostgres.