Why am I getting this error-msg: Warning in postgresqlQuickSQL

Question

I am using the package pool for a Shiny-app, which establishes a connection to my PostgreSQL-DB. From time to time i am getting the error-msg below without the app running. Also when I run the code locally, the same error appears, and sometimes repeatedly.

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

Is it because pool creates a connection to the DB and after some time the DB (or server) gets disconnected because of a time-out?

Anyway, nowhere in my code I am making an SQL-query which has SELECT 1 inside.

Is the query coming from pool itself (The validateQuery())?
How can I prevent this error?

In my ShinyApp I have also the following code to end the pool connection after the App was closed:

  session$onSessionEnded(function() {
    pool::poolClose(pool)
  })

Furthermore, when running the ShinyApp, the App works fine & behaves as expected. But when I close the App, RStudio often crashes and I have to reopen it and reload the Project.

Why is that?
How can I find out what caused the crash?

Devin Devin · Accepted Answer · 2018-06-01T13:36:29

You don't want to be doing anything with the pool inside server.R other than CRUD stuff. You should only create/destroy the pool inside global.R. In other words, the pool is created once when the app launches and is shared among all users' sessions. It is only closed when it is no longer needed, which is when the app shuts down.

global.R

pool <- dbPool(...)

onStop(function() {
  poolClose(pool)
})

Why am I getting this error-msg: Warning in postgresqlQuickSQL

1 Answers