Windows环境下重复调用含DBI::dbSendQuery()的DuckDB连接函数报错求助

问题描述 #

在Windows系统中两次调用下方函数会触发文件已打开的错误，但Ubuntu系统无此问题，已在两类系统各两台机器上验证。问题根源与DBI::dbSendQuery()相关——注释掉该语句后，函数可重复执行无报错。四台机器均安装了duckdb v1.0.0和DBI v1.2.3，无论参数在duckdb::duckdb()调用内部还是外部传入，都会出现类似错误。

目前仅有的临时解决方案是全程保持连接开启，若要再次访问同一DuckDB文件需重启R（如RStudio中按Ctrl+Shift+F10），求其他可行解决办法。

复现代码 #

path_db <- "~/so-question.duckdb"
f <- function() {
  sql_1 <-
    "
      DROP TABLE if exists t1;
      CREATE TABLE t1 (
        id   int  primary key,
        s    text not null,
      );
    "
  sql_2 <- "SHOW ALL TABLES;"
  cn <- DBI::dbConnect(duckdb::duckdb(), dbdir = path_db, bigint = "integer64")
  # 替代连接方式
  # cn <- DBI::dbConnect(duckdb::duckdb(dbdir = path_db, bigint = "integer64"))
  
  ds_1 <- DBI::dbSendQuery(cn, sql_1)
  ds_2 <- DBI::dbGetQuery(cn, sql_2)
  DBI::dbDisconnect(cn, shutdown = TRUE)
  rm(cn)
  
  ds_2
}

# 第一次执行正常输出
f()
#      database schema name column_names     column_types temporary
# 1 so-question   main   t1        id, s INTEGER, VARCHAR     FALSE

# 第二次执行在Windows下报错
f()

错误信息 #

常规连接方式报错 #

Error: rapi_startup: Failed to open database: {"exception_type":"IO","exception_message":"File is already open in \nC:\\Program Files\\RStudio\\resources\\app\\bin\\rsession-utf8.exe (PID 14948)"}

替代连接方式报错 #

Error in h(simpleError(msg, call)) : 
  error in evaluating the argument 'drv' in selecting a method for function 'dbConnect': rapi_startup: Failed to open database: {"exception_type":"IO","exception_message":"File is already open in \nC:\\Program Files\\RStudio\\resources\\app\\bin\\rsession-utf8.exe (PID 12140)"}

可行解决办法 #

1. 手动释放dbSendQuery的结果资源 #

使用dbSendQuery执行DDL语句后，必须调用dbClearResult释放结果集资源，否则Windows的文件系统会保持文件锁定，导致再次连接失败。修改代码如下：

ds_1 <- DBI::dbSendQuery(cn, sql_1)
DBI::dbClearResult(ds_1) # 新增该行，释放资源

2. 用dbExecute替代dbSendQuery执行DDL语句 #

对于DROP TABLE、CREATE TABLE这类无需返回结果的DDL操作，推荐使用dbExecute，它会自动处理结果集的清理，无需手动调用dbClearResult：

# 替换原有的dbSendQuery代码
DBI::dbExecute(cn, sql_1)

3. 断开连接后手动触发垃圾回收 #

在dbDisconnect和rm(cn)后，手动调用gc()触发垃圾回收，确保R彻底释放与数据库连接相关的资源：

DBI::dbDisconnect(cn, shutdown = TRUE)
rm(cn)
gc() # 新增垃圾回收

修改后的完整可运行代码 #

path_db <- "~/so-question.duckdb"
f <- function() {
  sql_1 <-
    "
      DROP TABLE if exists t1;
      CREATE TABLE t1 (
        id   int  primary key,
        s    text not null
      );
    "
  sql_2 <- "SHOW ALL TABLES;"
  cn <- DBI::dbConnect(duckdb::duckdb(), dbdir = path_db, bigint = "integer64")
  
  # 使用dbExecute执行DDL，自动清理资源
  DBI::dbExecute(cn, sql_1)
  ds_2 <- DBI::dbGetQuery(cn, sql_2)
  
  DBI::dbDisconnect(cn, shutdown = TRUE)
  rm(cn)
  gc()
  
  ds_2
}

# 两次执行均正常
f()
f()

内容的提问来源于stack exchange，提问作者wibeasley