I cannot run the following code twice in one julia session:
using DuckDB
using DBInterface
db = DuckDB.DB("C:\\Temp\\test.duckdb")
t = DBInterface.execute(db, "SELECT 1")
DuckDB.close_database(db)
On the second run I get the error:
`ERROR: IO Error: Cannot open file "C:\Temp\test.duckdb": The process cannot access the file because it is being used by another process.`
I have tried
DBInterface.close!(db)
in place of
DuckDB.close_database(db)
it makes no difference.
DuckDB defines close(db::DuckDB.DB) function so close(db) would be enough.
This does not error for me:
using DuckDB, DBInterface
db = DBInterface.connect(DuckDB.DB,"C:\\Temp\\test.duckdb")
t = DBInterface.execute(db, "SELECT 1")
DBInterface.close!(db)
db = DBInterface.connect(DuckDB.DB,"C:\\Temp\\test.duckdb")
t = DBInterface.execute(db, "SELECT 1")
DBInterface.close!(db)
This is how I create and close connections, never had issues (except sometimes within Pluto, but I think that is separate).
Just using close(db) gives the same error
Still the same problem with your approach:
ERROR: IO Error: Cannot open file "C:\Temp\test.duckdb": The process cannot access the file because it is being used by another process.
Stacktrace:
[1] DuckDB.DuckDBHandle(f::String, config::DuckDB.Config)
@ DuckDB C:\Users\kobus\.julia\packages\DuckDB\VdbjW\src\database.jl:17
[2] DuckDB.DB(f::String, config::DuckDB.Config)
@ DuckDB C:\Users\kobus\.julia\packages\DuckDB\VdbjW\src\database.jl:82
[3] DB
@ C:\Users\kobus\.julia\packages\DuckDB\VdbjW\src\database.jl:90 [inlined]
[4] connect(#unused#::Type{DuckDB.DB}, f::String)
@ DuckDB C:\Users\kobus\.julia\packages\DuckDB\VdbjW\src\database.jl:105
[5] top-level scope
@ c:\Source\Repos\RDAIngest.jl\src\close_test.jl:7
System: Windows 11
Latest version of Visual Studio Code
Julia v1.9.1
What version of duckdb?
Apologies,
DuckDB v0.8.0
Updated to DuckDB v0.8.1 - same story
Maybe the problem is with windows not “letting go” of the file.
I’ve seen permissions change in new files that prevented successive
access by the same program or user (in a shell session).
What about trying to use separate filenames for each instance,
maybe with Base.Filesystem.mktemp or some such?
Very strange because it sounds like your system matches mine in this regard (latest Julia & DuckDB & Win 11).
I have done some further tests and I get the same error if I run it under Windows Server 2019 and Windows 10. Same Julia 1.9.1. In these cases I also used a different user account to my original test.
I also ran the test in a Julia environment with just DBInterface and DuckDB installed. Same problem.
Any thoughts on where I could look next?
I would very much like to consider DuckDB for a project I have, but not being able to reliably close and open the database is a problem.
Unfortunately that won’t work for me, I need repeated access to the same database in a single julia session.
Have you tried the duckdb cli to see if you also get errors with that?
Have you tried with a different file location?
The DBeaver application has no problem with opening and closing DuckDb databases, including DuckDb databases created via julia. I have not worked with the DuckDb CLI. I have tried various file locations - it makes no difference.
I have noticed that when I close the DuckDb database in julia the temporary *.wal file that gets created when a database is opened, is not deleted on database closure, whereas when a database opened and closed with an application like DBeaver, the *wal file is removed.
I can successfully open the dummy database test.duckdb created by the julia code and upon closing the connection in DBeaver, the *.wal file is removed.
If I try to open the database using DBeaver, whilst the julia process is still active (after executing the first set of database open and close statements), I get the same error in DBeaver:
Reason:
IO Error: Cannot open file "d:\temp\test.duckdb": The process cannot access the file because it is being used by another process.
Hmmm ok, amending my first reply, I do get the bug. But it seems like mine happens one step after yours?
If I just simply take this and paste it into the REPL one time, no issues.
using DuckDB, DBInterface
db = DBInterface.connect(DuckDB.DB,"C:\\Temp\\test.duckdb")
t = DBInterface.execute(db, "SELECT 1")
DBInterface.close!(db)
db = DBInterface.connect(DuckDB.DB,"C:\\Temp\\test.duckdb")
t = DBInterface.execute(db, "SELECT 1")
DBInterface.close!(db)
If I try to run it as a script – errors.
If I paste it twice in the same REPL session – errors.
I was going to say that you should open an issue but I see you already have.
What use case do you need to repeatedly open and close the db in the same session?
Can confirm that the error doesn’t happen when using python-duckdb via PythonCall.jl so the issue is Julia client specific.
The error happens because they never get closed 
I will update your issue on the repo. Should be a simple fix.
I have a package that imports different datasets from different sources into the database (production system actually uses Sqlite). The function to import a specific data source open the database and close the database on import completion. The different functions a called from a main script one after another.
This fix plus @aplavin awesome PR to make DuckDB Tables.jl compatible (hopefully to be merged soon!) are two big steps forward for Julia+DuckDB users.
The error still persists for me. The workaround was
using DuckDB, DBInterface
db = DuckDB.open("C:\\Temp\\test.duckdb")
conn = DuckDB.connect(db)
t = DBInterface.execute(conn, "SELECT 1")
DBInterface.close!(conn)
DuckDB.close(db)
db = DuckDB.open("C:\\Temp\\test.duckdb")
conn = DuckDB.connect(db)
t = DBInterface.execute(conn, "SELECT 1")
DBInterface.close!(conn)
DuckDB.close(db)
There is some discussion over here which may be useful to help with this issue
https://discourse.julialang.org/t/is-it-hard-to-support-julia-udfs-in-duckdb/118509/38?u