SQL Server fails to reuse temporary tables across sessions

[krlmlr]

Issue Description and Expected Result

On SQL Server, dbWriteTable() fails to write a temporary table with the same name from two sessions. The cause is that dbExistsTable() shows temporary tables from other sessions. Fixing dbExistsTable() also fixes this problem.

On a side note, dbCreateTable() needs dbExecute(immediate = TRUE) .

Database

SQL Server

Reproducible Example

r_1 <- callr::r_session$new()
r_2 <- callr::r_session$new()

r_1$run_with_output(function() {
  .GlobalEnv$conn <- DBI::dbConnect(
    odbc::odbc(),
    driver = "ODBC Driver 18 for SQL Server",
    server = Sys.getenv("DM_TEST_DOCKER_HOST"),
    database = "test",
    uid = "SA",
    pwd = "YourStrong!Passw0rd",
    port = 1433,
    TrustServerCertificate = "yes"
  )
  print(.GlobalEnv$conn)
  NULL
})$stdout |> writeLines()
#> <OdbcConnection> dbo@df80b6ed0a1a
#>   Database: test
#>   Microsoft SQL Server Version: 16.00.1000

r_2$run_with_output(function() {
  .GlobalEnv$conn <- DBI::dbConnect(
    odbc::odbc(),
    driver = "ODBC Driver 18 for SQL Server",
    server = Sys.getenv("DM_TEST_DOCKER_HOST"),
    database = "test",
    uid = "SA",
    pwd = "YourStrong!Passw0rd",
    port = 1433,
    TrustServerCertificate = "yes"
  )
  print(.GlobalEnv$conn)
  NULL
})$stdout |> writeLines()
#> <OdbcConnection> dbo@df80b6ed0a1a
#>   Database: test
#>   Microsoft SQL Server Version: 16.00.1000

# Baseline
r_1$run(function() {
  DBI::dbExistsTable(.GlobalEnv$conn, "#test")
})
#> [1] FALSE

r_2$run(function() {
  DBI::dbExistsTable(.GlobalEnv$conn, "#test")
})
#> [1] FALSE

# Create table in first session
r_1$run(function() {
  DBI::dbWriteTable(.GlobalEnv$conn, "#test", data.frame(a = 1))
})
#> [1] TRUE

# Create table in second session fails
r_2$run_with_output(function() {
  DBI::dbWriteTable(.GlobalEnv$conn, "#test", data.frame(a = 2))
})$error
#> <callr_error/rlib_error_3_0/rlib_error/error>
#> Error: 
#> ! in callr subprocess.
#> Caused by error: 
#> ! Table #test exists in database, and both overwrite and append are FALSE
#> ---
#> Subprocess backtrace:
#> 1. DBI::dbWriteTable(.GlobalEnv$conn, "#test", data.frame(a = 2))
#> 2. DBI::dbWriteTable(.GlobalEnv$conn, "#test", data.frame(a = 2)) at 13-dbWriteTable.R:49:9
#> 3. local .local(conn, name, value, ...)
#> 4. base::stop("Table ", toString(name), " exists in database, and both overwrite an…
#> 5. | base::.handleSimpleError(function (e) …
#> 6. global h(simpleError(msg, call))

# Cause of failure (but not the core reason)
r_2$run(function() {
  DBI::dbExistsTable(.GlobalEnv$conn, "#test")
})
#> [1] TRUE

# Succeeds but no effect (need immediate = TRUE)
r_2$run(function() {
  DBI::dbCreateTable(.GlobalEnv$conn, "#test", data.frame(a = 2))
  DBI::dbCreateTable(.GlobalEnv$conn, "#test", data.frame(a = 2))
})
#> [1] TRUE

# Succeeds, finally
r_2$run(function() {
  DBI::dbExecute(.GlobalEnv$conn, "CREATE TABLE #test (a NUMERIC)", immediate = TRUE)
})
#> [1] 0

r_2$run(function() {
  DBI::dbAppendTable(.GlobalEnv$conn, "#test", data.frame(a = 2))
})
#> [1] NA

r_1$run(function() {
  DBI::dbReadTable(.GlobalEnv$conn, "#test")
})
#>   a
#> 1 1

r_2$run(function() {
  DBI::dbReadTable(.GlobalEnv$conn, "#test")
})
#>   a
#> 1 2

^{Created on 2024-01-08 with reprex v2.0.2}

[meztez]

We encountered the same issue today. @krlmlr before I submit a fix using the session id at the end of the pattern, were you working on a fix on your side?

Thanks

[krlmlr]

Not working on a fix, happy to review a PR.

[meztez]

@krlmlr beside "the fix works on my pc", let me know if this could be a possible solution.

[hadley]

Fixed by #724