How to use SQL pool ?

[Patanouque]

Hello, I started using async programming on an app that does long SQL queries. I'm using the pool package but I can't make it work It returns : 'Error: expired manager' and gives me a leaked pool object

Here is a minimal exemple for the app :

library(dplyr)
library(shiny)
library(highcharter)
library(shinycssloaders)
library(future)
plan(multiprocess)
library(promises)

pool <- dbPool(
  drv = dbDriver('PostgreSQL'),
  dbname = "mydb",
  host = "localhost", 
  user = "user",
  password = "pswd"
)

ui = fluidPage(
         actionButton("run_calcl", "Query database"),
         uiOutput("plot")%>%withSpinner()
)

server = function(input, output){
  querydb = eventReactive(input$run_calcl, {
    future({
      Sys.sleep(3)

      # working
      #plot =  mtcars %>% filter(cyl > 4) %>% head() %>% hchart(type = "line", hcaes(x=drat, y = disp)) 

      # not working
      plot = pool %>% tbl("my_tab") %>% as_tibble() %>% hchart(type = "scatter", hcaes(x= xaxis, y = yaxis))
    })

  })

  output$plot = renderUI({
    querydb() %...>% div()
  })
}
shinyApp(ui, server)

The SQL query works if not in a promise

Is async programming supposed to work with SQL or am I doing something wrong ?

Thanks !

[Patanouque]

I found an answer : https://cran.r-project.org/web/packages/future/vignettes/future-2-issues.html about "Non-exportable objects". It says that connextions cannot be passed along to another R process.

To overcome this limitation you can define the connection inside the future statement

[YsoSirius]

I am not able to make that work, although defining everything inside the future block. I even had to inlcude the RPostgreSQLpackage, otherwise I would get that error:

Unhandled promise error: Couldn't find driver PostgreSQL. Looked in:
* global namespace
* in package called PostgreSQL
* in package called RPostgreSQL

That example is not working for me and not giving any errors.

library(shiny)
library(plotly)
library(RPostgreSQL)
library(pool)
library(promises)
library(future)
plan(multiprocess)

ui <- fluidPage(
  plotlyOutput("plot")
)

onStop(function() {
  poolClose(pool)
})

server <- function(input, output, session) {
  output$plot <- renderPlotly({
    future({
      library(RPostgreSQL)
      # library(pool)
      drv <- dbDriver("PostgreSQL")
      pool <- dbPool(drv = drv, dbname = "postgres", host = "localhost", user = "postgres", password = "***")
      dbGetQuery(pool, "select * from table;") %>% 
        select(name, x)
    }) %...>% {
      d <- ggplot(., aes(as.factor(name), x)) +
        geom_point()
      # ggplotly(d)
      d
    }
  })
}

shinyApp(ui, server)

@Patanouque The link moved to https://cran.r-project.org/web/packages/future/vignettes/future-4-issues.html

[r2evans]

Untested: https://github.com/rstudio/pool/issues/83

[Tazovsky]

@YsoSirius During my struggle with future and pool I found out that without dplyr::collect future will fail, because it loses connection with pool

future::plan(multisession)

db_res <- future::future({
  connection <- pool::dbPool(
    maxSize = Inf,
    minSize = 0,
    validationInterval = 15,
    RPostgres::Postgres(),
    host = host,
    user = user,
    password = password,
    dbname = dbname,
    port = port
  )

# does not work:
  get_data_from_db(connection)

# works:
  get_data_from_db(connection) %>% 
    dplyr::collect()

value(db_res)
}