Increase connection attempt with DBI

user_16242335 0
Viewed 75

I'm trying to build a Shiny application which will be connected to an unstable PostgreSQL Database with DBI. It works fine when the database is stable, but gives Whitelabel Error whenever the database is slow or down for a moment. How can I configure my application so that it will keep trying to connect a number of times before throwing error? A sample code is given below:

library(shiny)
library(DBI)

con <- dbConnect(
  RPostgres::Postgres(),
  host = "myip",
  port = "myport",
  dbname = "mydb",
  user = "user",
  password = "password"
)

ui <- fluidPage(
  tableOutput("mytable")
)

server <- function(input, output, session){
  output$mytable <- renderTable(
    dbGetQuery(
      con,
      "select * from mytable;"
    )
  )
}

shinyApp(ui, server)
dbi shiny r
1 Answers

We can try purrr::insistently() with rate_backoff as suggested by @r2evans, to retry the query waiting longer between each time up to a maximum of 4 in this case.

library(shiny)
library(DBI)
library(purrr)

con <- dbConnect(
  RPostgres::Postgres(),
  host = "myip",
  port = "myport",
  dbname = "mydb",
  user = "user",
  password = "password"
)

my_rate <- rate_backoff(
  pause_base = 1,
  pause_cap = 60,
  pause_min = 1,
  max_times = 4,
  jitter = FALSE
)


ui <- fluidPage(
  tableOutput("mytable")
)

server <- function(input, output, session) {
  output$mytable <- renderTable(
    insistently(~
    dbGetQuery(
      con,
      "select * from mytable;"
    ), rate = my_rate, quiet = FALSE)()
  )
}

shinyApp(ui, server)
user_111687660
Related