Error: TransactionContext Error: Failed to commit: failed to pin block of size 256.0 KiB. Temporary directory does not used? #1604
Description
Hello!
It is may be a bug, or may be just my misunderstanding.
I am trying to insert a data.frame into existing database. The data.frame is 257.61 MB size (according to lobstr::obj_size(dat_extr)) - 12,880,502 rows × 5 integer columns.
db_conn <- dbConnect(duckdb::duckdb(), dbdir = "db.duckdb")
dbExecute(db_conn, sprintf("SET memory_limit = '%sGB';", 3L))
dbExecute(db_conn, sprintf("SET max_temp_directory_size = '%sGB';", 20))
dbExecute(db_conn, sprintf("SET threads TO %s;", 1L))
dbExecute(db_conn, "SET preserve_insertion_order=false;")
dbWriteTable(db_conn, tbl_name, data, append = T)I set memory_limit of 3 GB (which is much more than the data size. But I get an error:
Error in `duckdb_result()`:
! rapi_execute: Failed to run query
Error: TransactionContext Error: Failed to commit: failed to pin block of size 256.0 KiB (2.7 GiB/2.7 GiB used)
And by some reason the temporary directory db.duckdb.tmp does not creating during the insertion. It looks like it does not used at all.
Well, the insertion works when I set memory_limit = 4 GB, but that machine doesnt have that amount of free memory and starts swapping.
On my desktop PC the insertion completes successfully even with the memory_limit = 0.1 GB with an active tmp directory use.
duckdb ver. 1.3.2. Cant check on 1.4.0 because of #1587
sessionInfo()
R version 4.5.1 (2025-06-13)
Platform: x86_64-pc-linux-gnu
Running under: Debian GNU/Linux 12 (bookworm)
Matrix products: default
BLAS: /usr/lib/x86_64-linux-gnu/openblas/libblas.so.3
LAPACK: /usr/lib/x86_64-linux-gnu/libopenblasp-r0.3.5.so; LAPACK version 3.8.0
locale:
[1] LC_CTYPE=C.UTF-8 LC_NUMERIC=C LC_TIME=C.UTF-8 LC_COLLATE=C.UTF-8 LC_MONETARY=C.UTF-8 LC_MESSAGES=C.UTF-8
[7] LC_PAPER=C.UTF-8 LC_NAME=C LC_ADDRESS=C LC_TELEPHONE=C LC_MEASUREMENT=C.UTF-8 LC_IDENTIFICATION=C
time zone: Europe/Moscow
tzcode source: system (glibc)
attached base packages:
[1] tools stats graphics grDevices utils datasets methods base
other attached packages:
[1] ranger_0.17.0 xml2_1.4.0 tibble_3.3.0 terra_1.8-70 stringr_1.5.2 stringi_1.8.7
[7] sf_1.0-21 s2_1.1.9 RSQLite_2.4.3 rmapshaper_0.5.0 purrr_1.1.0 progressr_0.16.0
[13] openxlsx_4.2.8 openssl_2.3.3 magrittr_2.0.4 lwgeom_0.2-14 lubridate_1.9.4 logging_0.10-108
[19] jsonlite_2.0.0 httr2_1.2.1 googleCloudStorageR_0.7.0 ggplot2_4.0.0 furrr_0.3.1 future_1.67.0
[25] exactextractr_0.10.0 duckdb_1.3.2 dplyr_1.1.4 DBI_1.2.3 cowplot_1.2.0 aws.s3_0.3.22
loaded via a namespace (and not attached):
[1] tidyselect_1.2.1 farver_2.1.2 blob_1.2.4 S7_0.2.0 fastmap_1.2.0 digest_0.6.37 timechange_0.3.0
[8] lifecycle_1.0.4 googleAuthR_2.0.2 compiler_4.5.1 rlang_1.1.6 yaml_2.3.10 lobstr_1.1.2 askpass_1.2.1
[15] prettyunits_1.2.0 bit_4.6.0 sp_2.2-0 classInt_0.4-11 curl_7.0.0 aws.signature_0.6.0 RColorBrewer_1.1-3
[22] KernSmooth_2.23-26 withr_3.0.2 grid_4.5.1 e1071_1.7-16 globals_0.18.0 scales_1.4.0 cli_3.6.5
[29] crayon_1.5.3 generics_0.1.4 rstudioapi_0.17.1 httr_1.4.7 cachem_1.1.0 proxy_0.4-27 assertthat_0.2.1
[36] parallel_4.5.1 base64enc_0.1-3 vctrs_0.6.5 Matrix_1.7-3 V8_8.0.0 bit64_4.6.0-1 listenv_0.9.1
[43] units_0.8-7 glue_1.8.0 parallelly_1.45.1 codetools_0.2-20 gtable_0.3.6 raster_3.6-32 pillar_1.11.1
[50] rappdirs_0.3.3 dbplyr_2.5.1 R6_2.6.1 wk_0.9.4 lattice_0.22-7 memoise_2.0.1 gargle_1.6.0
[57] class_7.3-23 Rcpp_1.1.0 zip_2.3.3 fs_1.6.6 pkgconfig_2.0.3