7

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?

David
  • 9,216
  • 4
  • 45
  • 78

2 Answers2

17

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.

Community
  • 1
  • 1
Richie Cotton
  • 118,240
  • 47
  • 247
  • 360
  • Do I have to unload the driver? – David Oct 13 '15 at 09:52
  • I simply do it like this: library(RODBC) dbconnection <- odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=RSHUELL00193\\SQLEXPRESS; Database=TestDB;Uid=; Pwd=; trusted_connection=yes") initdata <- sqlQuery(dbconnection,paste("select * from MyTable;")) odbcClose(channel) I have no problems with that at all. – ASH Oct 14 '15 at 15:58
10

If you ran con <- dbConnect("PostgreSQL") more than once, you have two open connections, but con was overwritten and now only references the second one. quick remedy: close all open PostgreSQL connections, no matter their name:
lapply(dbListConnections(drv = dbDriver("PostgreSQL")), function(x) {dbDisconnect(conn = x)})
This runs the function dbDisconnect() on the list you get by checking all open connections with the driver dbDriver("PostgreSQL").

Christoph Terwitte
  • 101
  • 1
  • 4
  • 1
    This also handles the case where a connection was opened and the script stopped because of an error and was then run again, opening another connection. This should be the accepted answer imo. – MGP Oct 07 '20 at 07:32
  • thanks for pointing out this additional benefit! – Christoph Terwitte Mar 22 '22 at 08:52