Shiny reactive caching (?) issue

shiny
,
1

I have a Shiny app that reads some tables from a database and lets the user edit them. There is anomalous behavior at one point that I'm hoping someone can explain. Apologies for the necessarily long post.

The app has two reactive lists, defined as follows.

  database <- reactiveValues()    # holds the list of tables
  display <- reactiveValues()     # holds the display versions of the tables

Each database table XYZ is read from the database and added to the first list as a dataframe (database$XYZ). A slightly modified version is then added to the second list (display$XYZ). For instance, 0-1 integer columns in the database table are converted to true/false logical values in the display table.

Table XYZ is displayed using renderDT and DTOutput with row selections limited to one row. There is a button to delete the selected row and an event observer to react to a button click. The misadventure occurs inside the event observer.

Every table has an integer primary key field called "UID". The key portion of the event observer is as follows.

                   record <- database$Committee[input$display_committee_rows_selected, ]
                   uid <- record[1, "UID"]
                   dbExecute(con, paste0("DELETE FROM Committee WHERE UID = ", uid))
                   database$Committee <- dbGetQuery(con, "SELECT * FROM Committee")
                   display$Committee <- displayCommittee(database)

The first line gets the selected row, the second extracts the value of the primary key, the third deletes the row in the database, the fourth reloads the database in its entirety and the fifth calls a function that does the display formatting for the revised dataframe.

Let's say that on initial load the dataframe has 17 rows, with keys 42, ..., 45 in the last four rows. The user selects row 14 (key 42) and deletes it. The displayed table updates to 16 rows, with keys 43-45 in rows 14-16, and the database shows the deletion occurred. So far, so good.

Now the user selects either row 15 (key 44) and clicks the delete button again. Inside the event observer, the selected row is correct (15), but the UID, which should be 44, is shown as 43 (which was the UID of rows 15 before the first row deletion). The value of record (the selected row in the table) does show key 43, meaning it's the 15th row of the original table, not the updated table. The record with key 43 is indeed deleted from the database, and the display table updates accordingly.

It gets weirder. If I change the first line of code above to

                   record <- display$Committee[input$display_committee_rows_selected, ]

(i.e., get the record and thus the UID from the display version of the table rather than the database version), the code works correctly ... even though the display version is computed from the database version.

The code is working with that change, but I would really like to understand what is going on.

2

Not sure if this is just a typo, but I just stumbled across this line:
display$Committee <- displayCommittee(database)

shouldn't that be:
display$Committee <- displayCommittee(database$Committee)
(but maybe that's handled in the unknown function)

To me it sounds like either an initialization problem or a reactiveValue is used before the reactive cycle was able to update it - hard to tell without a reproducible example.

This seems to be working fine:

library(shiny)
library(RSQLite)
library(DBI)
library(datasets)
library(DT)

DF <- iris
DF$UID <- seq_len(NROW(DF))

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "Committee", DF)

displayCommittee <- function(DF){cbind(DF[1:4]*2, UID = DF$UID)}

database <- reactiveValues(Committee = DF)    # holds the list of tables
display <- reactiveValues(Committee = displayCommittee(DF))     # holds the display versions of the tables

ui <- fluidPage(
  DTOutput("display_committee"),
  actionButton("delete_row", "delete_row")
)

server <- function(input, output, session) {
  output$display_committee <- renderDT({
    display$Committee
  }, selection = "single")
  observeEvent(input$delete_row, {
    if(is.null(input$display_committee_rows_selected)){
      showNotification("Please select the row to be deleted.")
    } else {
      record <- database$Committee[input$display_committee_rows_selected, ]
      uid <- record$UID
      dbExecute(con, paste0("DELETE FROM Committee WHERE UID = ", uid, ";"))
      database$Committee <- dbGetQuery(con, "SELECT * FROM Committee;")
      display$Committee <- displayCommittee(database$Committee) 
    }
  })
}

shinyApp(ui, server,
         onStart = function() {
           cat("Doing application setup\n")
           onStop(function() {
             cat("Doing application cleanup\n")
             dbDisconnect(con)
           })
         })
3

Thanks for the response. The function call is actually correct. I pass the entire list of tables to the function that renders display versions because rendering tables containing foreign keys requires access to the sources of those keys.

I agree that it sounds like a timing issue, but record (which does not update properly) is a local variable inside a function and not a reactive value. It comes from a reactive variable (database$Committee), but I have verified (by printing the table right after the assignment to record) that database$Committee is up to date ... and yet record is a line from the previous version of the table.

4

Are you able to condense this into a reproducible example?

5

Not in the sense of something that could be posted here. For one thing, it uses RMariaDB and requires a database (and database server). If Posit decides it's something worth chasing down, I could probably bundle up the code and a sample database (assuming the issue repeated on the sample DB), but I would need to invest a nontrivial amount of time coming up with the fake data. I'm not sure it's worth their (or my) effort.

6

In my experience, such problems very rarely have anything to do with the DBMS used and without a corresponding example it is indeed pointless to deal with the matter further.

1 Like