dbIsValid does not work as expected

It seems like the check being used to decide if a connection is closed does not work as expected. Here you can see that the dbIsValid function returns TRUE even after the connection is closed.

Also trying to use a connection after it is closed seems to crash R on my computer.

# install.packages("DatabaseConnector")

library(DatabaseConnector)
connectionDetails <- createConnectionDetails(dbms = "postgresql",
                                             server = Sys.getenv("CDM5_POSTGRESQL_SERVER"),
                                             user = Sys.getenv("CDM5_POSTGRESQL_USER"),
                                             password = Sys.getenv("CDM5_POSTGRESQL_PASSWORD"))

con <- connect(connectionDetails)
#> Connecting using PostgreSQL driver
getTableNames(con,  Sys.getenv("CDM5_POSTGRESQL_CDM_SCHEMA"))
#>  [1] "attribute_definition"  "care_site"             "cdm_source"           
#>  [4] "cohort"                "cohort_attribute"      "cohort_definition"    
#>  [7] "concept"               "concept_ancestor"      "concept_class"        
#> [10] "concept_relationship"  "concept_synonym"       "condition_era"        
#> [13] "condition_occurrence"  "cost"                  "death"                
#> [16] "device_exposure"       "domain"                "dose_era"             
#> [19] "drug_era"              "drug_exposure"         "drug_strength"        
#> [22] "fact_relationship"     "location"              "measurement"          
#> [25] "metadata"              "note"                  "note_nlp"             
#> [28] "observation"           "observation_period"    "payer_plan_period"    
#> [31] "person"                "procedure_occurrence"  "provider"             
#> [34] "relationship"          "source_to_concept_map" "specimen"             
#> [37] "visit_detail"          "visit_occurrence"      "vocabulary"

DBI::dbIsValid(con)
#> [1] TRUE

rJava::is.jnull(con@jConnection)
#> [1] FALSE

disconnect(con)


DBI::dbIsValid(con)
#> [1] TRUE

rJava::is.jnull(con@jConnection)
#> [1] FALSE

sessionInfo()
#> R version 4.2.2 (2022-10-31)
#> Platform: x86_64-apple-darwin17.0 (64-bit)
#> Running under: macOS Big Sur ... 10.16
#> 
#> Matrix products: default
#> BLAS:   /Library/Frameworks/R.framework/Versions/4.2/Resources/lib/libRblas.0.dylib
#> LAPACK: /Library/Frameworks/R.framework/Versions/4.2/Resources/lib/libRlapack.dylib
#> 
#> locale:
#> [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#> [1] DatabaseConnector_6.2.4
#> 
#> loaded via a namespace (and not attached):
#>  [1] rstudioapi_0.15.0 knitr_1.43        magrittr_2.0.3    bit_4.0.5        
#>  [5] R.cache_0.16.0    rlang_1.1.1       fastmap_1.1.1     styler_1.10.1    
#>  [9] tools_4.2.2       checkmate_2.2.0   xfun_0.39         R.oo_1.25.0      
#> [13] cli_3.6.1         DBI_1.1.3         withr_2.5.0       htmltools_0.5.5  
#> [17] yaml_2.3.7        bit64_4.0.5       digest_0.6.33     lifecycle_1.0.3  
#> [21] rJava_1.0-6       purrr_1.0.2       vctrs_0.6.3       R.utils_2.12.2   
#> [25] fs_1.6.3          glue_1.6.2        evaluate_0.21     rmarkdown_2.23   
#> [29] reprex_2.0.2      compiler_4.2.2    backports_1.4.1   R.methodsS3_1.8.2

^{Created on 2023-09-10 with reprex v2.0.2}

Start by reproducing the PostgreSQL example with DBI::dbIsValid(con) before and after disconnect(con), then check behavior when getTableNames is called on the closed connection. Trace the disconnect and validity checks for the connection object. Done means dbIsValid reports the closed state correctly and using the closed connection does not crash R.