I have an app on the server that retrieves all needed data from the database at the beginning of its work.
It works once, twice, but if too many people open the app simultaneously or refresh too fast, too many connections are being made and it "freezes" and never actually load the data properly anymore.
In logs I have found a .log file that once stated: "Error: C stack usage is too close to limit" but there is no possibility of recursion of anything as the app looks something like this:
pacman::p_load(shiny, RPostgres, pool, future, promises)
plan(multiprocess)
pool <- dbPool(Postgres(),
dbname = ...)
database_table <- dbGetQuery(pool, "select * from table")
ui <- fluidPage(tableOutput('tab'))
server <- function(input, output, session) {
output$tab <- renderTable({
database_table
})
}
shinyApp(ui, server)
Changing the code to asynchronous does not fix anything. It still freezes and I have to wait some time to make it work after a refresh.
database_table <- future({
pool <- dbPool(Postgres(),
dbname = ...)
dbGetQuery(pool, "select * from table")
})
Closing a pool object does not fix it neither.
onStop(function() {
poolClose(pool)
})
Then, what is the best way to have a connection to the database in the Shiny app?
Is it even a good practice to load everything at the beginning or each time after someone actually clicks something that demands database data?