RPostgreSQL Cannot Close Connections

Question

I have a shiny app that connects to a database using RPostgreSQL. At the end of the app the connection is closed and the driver should be unloaded but I get an error, warning me that the connection is not closed.

The code looks something like this:

 # in the app.R file, but not in the server function:
 drv <- dbDriver("PostgreSQL")
 con <- dbConnect(drv, dbname = "database1",
                host = "localhost", port = 5432,
                user = "user", password = "pw")

# in the server function:
foo <- dbGetQuery(con, "SELECT * from table1")

# at the end of the server function to disconnect when the app is closed:
session$onSessionEnded(function(){
    dbDisconnect(con)
    dbUnloadDriver(drv)
})

However, I get the error message: Error in postgresqlCloseDriver(drv, ...): RS-DBI driver: (There are opened connections -- close them first) this is displayed with the command dbUnloadDriver(drv).

When I manually look for open connections with dbListConnections() I get a list with up to 16 open connections to the database. Notice, I only use dbGetQuery never dbSendQuery to avoid having to close connections.

Any ideas?

Answer 1

Structure your code like this:

function()
{
  con <- dbConnect("PostgreSQL") # + other params
  on.exit(dbDisconnect(con))

  dbGetQuery("SELECT * FROM wherever") # or whatever you want to do
}

By using on.exit, the connection is guaranteed to be closed, whether or not an error occurred.

See also How and when should I use on.exit?

---

If you want, you can unload the driver using:

on.exit(dbUnloadDriver(drv), add = TRUE)

I suspect this may provide worse performance though, since you'll be unloading and reloading the driver each time you connect to the database. Test this under your usage conditions if you are worried about this.