st_read/st_write returns error with pooled DB connection (dbPool)

[matthewpaulking]

I have a Shiny app which accesses a Postgis database with dbPool from the pool package. I used to use st_read_db with the pooled connection with no issues. Since upgrading to sf_0.6-3, I get an error when using my pooled database connection. Example below:

library(RPostgreSQL)
library(pool)
library(sf)

nc <- st_read(
  system.file("shape/nc.shp", package = "sf"),
  stringsAsFactors = FALSE,
  quiet = TRUE
)

# dbConnect works fine

my_db <- dbConnect(
  drv = dbDriver('PostgreSQL'),
  user = 'postgres',
  dbname = 'postgis',
  host = 'localhost',
  password = 'admin'
)

st_write(obj = nc, dsn = my_db, layer = "nc", overwrite = TRUE)
st_read(dsn = my_db, layer = "nc")
# works as expected

dbDisconnect(my_db)


# dbPool gives error

my_db <- dbPool(
  drv = dbDriver('PostgreSQL'),
  user = 'postgres',
  dbname = 'postgis',
  host = 'localhost',
  password = 'admin'
)

st_read(dsn = my_db, layer = "nc")
#Error in CPL_read_ogr(dsn, layer, as.character(options), quiet, type,  : 
# Not compatible with STRSXP: [type=environment].

st_write(obj = nc, dsn = my_db, layer = "nc2")
#Error: is.character(dsn) is not TRUE

poolClose(my_db)

The traceback() for st_read error

> traceback()
5: stop(list(message = "Not compatible with STRSXP: [type=environment].", 
       call = CPL_read_ogr(dsn, layer, as.character(options), quiet, 
           type, promote_to_multi, int64_as_string), cppstack = NULL))
4: .Call("_sf_CPL_read_ogr", PACKAGE = "sf", datasource, layer, 
       options, quiet, toTypeUser, promote_to_multi, int64_as_string)
3: CPL_read_ogr(dsn, layer, as.character(options), quiet, type, 
       promote_to_multi, int64_as_string)
2: st_read.default(dsn = my_db, layer = "nc")
1: st_read(dsn = my_db, layer = "nc")

My sessionInfo()

> sessionInfo()
R version 3.4.2 (2017-09-28)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252    LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C                           LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] sf_0.6-3          pool_0.1.4        RPostgreSQL_0.6-2 DBI_0.8          

loaded via a namespace (and not attached):
 [1] Rcpp_0.12.16     bindr_0.1        magrittr_1.5     units_0.5-1      R6_2.2.2         rlang_0.2.0.9001 udunits2_0.13    dplyr_0.7.4     
 [9] tools_3.4.2      grid_3.4.2       e1071_1.6-8      dbplyr_1.1.0     class_7.3-14     yaml_2.1.14      assertthat_0.2.0 tibble_1.4.2    
[17] bindrcpp_0.2     spData_0.2.8.3   later_0.5        glue_1.2.0       compiler_3.4.2   pillar_1.2.2     classInt_0.2-3   pkgconfig_2.0.1 

I'm not really sure what the error means (something with rcpp ?)
Is there a different way I should be handling pooled objects in st_read and st_write?

[etiennebr]

Thanks for the feedback. I'm interested in seeing the error from dbPool as well (maybe use reprex::reprex()). I'm new to the pool package, is there a difference between poolClose(my_db) and dbDisconnect(my_db)?
From what I could read, there is no obvious reason it would fail with sf. Is it possible that the error raised when re-connecting would indicate something?

[edzer]

> class(my_db)
[1] "Pool" "R6"  

Looks like we need an st_read method for Pool objects. And have st_read.default raise an error, rather than expect character (and thus have st_read.character).

Can the Pool object be coerced to a DBIObject or PostgreSQLConnection?

[matthewpaulking]

Thanks @etiennebr and @edzer for your reply!

Yes, it does look like Pool objects can be coerced to PostgreSQLConnection with poolCheckout().

Continuing my previous example, this now will work:

my_db_checkout <- poolCheckout(my_db)

class(my_db_checkout)
#[1] "PostgreSQLConnection"
#attr(,"package")
#[1] "RPostgreSQL"

# Works
st_read(dsn = my_db_checkout, layer = "nc")
st_write(obj = nc, dsn = my_db_checkout, layer = "nc2")
poolReturn(my_db_checkout)

[edzer]

We should now be able to read and write Pool objects; pts test.