Shiny pool package

shiny
1

I used pool package like the following in a shiny app to access oracle database. The queries are running great. However, the database sessions created from the pool doesn't die, even. I didn't know this until oracle dba informed me of excessive number of inactive sessions from my application.

  # this is in global.R pool_0.1.4.1 
  pool <- dbPool(
    drv = dbDriver("Oracle"),
    dbname = connect.string,
    username = "username",
    password = get_secret(name = db$secretName, key = privateKey, vault = vault)$password,
    minSize = 2,
    maxSize = Inf,    # this could have been omitted since it's the default
    idleTimeout = 300000 # 5min
  )
  
onStop(function() {
  poolClose(pool)
})

My shiny app is setup in a way that is is terminated after about 20 minutes of inactive. As you can see, I have two connections but they exist even if I log out of shiny app (we use shiny pro server).
Has anyone experienced similar situation? I wonder at what point the pool is supposed to be closed and how I can ensure no inactive sessions linger around.

1 Like
2

Interesting and unfortunate! Are you persisting logs on your Shiny Server Pro? You could always add a log message / print statement above poolClose to be sure that onStop is firing as expected? The poolClose should take care of things, I would think. If that onStop method is not being fired when the app tears down, then that would explain why the connections remain, and I think the log message would (should) show you that. You also might test running the app locally to be sure the onStop is firing there, as well.

3

@jian_wang That is quite surprising to me for a number of reasons.

  1. I would expect the poolClose to be called there.
  2. I would expect Oracle DB to notice if the socket connection is severed, and to close the session. We experimented with, I believe, MySQL and Postgres and they either closed right away or after a timeout (I can't remember which).

You might also consider setting minSize = 0, this will allow the pool to shrink to zero even before the application exits. It just means that you may initiate a new connection once in a while--no more frequently than once every 5 minutes.

1 Like