Disconnect connection and close database

[hl]

First of all, many thanks for this great library.

I was wondering if duckdb_disconnect and duckdb_close are on the roadmap.

My use case would be to start a db and connection (in memory), load data, query it and shut it down again to free up the allocated memory.

(My C knowledge is non-existent otherwise I would have opened a PR)

[AlexR2D2]

Thanks!
Please, see similar issues, next

[ruslandoga]

👋 @AlexR2D2

Just a question: since the NIF resource destructors run on the main scheduler, doesn't it have a slight issue with (unlikely) scheduler blocking?

For reference, here are the timings on my system using C API bindings, closing an in-memory database (for the first time) takes 4 ms (which is probably fine, even on a main scheduler).

iex> {_, db} = :timer.tc fn -> DuxDB.open(":memory:") end
{61964, #Reference<0.1637195888.2898133009.42168>}
iex> {_, conn} = :timer.tc fn -> DuxDB.connect(db) end
{92, #Reference<0.1637195888.2898132993.42140>}
iex> {_, stmt} = :timer.tc fn -> DuxDB.prepare(conn, "select 1") end
{7939, #Reference<0.1637195888.2898133008.41788>}
iex> {_, :ok} = :timer.tc fn -> DuxDB.destroy_prepare(stmt) end
{53, :ok}
iex> {_, :ok} = :timer.tc fn -> DuxDB.disconnect(conn) end
{82, :ok}
iex> {_, :ok} = :timer.tc fn -> DuxDB.close(db) end
{4514, :ok}

Subsequent executions of this snippet show lower timings for open, prepare, and sometimes for close too.

iex> {_, db} = :timer.tc fn -> DuxDB.open(":memory:") end
{6002, #Reference<0.1637195888.2898133009.42198>}
iex> {_, conn} = :timer.tc fn -> DuxDB.connect(db) end
{123, #Reference<0.1637195888.2898132993.43088>}
iex> {_, stmt} = :timer.tc fn -> DuxDB.prepare(conn, "select 1") end
{401, #Reference<0.1637195888.2898133008.41794>}
iex> {_, :ok} = :timer.tc fn -> DuxDB.destroy_prepare(stmt) end
{25, :ok}
iex> {_, :ok} = :timer.tc fn -> DuxDB.disconnect(conn) end
{58, :ok}
iex> {_, :ok} = :timer.tc fn -> DuxDB.close(db) end
{4360, :ok}

[AlexR2D2]

👋 @ruslandoga yes, you are right, this can happen. In addition, you may need to close DB explicitly, for example to send db file somebody or archive it and open new db file, thus, explicit closing can be required by business logic, so I will add this close functions to the lib. Thanks!

[ruslandoga]

There was a recent discussion about this problem on Erlang forums: https://erlangforums.com/t/how-to-deal-with-destructors-that-can-take-a-while-to-run-and-possibly-block-the-scheduler/4290

DuckDB is probably safe from these issues but I still wanted to share since it's interesting :)