Keep alive function for PostgreSQL connection in Shiny app?

Question

I have a shiny app that utilizes RPostgreSQL to connect to database & queries data ad-hoc.

# Initialize environment to hold SQL parameters:
library(RPostgreSQL)
if (!exists('.sql')) .sql <- new.env()
.sql$cxn <- dbConnect(PostgreSQL(), host = "localhost", dbname = "testdb", user = "myuser", password = "Passw0rd!", port = 5432)

This code runs when the app initializes, but after some time, the connection is terminated by the server:

> dbGetQuery(.sql$cxn, "SELECT 1")
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not run statement: no connection to the server
)

If I simply call:

.sql$cxn <- dbConnect(PostgreSQL(), host = "localhost", dbname = "testdb", user = "myuser", password = "Passw0rd!", port = 5432)

again, it creates a second connection: (and I do not want that)

> dbListConnections(PostgreSQL())
[[1]]
<PostgreSQLConnection:(10601,4)> 

[[2]]
<PostgreSQLConnection:(10601,5)>

(Eventually, the maximum number of connections is reached and you cannot create another: https://groups.google.com/forum/#!topic/shiny-discuss/0VjQc2a6z3M)

I want to create a function that connects to my PostgreSQL database (if it hasn't been already), and keeps the connection open (by SELECT'ing 1) if it has:

getConnection <- function(.host, .user, .pw) {
  tryCatch({
    if (!exists('cxn', where = .sql)) {
      .sql$cxn <- dbConnect(PostgreSQL(), host = .host, dbname = "testdb", user = .user, password = .pw, port = 5432)
    } else {
      dbGetQuery(.sql$cxn, "SELECT 1")
    }
  }, warning = function(w) {
    NULL  # placeholder for warnings
  }, error = function(e) {
    print(e)
    cat("Looks like PostgreSQL connection died. Let's try to reconnect...\n")
    invisible(lapply(dbListConnections(PostgreSQL()), dbDisconnect))  # Close all db connections
.sql$cxn <<- dbConnect(PostgreSQL(), host = .host, dbname = "testdb", user = .user, password = .pw, port = 5432)
  }, finally = {
    return(.sql$cxn)
  })
}

getConnection(.host = "localhost", .user = "myuser", .pw = "Passw0rd!")

EDIT 2016/03/12: Not sure why, but the function I wrote above doesn't seem to be working properly...

When I call it, I get:

> getConnection(.host = awsrds$host, .user = awsrds$username, .pw = awsrds$password)
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not run statement: no connection to the server
)
<PostgreSQLConnection:(12495,0)>

In particular, this part dbGetQuery(.sql$cxn, "SELECT 1") returns:

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not run statement: no connection to the server
)
NULL
Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
  Could not create executeSELECT 1

And the class of this output is NULL (as opposed to an error?).

Any ideas what I'm doing wrong? Thanks!

Can you just close the connection every time after use? Is keeping connection live necessary for performance reason? — Xiongbing Jin, Mar 01 '16 at 00:04
@warmoverflow Unfortunately, the connection must be left open. An arbitrary # of users are accessing the app at any given moment and may query the database. — Ray, Mar 01 '16 at 00:09
My understanding (which could be wrong) is that each user runs his own Shiny session, so each user will connect/disconnect the database without influencing other users, as long as you code is organized properly (see http://shiny.rstudio.com/articles/scoping.html) — Xiongbing Jin, Mar 01 '16 at 00:20

score -2 · Answer 1 · answered Mar 01 '16 at 15:30

I believe you can solve the error by including a check in your connection function. This is what I did for mysql...should work the same in your case I believe. The key is checking if SELECT 1 == 'try-error'

connectionFunction <- function() {
  if (!exists("connectionName", where = .GlobalEnv)) {
      connectionName <<- dbConnect(MySQL(), default.file = mysqlconf, dbname = "dbName")
  } else if (class(try(dbGetQuery(connectionName, "SELECT 1"))) == "try-error") {
      dbDisconnect(connectionName)
      connectionName <<- dbConnect(MySQL(), default.file = mysqlconf, dbName = "dbName")
  }
  return(connectionName)

}

Keep alive function for PostgreSQL connection in Shiny app?

1 Answers1