Duckdb: Way to connect multiple R sessions to the same duckdb file ?

[GuillaumePressiat]

Hi,

Many thanks for this work. It's really promising.

Is there a way to connect multiples R sessions to the same duckdb ?
I have seen issues on this relating to the db itself, in readmode only, but not accessing from R specifically. Am I missing something ?

duckdb::src_duckdb("~/Documents/data/duckdb/testing_the_duck.duckdb", create = FALSE)

Error in duckdb_connection(drv, dbdir = dbdir, debug = debug) : 
  duckdb_startup_R: Failed to open database

It would be very nice to have speed of MonetDbLite queries and yet the possibility of multiple connections too, best of both worlds.

Thanks,
Guillaume

[hannesmuehleisen]

This is clearly on the list of features that we plan to support in the future. But this is not supported at the moment.

[hannesmuehleisen]

We now have a first iteration of multiple connections to the same DuckDB database in the rreadonly branch: (CC @cboettig)

Using the read_only parameter to either driver or connection, we can start DuckDB in read-only mode on a particular database file. This can be used from multiple R sessions at the same time. Here is an example:

````R
library("DBI")

same DB dir for everyone

dbdir <- tempfile()

create a db and write some tables

create a connection with read_only=FALSE (the default)

con <- dbConnect(duckdb::duckdb(), dbdir)
res <- dbWriteTable(con, "iris", iris)

this is important, shut down read-write instance

dbDisconnect(con, shutdown=TRUE)

connect to this db again, but this time with read_only

con1 <- dbConnect(duckdb::duckdb(), dbdir, read_only=TRUE)

make another, separate read_only connection to the same folder

con2 <- dbConnect(duckdb::duckdb(), dbdir, read_only=TRUE)

res <- dbReadTable(con1, "iris")
res <- dbReadTable(con2, "iris")

do the same in a separate process

callr::r(function(dbdir) {
library("DBI")
con <- dbConnect(duckdb::duckdb(), dbdir, read_only=TRUE)
res <- dbReadTable(con, "iris")
dbDisconnect(con, shutdown=TRUE)
TRUE
}, args = list(dbdir))

dbDisconnect(con1)
dbDisconnect(con2, shutdown=TRUE)

what if we want to make multiple read-write connections within the same process?

use the driver, luke!

drv <- duckdb::duckdb(dbdir, read_only=FALSE)
con1 <- dbConnect(drv)
con2 <- dbConnect(drv)

res <- dbWriteTable(con1, "iris1", iris)
res <- dbWriteTable(con2, "iris2", iris)

duckdb::duckdb_shutdown(drv) # also kills all connections
````

@Mytherin there it is, one of the reasons for the whole thing :)

[hannesmuehleisen]

This is by the way the issue that we could never address in https://github.com/MonetDB/MonetDBLite-R/issues/14

[cboettig]

excellent, works perfectly on my end (Linux with gcc)

[GuillaumePressiat]

Really nice first iteration !

These lines of code works perfectly on my mac too.

I knew this was a reason for the whole thing (like you said "This is clearly on the list of features...") but now it's far more evident!

I've switched callr step's by this one to test ten calls from ten separate sessions simultaneoulsy:

library(duckdb)
library(future)
library(future.apply)
plan(multisession)
# Test with ten sessions / readers almost simultaneoulsy
future_duckdb <- future_lapply(1:10,
                               function(i) {
                                 library("DBI")
                                 con <- dbConnect(duckdb::duckdb(), dbdir, read_only=TRUE) 
                                 res <- dbReadTable(con, "iris")
                                 Sys.sleep(5)
                                 dbDisconnect(con, shutdown=TRUE)
                                 res$time <- Sys.time()
                                 res
                               })

And it works too.

Thank you

[hannesmuehleisen]

One caveat that I'd like to note: We use fcntl() and its windows equivalent to lock the database file and enforce the behaviour described above. These locks are known not to be reliable on remote NFS/SMB file shares. This means you could be able to create multiple read-write connections to the same database file in these circumstances. This will lead to data corruption. See also SQLite's discussion on the topic. So please, be extra careful with remote files.