DuckDB persistent storage: KeyError after reconnect

[rgreminger]

There might be a bug for DuckDB with persistent storage.

The following produces a KeyError and the table cannot be retrieved after the connection is closed and re-opened. The same also happens when Julia is restarted.

using TidierDB 

df = DataFrame(id = [string('A' + i ÷ 26, 'A' + i % 26) for i in 0:9], 
                          groups = [i % 2 == 0 ? "aa" : "bb" for i in 1:10], 
                          value = repeat(1:5, 2), 
                          percent = 0.1:0.1:1.0);

db = connect(duckdb(), "tmp.db")

copy_to(db, df, "df_mem");

close(db)

db = connect(duckdb(), "tmp.db")

show_tables(db) 
# this works and lists the "df_mem" table correctly 

table_retrieved = db_table(db, "df_mem")
# produces: KeyError Binder Error: KeyError: key "df_mem" not found

[drizk1]

Interesting. So i played around with this and found i could recreate the issue with DataFrames, but not with with csvs, jsons etc - those files would work after closing the connection/restarting julia. if you recreate the df in your julia session, it will be able to read it since the view persists (based on show_tables)

The issue with dataframes is that in copy_to, the following is being used DuckDB.register_data_frame, which is actually creating a view of the dataframe - not copying it into memory. This is misleading/incorrect.

To fix this, I will make it so copy_to actually copies the df into the database - instead of creating a view, and I will adjust db_table so that it supports directly receiving a df as the second argument to avoid confusion.

Another great catch, thank you.

[drizk1]

Some changes in this branch fixes-db_table-and-copy_to-for-df:

• copy_to will actually copy the table to the database, instead of just creating a view (regardless if you close connection or restart julia)
• db_table now supports dataframes directly

# this will create a view called "df_view" not a copy
dfv = db_table(db, df, "df_view") 

@chain t(dfv) begin ...

you are welcome to try it out while i make some docs adjustments before releasing

[rgreminger]

Wow, that was quick. I tested the fix with the simple example below (and a bit more broadly), and it works as intended.
Thank you so much for the fix and all your work on this project!

using TidierDB 

df = DataFrame(id = [string('A' + i ÷ 26, 'A' + i % 26) for i in 0:9], 
                          groups = [i % 2 == 0 ? "aa" : "bb" for i in 1:10], 
                          value = repeat(1:5, 2), 
                          percent = 0.1:0.1:1.0);
db = connect(duckdb(), "tmp.db")
copy_to(db, df, "df_mem");
close(db)


db = connect(duckdb(), "tmp.db")
df_retrieved = @collect(db_table(db, "df_mem"))
isequal(df_retrieved, df)
# true -> all good! 

close(db)
rm("tmp.db")

[drizk1]

Happy to hear the fix works for you and that you're enjoying the ecosystem!

Will close this issue when we release the update soon