"IO Error: Failed to close entry" when COPYing from PostgreSQL to EXCEL

[FlipperPA]

I'm receiving the following error from DuckDB when COPYing a query from PostgreSQL to xlsx. This code works for 99% of the SQL queries we throw at it, but we've found one which produces a large result which is kicking this error. I've searched high and low but can't find any reference to this error in the source (or the web in general!) that matches. I'm hoping someone can point me in the right direction. Here's the stacktrace from Python:

Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "/var/query-manager/workers/postgres_env/lib/python3.12/site-packages/postgres_query/__main__.py", line 6, in <module>
    PostgreSQLWorker(argv[1], argv[2], argv[3]).run()
  File "/var/query-manager/workers/postgres_env/lib/python3.12/site-packages/worker/__init__.py", line 509, in run
    raise (e)
  File "/var/query-manager/workers/postgres_env/lib/python3.12/site-packages/worker/__init__.py", line 439, in run
    results_files = self.run_query(identifiers, method_2_document_id)
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/query-manager/workers/postgres_env/lib/python3.12/site-packages/postgres_query/utils.py", line 827, in run_query
    self.write_query_to_file(cursor, query_runner, pg_format, ext)
  File "/var/query-manager/workers/postgres_env/lib/python3.12/site-packages/postgres_query/utils.py", line 292, in write_query_to_file
    self.write_duckdb_file(cursor, query_runner, ext, copy_sql)
  File "/var/query-manager/workers/postgres_env/lib/python3.12/site-packages/postgres_query/utils.py", line 400, in write_duckdb_file
    duckdb.sql(f"""
duckdb.duckdb.IOException: IO Error: Failed to close entry

Here's the pertinent part of the code - the error triggers from duckdb.sql():

duckdb.sql(f"SET extension_directory = '{output_path}';")
duckdb.sql(f"SET temp_directory = '{output_path}';")
extension_importer.import_extension("postgres_scanner")

duckdb.sql(f"""
    ATTACH 'host={DB["HOST"]} port={DB["PORT"]} user={username}
    dbname={DB["DBNAME"]}' AS pg_connection (TYPE POSTGRES);
""")

extension_importer.import_extension("excel")
duckdb.sql(f"""
    LOAD excel;
    COPY (SELECT * FROM postgres_query('pg_connection','{ddb_query}'))
    TO '{output_file}'
    WITH (FORMAT xlsx, HEADER true, SHEET 'Results');
""")

The same basic code works when exporting to Parquet: it runs successfully, outputting 676 columns and 717,039 rows when COPY'd to Parquet (or CSV). Here's the code that works for the problematic query to Parquet:

duckdb.sql(f"SET extension_directory = '{output_path}';")
duckdb.sql(f"SET temp_directory = '{output_path}';")
extension_importer.import_extension("postgres_scanner")

duckdb.sql(f"""
    ATTACH 'host={DB["HOST"]} port={DB["PORT"]} user={username}
    dbname={DB["DBNAME"]}' AS pg_connection (TYPE POSTGRES);
""")

duckdb.sql(f"""
    COPY (SELECT * FROM postgres_query('pg_connection','{ddb_query}'))
    TO '{output_file}'
    WITH (FORMAT parquet, COMPRESSION zstd, COMPRESSION_LEVEL 3);
""")

Here's the SQL for the problematic query (ddb_query); it runs fine to Parquet:

SELECT
    comp_na_daily_all.fundq.costat AS "(costat) Active/Inactive Status Marker",
    id_table.add1 AS "(add1) Address Line 1",
    id_table.add2 AS "(add2) Address Line 2",
    [...trimmed for length...]
    comp_na_daily_all.fundq.prchq AS "(prchq) Price High - Quarter",
    comp_na_daily_all.fundq.prclq AS "(prclq) Price Low - Quarter"

FROM comp_na_daily_all.fundq
LEFT JOIN (
    SELECT
        gvkey,
        add1,
        add2,
        add3,
        add4,
        addzip,
        busdesc,
        city,
        conml,
        county,
        dldte,
        dlrsn,
        ein,
        fax,
        fyrc,
        ggroup,
        gind,
        gsector,
        gsubind,
        idbflag,
        incorp,
        ipodate,
        loc,
        naics,
        phone,
        prican,
        prirow,
        priusa,
        sic,
        spcindcd,
        spcseccd,
        spcsrc,
        state,
        stko,
        weburl
    FROM comp_na_daily_all.company
) AS id_table 

ON comp_na_daily_all.fundq.gvkey = id_table.gvkey

WHERE comp_na_daily_all.fundq.datadate BETWEEN
    %s AND %s

AND ("comp_na_daily_all"."fundq"."consol" = ANY (ARRAY['C','N','R','P','D']) AND "comp_na_daily_all"."fundq"."indfmt" = ANY (ARRAY['INDL','FS']) AND "comp_na_daily_all"."fundq"."datafmt" = ANY (ARRAY['STD','SUMM_STD','PRE_AMENDS','PRE_AMENDSS']) AND "comp_na_daily_all"."fundq"."curcdq" = ANY (ARRAY['USD','CAD']) AND "comp_na_daily_all"."fundq"."costat" = ANY (ARRAY['A','I'])) AND (datafqtr IS NOT NULL) AND (datacqtr IS NOT NULL)

PARAMETERS: [datetime.date(2010, 1, 1), datetime.date(2025, 6, 30)]

If anyone with more expertise in DuckDB could help point me in the right direction, that would be very much appreciated!

[FlipperPA]

Just to follow up: these seem to be occurring for queries of a certain size, when the number of columns * the number of rows causes the size to balloon. Is DuckDB doing a SELECT from PostgreSQL into a temporary DuckDB table called postgres_query during this process? If so, is there a limit on what size that temp table could be? That might be what we're running into. I know it isn't often people are selecting 500+ columns.

[FlipperPA]

I've gotten a stack trace on the error from C++:

Thread 9 "python" hit Breakpoint 1.7, 0x00007fffb6b785c0 in duckdb::IOException::IOException(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&) ()
   from /tmp/v1.3.0/linux_amd64/excel.duckdb_extension
(gdb) bt
#0  0x00007fffb6b785c0 in duckdb::IOException::IOException(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&) () from /tmp/v1.3.0/linux_amd64/excel.duckdb_extension
#1  0x00007fffb694709b in duckdb::ZipFileWriter::EndFile() () from /tmp/v1.3.0/linux_amd64/excel.duckdb_extension
#2  0x00007fffb6966658 in duckdb::Finalize(duckdb::ClientContext&, duckdb::FunctionData&, duckdb::GlobalFunctionData&) () from /tmp/v1.3.0/linux_amd64/excel.duckdb_extension
#3  0x00007ffff5eb07bd in duckdb::PhysicalCopyToFile::FinalizeInternal(duckdb::ClientContext&, duckdb::GlobalSinkState&) const ()
   from /home/tallen/projects/duckdb-xlsx/venv/lib/python3.12/site-packages/duckdb/duckdb.cpython-312-x86_64-linux-gnu.so
#4  0x00007ffff648316c in duckdb::PipelineFinishTask::ExecuteTask(duckdb::TaskExecutionMode) ()
   from /home/tallen/projects/duckdb-xlsx/venv/lib/python3.12/site-packages/duckdb/duckdb.cpython-312-x86_64-linux-gnu.so
#5  0x00007ffff6474fc6 in duckdb::ExecutorTask::Execute(duckdb::TaskExecutionMode) ()
   from /home/tallen/projects/duckdb-xlsx/venv/lib/python3.12/site-packages/duckdb/duckdb.cpython-312-x86_64-linux-gnu.so
#6  0x00007ffff6477712 in duckdb::TaskScheduler::ExecuteForever(std::atomic<bool>*) ()
   from /home/tallen/projects/duckdb-xlsx/venv/lib/python3.12/site-packages/duckdb/duckdb.cpython-312-x86_64-linux-gnu.so
#7  0x00007ffff3eeabb4 in ?? () from /lib/x86_64-linux-gnu/libstdc++.so.6
#8  0x00007ffff7c9caa4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:447
#9  0x00007ffff7d29c3c in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:78

The error is being thrown from this line:

duckdb-excel/src/excel/xlsx/zip_file.cpp

Line 268 in 9189a80

throw IOException("Failed to close entry");

[Maxxen]

Hello!

I haven't looked into this yes, but my guess is that the sheet is too large for zip32 entries (>4GB) given that 676 * 717039 * 8 (for doubles) is approaching that limit. XLSX files are really just zip archives of XML, so we probably need to add some flag to make minizip write zip64 archives.

[FlipperPA]

@Maxxen Ha, I was just debugging with a colleague with gdb and we said the same exact thing a few minutes ago. I was thinking perhaps MZ_OK isn't being returned once we surpass the zip32.

We'll continue to look into this as well to see if we can find the appropriate flag. That said, the file we're writing doesn't seem to be close to 4GB when it dies on us:

-rw-r--r-- 1 tallen tallen 926696583 Jun 25 11:53 duckdb-test.xlsx

I'm going to have a colleague publish a repro here, just in case it helps. Thanks again!

[FlipperPA]

I noticed two lines where it appears the code is disabling zip64, with a note that it isn't compatible with XLSX:

• duckdb-excel/src/excel/xlsx/zip_file.cpp

  Line 228 in 9189a80

  file_info.zip64 = MZ_ZIP64_DISABLE; // not compatible with XLSX

• duckdb-excel/src/excel/xlsx/zip_file.cpp

  Line 240 in 9189a80

  file_info.zip64 = MZ_ZIP64_DISABLE; // not compatible with XLSX

According to the docs here, there are options for MZ_ZIP64_AUTO and MZ_ZIP64_DISABLE.

[FlipperPA]

I've been trying to build this extension successfully, and think I have most of it figured out, but keep getting stuck on minizip-ng. Here are the steps I'm taking. If @Maxxen or someone else helps me get this working, I'd be happy to writing up a README-contributing.md for the repo. Here are the steps I've taken:

• Fork duckdb-excel, clone, create a branch, make my changes, and push to my fork
• git clone git@github.com:duckdb/duckdb.git
• Install pre-reqs: sudo apt install cmake pkg-config or for Rocky sudo dnf install cmake expat-devel
• git clone https://github.com/microsoft/vcpkg.git
• cd vcpkg && ./bootstrap-vcpkg.sh && ./vcpkg install minizip-ng
• cd .. && git clone git@github.com:duckdb/duckdb.git && cd duckdb
• Create ./extension/extension_config_local.cmake with these contents for my fork and commit with my changes:

duckdb_extension_load(duckdb-excel
    (DONT_LINK)
    GIT_URL https://github.com/FlipperPA/duckdb-excel
    GIT_TAG 0dd2bbb9fc72f65481469c52a59f5387aeda3aff
)

• mkdir build && cd build
• Try to build with cmake -DCMAKE_TOOLCHAIN_FILE=../../vcpkg/scripts/buildsystems/vcpkg.cmake ..
• I've tried multiple attempts to get it to find minizip-ng, but I'm clearly missing something:

$ cmake -DCMAKE_TOOLCHAIN_FILE=../../vcpkg/scripts/buildsystems/vcpkg.cmake ..
-- git hash d524c57a58, version v1.4.0-dev1068, extension folder d524c57a58
-- Extensions will be deployed to: /home/tallen/projects/duckdb/build/repository
-- Load extension 'duckdb-excel' from https://github.com/FlipperPA/duckdb-excel @ 0dd2bbb
-- Load extension 'core_functions' from '/home/tallen/projects/duckdb/extensions' @ d524c57a58
-- Load extension 'parquet' from '/home/tallen/projects/duckdb/extensions' @ d524c57a58
-- Load extension 'jemalloc' from '/home/tallen/projects/duckdb/extensions' @ d524c57a58
CMake Warning at CMakeLists.txt:1365 (message):
  Extension 'duckdb-excel' has a vcpkg.json, but build was not run with
  VCPKG.  If build fails, check out VCPKG build instructions in
  'duckdb/extension/README.md' or try manually installing the dependencies in
  /home/tallen/projects/duckdb/build/_deps/duckdb-excel_extension_fc-src/vcpkg.json


-- Extensions linked into DuckDB: [core_functions, parquet, jemalloc]
-- Extensions built but not linked: [duckdb-excel]
-- Configuring done (3.7s)
CMake Error at build/_deps/duckdb-excel_extension_fc-src/CMakeLists.txt:27 (target_link_libraries):
  Target "excel_extension" links to:

    MINIZIP::minizip-ng

  but the target was not found.  Possible reasons include:

    * There is a typo in the target name.
    * A find_package call is missing for an IMPORTED target.
    * An ALIAS target is missing.

Previously I tried building minizip-ng from source:

git clone https://github.com/zlib-ng/minizip-ng.git
cd minizip-ng
mkdir -p build
cd build
cmake .. -DMZ_PROJECT_SUFFIX=-ng
make
sudo make install
sudo ln -s /usr/local/lib/cmake/minizip /usr/local/lib/cmake/minizip-ng
cd /usr/local/lib/cmake/minizip-ng
sudo ln -s minizip-config.cmake minizip-ngConfig.cmake
sudo ln -s minizip-config-version.cmake minizip-ngConfig-version.cmake

Similar issue there, even with the symlinks.

[Maxxen]

Hello! You should be a able to just "make debug" in this repository to build duckdb with a statically linked excel dxtensions. If you are building the excel extension as an "out of tree" extension from the main duckdb repo (like it seems like youre trying to do) you want to read this https://github.com/duckdb/duckdb/blob/main/extension/README.md, specifically the part all the way in the bottom. As per the error message, the build system doesnt pick up VCPKG, so you need to run make extension_configuration and set the environment variable to your vcpkg install dir.

[amalek215]

Here is code that recreates the issue.
Error on my system is encountered on third attempt to create xslx file
615000 rows and 100 columns works
615500 rows and 100 columns works
616000 rows and 100 columns throws the error
update start_rows to 617000 to have it fail on the first pass (and to speedup the the test)

import duckdb
import os
from duckdb_extensions import extension_importer
from datetime import datetime
import csv

def write_to_excel(duckdb_conn, output_filename="output.xlsx"):
    try:
        print(f"Attempting to copy data from temp_table to '{output_filename}'...")
        extension_importer.import_extension("excel")
        duckdb_conn.execute(f"""LOAD excel; 
                                COPY (SELECT * FROM temp_table) 
                                TO '{output_filename}' 
                                WITH (FORMAT xlsx, HEADER true);""")

        print(f"Successfully wrote data to {output_filename}")
    except duckdb.IOException as e:
        print(f"DuckDB IOException during Excel export: {e}")
        print("This indicates a potential issue writing the Excel file.")
        raise e
    except Exception as e:
        print(f"An unexpected error occurred during Excel export: {e}")
        raise e

def main():
    # Connect to DuckDB
    con = duckdb.connect(database=':memory:', read_only=False)
    # --- Test Parameters ---
    start_rows = 615000  # Starting number of rows to test
    row_increment = 500 # Increase rows by this amount in each test
    num_cols = 100     # Number of columns to test
    init_csv = f"init{num_cols}_{start_rows}.csv" # quick way load data into duckdb is from csv
    # Create table 
    print(f"{datetime.now()}:Creating table 'temp_table' w/ {num_cols} columns.")
    col_str = ",\n    ".join([f"col{i} VARCHAR" for i in range(1, num_cols+1)])
    con.execute(f"CREATE TABLE temp_table({col_str});")
    num_rows = start_rows
    if not os.path.exists(init_csv):
        print(f"{datetime.now()}:Initalizing array of size {start_rows}")
        data = [['This is my test data'] * (num_cols)] * start_rows
        print(f"{datetime.now()}:Creating csv {init_csv}.")
        with open(init_csv, mode="w", newline="") as file:
            writer = csv.writer(file)
            writer.writerows(data)

    print(f"{datetime.now()}:Loading from csv.")
    con.execute(f"COPY temp_table FROM '{init_csv}' ;")
    print(f"{datetime.now()}:Creating increment array w/ {row_increment} rows")
    inc_data = [['This is the new data'] * (num_cols) ] * row_increment

    while True:
        print(f"{datetime.now()}:\n--- Testing with {num_rows} rows and {num_cols} columns ---")
        # Write to Excel
        write_to_excel(con, f"test_output_{num_rows}rows_{num_cols}cols.xlsx")
        print(f"{datetime.now()}:Adding {len(inc_data)} rows to table")
        con.executemany(f"INSERT INTO temp_table VALUES ({', '.join('?'*num_cols)})", inc_data)
        num_rows = num_rows + len(inc_data)

if __name__ == "__main__":
    main()

[FlipperPA]

For anyone trying to build this on Rocky 9... don't bother. I wish we used Ubuntu at work, but... we don't.

Save yourself several days of inventing your own swear words, and just install an Ubuntu 24.04 VM. After a few days of going around in circles, I've got it compiling within a few minutes of my first attempt on Ubuntu.

EDIT: it took a lot of doing, but I got this working: #62