CODEWITHSUNDEEP
rrpostgresql
David
David

Reputation: 10152

RPostgreSQL Cannot Close Connections

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?

Upvotes: 8

Views: 14089

Answers (2)

Christoph Terwitte
Christoph Terwitte

Reputation: 101

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").

Upvotes: 10

Richie Cotton
Richie Cotton

Reputation: 121067

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.

Upvotes: 18

Related Questions