dbPool reconnect

reconnect.R

library(RPostgreSQL)
library(pool)
library(dplyr)
library(dbplyr)

pool <- dbPool(
  drv = PostgreSQL(),
  host = Sys.getenv("DB_HOST"),
  port = 5432,
  dbname = Sys.getenv("DB_NAME"),
  user = Sys.getenv("DB_USER"),
  password = Sys.getenv("DB_PASSWORD")
)

safe_pool <- function() {
  tryCatch({
    # try a fast query
    dbExecute(pool, "SELECT 1")
  }, error = function(e) {
    if (e$message == "RS-DBI driver: (could not run statement: no connection to the server\n)") {
      # create a new pool in enclosing environment
      poolClose(pool)

      pool <<- dbPool(
        drv = PostgreSQL(),
        host = Sys.getenv("DB_HOST"),
        port = 5432,
        dbname = Sys.getenv("DB_NAME"),
        user = Sys.getenv("DB_USER"),
        password = Sys.getenv("DB_PASSWORD")
      )
    } else {
      # there was an unknown error
      stop(e)
    }
  })

  pool
}

# try some queries
tbl(pool, "people") # works
tbl(safe_pool(), "people") # works

# now force a disconnect
conn <- dbConnect(
  PostgreSQL(),
  host = Sys.getenv("DB_HOST"),
  port = 5432,
  dbname = "postgres",
  user = Sys.getenv("DB_USER"),
  password = Sys.getenv("DB_PASSWORD")
)

disconnect_sql <- build_sql(
  "SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname = ",
  Sys.getenv("DB_NAME")
)
dbExecute(conn, disconnect_sql)
dbDisconnect(conn)

# try the queries again
tbl(pool, "people") # "Error in postgresqlExecStatement" as expected
tbl(safe_pool(), "people") # works