Duckdb: Temporary tables support

[Mytherin]

[hannesmuehleisen]

Should the temp tables be only in memory?

[cboettig]

Support for temporary tables would be great for me. In MonetDBLite I love the ability to do fast filtering joins by copying a table from R into a temporary table (e.g. via dplyr::semi_join(..., copy=TRUE)).

Should the temp tables be only in memory?

For my purposes it would always be okay for temp tables to only be in memory, though perhaps that's something one could configure by an environmental variable or something?

[Mytherin]

For my purposes it would always be okay for temp tables to only be in memory, though perhaps that's something one could configure by an environmental variable or something?

Having this be configurable should be unnecessary once we have a buffer manager for the temporary data (which we need for regular tables anyway). A temporary table will then just be a table for which the data is never (1) checkpointed, or (2) written to disk. The table should always reside in memory unless the user runs out of memory, in which case it might temporarily spill to disk.

This should not be so difficult to implement; I might get to implementing it after I finish implementing the buffer manager for the storage.

[cboettig]

@Mytherin @hannesmuehleisen Any news on this one? Support for this is still the main hold up for me in adopting duckdb more widely.

[Mytherin]

I am not currently working on this feature as I am still busy reworking the storage layer, however, I will make it a bigger priority and work on it after I am finished with that. Perhaps @hannesmuehleisen could take a look right now if he is up for it.

[hannesmuehleisen]

This is implemented and will be available as soon as the buffermanager branch is finished and merged. Here are some specifics:

• TEMPORARY tables are implemented to be connection-local, which means that they are visible from within the same connection, but not across connections.
• We only support the ON COMMIT PRESERVE ROWS behaviour.
• Temporary tables can only exist in the special temp schema, and not in any other schema. If no schema is specified, temp is assumed when CREATE TEMPORARY TABLE is used. There is no need to create the temp schema, in fact, it is not allowed.

[cboettig]

@hannesmuehleisen Wonderful! Maybe I'm just jumping the gun here, but installing from the buffermanager branch these don't seem to be working from the R side yet:

library(duckdb)
db <- dbConnect(duckdb())
dbWriteTable(db, "mtcars", mtcars) # success! 
dbWriteTable(db, "mtcars", mtcars, temporary = TRUE) # Error: Temporary tables not supported yet

[hannesmuehleisen]

@cboettig try again please, this should be fixed. We also now run most of the dbplyr test cases in DuckDB's testing with temporary tables

[cboettig]

@hannesmuehleisen 👍 Excellent, temporary table operations, including those that use dplyr for filtering joins etc, are all now working for me. 👏

Not directly related, but the installation of the buffermanager branch fails on my Ubuntu Linux box but it is working on my Mac. Should I open a different thread for that somewhere? The ubuntu system can install fine from the master branch, but creates the error log below on the branch version

Scanning dependencies of target duckdb_common
[ 40%] Building CXX object src/common/CMakeFiles/duckdb_common.dir/ub_duckdb_common.cpp.o
In file included from constants.cpp:0:0:
/tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:15:43: error: ‘numeric_limits’ is not a member of ‘std’
 const transaction_t NOT_DELETED_ID = std::numeric_limits::max() - 1;   // 2^64 - 1
                                           ^~~~~~~~~~~~~~
/tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:15:71: error: expected primary-expression before ‘>’ token
 const transaction_t NOT_DELETED_ID = std::numeric_limits::max() - 1;   // 2^64 - 1
                                                                       ^
/tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:15:78: error: no matching function for call to ‘max()’
 const transaction_t NOT_DELETED_ID = std::numeric_limits::max() - 1;   // 2^64 - 1
                                                                              ^
In file included from /usr/include/c++/7/memory:62:0,
                 from /tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/include/common/constants.hpp:12,
                 from /tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:1,
                 from constants.cpp:0:
/usr/include/c++/7/bits/stl_algobase.h:219:5: note: candidate: template const _Tp& std::max(const _Tp&, const _Tp&)
     max(const _Tp& __a, const _Tp& __b)
     ^~~
/usr/include/c++/7/bits/stl_algobase.h:219:5: note:   template argument deduction/substitution failed:
In file included from constants.cpp:0:0:
/tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:15:78: note:   candidate expects 2 arguments, 0 provided
 const transaction_t NOT_DELETED_ID = std::numeric_limits::max() - 1;   // 2^64 - 1
                                                                              ^
In file included from /usr/include/c++/7/memory:62:0,
                 from /tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/include/common/constants.hpp:12,
                 from /tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:1,
                 from constants.cpp:0:
/usr/include/c++/7/bits/stl_algobase.h:265:5: note: candidate: template const _Tp& std::max(const _Tp&, const _Tp&, _Compare)
     max(const _Tp& __a, const _Tp& __b, _Compare __comp)
     ^~~
/usr/include/c++/7/bits/stl_algobase.h:265:5: note:   template argument deduction/substitution failed:
In file included from constants.cpp:0:0:
/tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:15:78: note:   candidate expects 3 arguments, 0 provided
 const transaction_t NOT_DELETED_ID = std::numeric_limits::max() - 1;   // 2^64 - 1
                                                                              ^
/tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:16:45: error: ‘numeric_limits’ is not a member of ‘std’
 const transaction_t MAXIMUM_QUERY_ID = std::numeric_limits::max(); // 2^64
                                             ^~~~~~~~~~~~~~
/tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:16:73: error: expected primary-expression before ‘>’ token
 const transaction_t MAXIMUM_QUERY_ID = std::numeric_limits::max(); // 2^64
                                                                         ^
/tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:16:80: error: no matching function for call to ‘max()’
 const transaction_t MAXIMUM_QUERY_ID = std::numeric_limits::max(); // 2^64
                                                                                ^
In file included from /usr/include/c++/7/memory:62:0,
                 from /tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/include/common/constants.hpp:12,
                 from /tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:1,
                 from constants.cpp:0:
/usr/include/c++/7/bits/stl_algobase.h:219:5: note: candidate: template const _Tp& std::max(const _Tp&, const _Tp&)
     max(const _Tp& __a, const _Tp& __b)
     ^~~
/usr/include/c++/7/bits/stl_algobase.h:219:5: note:   template argument deduction/substitution failed:
In file included from constants.cpp:0:0:
/tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:16:80: note:   candidate expects 2 arguments, 0 provided
 const transaction_t MAXIMUM_QUERY_ID = std::numeric_limits::max(); // 2^64
                                                                                ^
In file included from /usr/include/c++/7/memory:62:0,
                 from /tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/include/common/constants.hpp:12,
                 from /tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:1,
                 from constants.cpp:0:
/usr/include/c++/7/bits/stl_algobase.h:265:5: note: candidate: template const _Tp& std::max(const _Tp&, const _Tp&, _Compare)
     max(const _Tp& __a, const _Tp& __b, _Compare __comp)
     ^~~
/usr/include/c++/7/bits/stl_algobase.h:265:5: note:   template argument deduction/substitution failed:
In file included from constants.cpp:0:0:
/tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/src/common/constants.cpp:16:80: note:   candidate expects 3 arguments, 0 provided
 const transaction_t MAXIMUM_QUERY_ID = std::numeric_limits::max(); // 2^64
                                                                                ^
src/common/CMakeFiles/duckdb_common.dir/build.make:62: recipe for target 'src/common/CMakeFiles/duckdb_common.dir/ub_duckdb_common.cpp.o' failed
make[3]: *** [src/common/CMakeFiles/duckdb_common.dir/ub_duckdb_common.cpp.o] Error 1
CMakeFiles/Makefile2:2941: recipe for target 'src/common/CMakeFiles/duckdb_common.dir/all' failed
make[2]: *** [src/common/CMakeFiles/duckdb_common.dir/all] Error 2
CMakeFiles/Makefile2:317: recipe for target 'src/CMakeFiles/duckdb_static.dir/rule' failed
make[1]: *** [src/CMakeFiles/duckdb_static.dir/rule] Error 2
Makefile:131: recipe for target 'duckdb_static' failed
make: *** [duckdb_static] Error 2
Build failed :/
ERROR: configuration failed for package ‘duckdb’
* removing ‘/usr/local/lib/R/site-library/duckdb’
* restoring previous ‘/usr/local/lib/R/site-library/duckdb’
Error: Failed to install 'duckdb' from GitHub:
  (converted from warning) installation of package ‘/tmp/RtmpL5Lr9r/remotes4ced20fc80f8/cwida-duckdb-90e2881/tools/rpkg’ had non-zero exit status
> 

[hannesmuehleisen]

@cboettig The branch is quite bleeding edge, does not surprise me that it does not compile on Linux yet. We mostly use OSX for development here.

[hannesmuehleisen]

@cboettig should work on GCC now

[hannesmuehleisen]

This is merged now.

[cboettig]

:clap: working great!

[cboettig]

@hannesmuehleisen Apologies if this is dumb question, but would it be possible for temporary tables to work on a read-only connection?

I'd very much like to be able to support multiple connections to the same db (e.g as in #77) while still allowing for things like filtering joins (inner_join(remote_tbl, filtering_tbl, copy=TRUE) in dplyr syntax), but (perhaps unsurprisingly) this currently throws an error for a readonly connection.

[hannesmuehleisen]

@cboettig in principle this is possible, we would have to think about where to put data from temporary tables once they become big though

[Mytherin]

@cboettig in principle this is possible, we would have to think about where to put data from temporary tables once they become big though

I think that should be fine already, the BufferManager will offload them to the temp directory specified by the user on startup. The only problem is that those directories by default will conflict, as both processes will default to e.g. database.db.tmp. It should be possible to extend the file locks to the temporary directories to solve this. Then when starting up, the database system could automatically try incremental versions if a lock is not obtained, e.g. database.tmp.1, database.tmp.2, etc.

[cboettig]

@hannesmuehleisen @Mytherin Thanks, that would be awesome. Right, I was wondering if the temporary table had an appropriately random-name suffix or something it might avoid the collision. In the use case I outline above (filtering joins), the temporary table used to filter is already in memory (though I suppose the result of the filter creates another temporary table?).

For me, filtering joins like these are a clear case where duckdb performance is just so much faster than iterating over filter or doing the same join in something like RSQLite (I frequently see joins that go in 1 sec in duckdb that take 8 min in RSQLite), and are the kind of operation I can't do efficiently with more limited storage backends like arrow.

[cboettig]

p.s. would it be better for me to open a new issue for this?

[Mytherin]

@hannesmuehleisen @Mytherin Thanks, that would be awesome. Right, I was wondering if the temporary table had an appropriately random-name suffix or something it might avoid the collision. In the use case I outline above (filtering joins), the temporary table used to filter is already in memory (though I suppose the result of the filter creates another temporary table?).

Off-loading to disk is only performed if a memory threshold is crossed. As long as the buffer manager limit is not exceeded the data will stay in memory. Parts of the temporary table will be offloaded only if the system runs out of memory (to prevent exceeding available memory). These off-loaded buffers are written as files to a temporary folder. Currently that folder defaults to database.db.tmp (if the database file is called database.db). The buffers themselves are just named id.block, e.g. 1.block, and hence if two databases write files to the same temporary folder there is a possibility of these files conflicting and the systems becoming corrupted in some manner.

p.s. would it be better for me to open a new issue for this?

Yes please.