[1.5.x regression] ST_Union_Agg + INSERT corrupts transaction ("Skipping beyond end of binary data") and fails rollback

[skutterai]

What happens?

ST_Union_Agg followed by an INSERT … SELECT into a table with a GEOMETRY column (then CHECKPOINT) intermittently corrupts the
transaction on a persistent on‑disk database, throwing:

Invalid Input Error: Skipping beyond end of binary data at position 16

The error then recurs during the automatic rollback, which escalates to a fatal, connection‑killing state:

FATAL Error: Failed to rollback transaction. Cannot continue operation.

It is non‑deterministic: running the exact same script on the exact same data fails at a different GROUP BY partition each time. There is ample free RAM and no disk spilling when it happens. This is a regression in 1.5.x — the identical script on the identical database file completes with no error on 1.4.4, but fails on 1.5.3 (same OS, hardware, data, client, and glibc malloc). Smaller per‑row geometries (e.g. writing the ~32M un‑aggregated input polygons) write fine; only the large aggregated multipolygons produced by
ST_Union_Agg trigger it.

To Reproduce

The failure occurs on a ~32 M‑row spatial dataset and is data‑dependent and intermittent, so I have not yet been able to distill a data‑independent minimal example (a synthetic generator producing ~6,900‑vertex unions did not trigger it). The exact, faithful reproducer:

  import duckdb

  # postcodes.duckdb (~7 GB) contains one table:
  #   voronoi_raw(postcode VARCHAR, region_code VARCHAR(2), voronoi_geom GEOMETRY)
  #   ~32.2M rows: a Voronoi tessellation of UK address points
  #   ~1.75M distinct postcodes spread across 11 region_code values
  con = duckdb.connect("postcodes.duckdb")
  con.execute("LOAD spatial;")
  con.execute("SET threads=4;")            # also fails at threads=23
  con.execute("SET memory_limit='31GB';")  # no disk spill occurs; RAM is not exhausted

  con.execute("""
      CREATE TABLE voronoi_unioned (
          postcode VARCHAR PRIMARY KEY,
          region_code VARCHAR(2),
          unioned_geom GEOMETRY,
          cell_count INTEGER,
          area_m2 DOUBLE
      )
  """)

  # Union the cells of each postcode into one geometry, one region per INSERT.
  for (region,) in con.execute(
      "SELECT DISTINCT region_code FROM voronoi_raw ORDER BY 1"
  ).fetchall():
      con.execute(f"""
          INSERT INTO voronoi_unioned
          SELECT postcode, region_code, unioned_geom, cell_count, ST_Area(unioned_geom)
          FROM (
              SELECT postcode, region_code,
                     ST_Union_Agg(voronoi_geom) AS unioned_geom,
                     COUNT(*) AS cell_count
              FROM voronoi_raw
              WHERE region_code = '{region}'
              GROUP BY postcode, region_code
          )
          WHERE unioned_geom IS NOT NULL
      """)
      con.execute("CHECKPOINT;")
      print(region, "ok")

Output (the partition at which it dies varies run‑to‑run, e.g. NE one run, WA the next):

EE ok
 EM ok
 LN ok
 duckdb.duckdb.FatalException: FATAL Error: Failed to rollback transaction. Cannot continue operation.
 Original Error: Invalid Input Error: Skipping beyond end of binary data at position 16
 Rollback Error: Invalid Input Error: Skipping beyond end of binary data at position 16

The same script against the same file on DuckDB 1.4.4 completes all 11 partitions with no error:

  EE ok
  EM ok
  LN ok
  NE ok
  NW ok
  SC ok
  SE ok
  SW ok
  WA ok
  WM ok
  YH ok

The failing operation is plain SQL (ST_Union_Agg + INSERT + CHECKPOINT), so it should reproduce from the DuckDB CLI as well; I have only reproduced it via the Python client
against this dataset and have not isolated a data‑independent CLI script.

OS:

Ubuntu 24.04.4 LTS (Linux 6.17.0‑35‑generic, x86_64)

DuckDB Version:

1.5.3 (regression); 1.4.4 works correctly

DuckDB Client:

Python client (duckdb PyPI wheel), Python 3.12.3; spatial extension b68b309

Hardware:

AMD Ryzen 9 3900X (12C/24T), 62 GiB RAM (~49 GiB free during the run), NVMe SSD

Full Name:

Matt Duggan

Affiliation:

skutter.ai

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant data sets for reproducing the issue?

Yes

[matthiasfeist]

I ran into the exact same issue using the nodejs bindings

[Maxxen]

Hello!
It's a difficult to fix this without some way to reproduce it. Are you getting this error when hitting the checkpoint specifically? DuckDB v1.5.4 has some fixes related to spatial column checkpointing (although I haven't seen this error in this context specifically). Its unfortunate the synthetic dataset does not reproduce the issue, but would it be possible to share the actual dataset that triggers this? If so you could share it with me privately by emailing me at max@duckdblabs.com.

[skutterai]

Hi, thanks for taking a look - I'll E-Mail you...

Matt

[darrenspurgeon]

Hi @Maxxen,

We hit what looks like the same bug from a different angle, and unlike the original report, ours doesn't need a large real-world dataset or ST_Union_Agg. Might help narrow the root cause.

Versions: DuckDB 1.5.4, via duckdb-go/v2 v2.10504.0.

Schema (simplified from our actual tables... the relevant part is a GEOMETRY column plus a UUID primary key):

CREATE TABLE locations (
   id UUID PRIMARY KEY,
   geojson GEOMETRY,
   -- other scalar columns omitted, not implicated
);
CREATE TABLE location_h3_index (
   h3_cell BIGINT NOT NULL,
   location_id UUID NOT NULL,
   PRIMARY KEY (h3_cell, location_id)
> );

Write pattern (one explicit transaction per logical insert):

BEGIN;
INSERT INTO locations (id, geojson, ...) VALUES (uuidv7(), ST_GeomFromGeoJSON(?), ...) RETURNING id;
INSERT INTO location_h3_index (h3_cell, location_id) VALUES (?, ?::UUID), (?, ?::UUID), ...
   ON CONFLICT (h3_cell, location_id) DO NOTHING;
COMMIT;

Concurrently, many short-lived read transactions against the same tables (each: BEGIN, a SELECT joining location_h3_index to locations, then ROLLBACK unconditionally — we always roll back reads rather than commit them).

Result is under sustained load (~30s per run; total transaction counts varied a lot by configuration, from a couple thousand at low concurrency up to 100,000+ at higher connection-pool sizes)... a write's COMMIT fails with

Invalid Input Error: Skipping beyond end of binary data at position 16

and the automatic rollback-on-failed-commit fails with the identical error, which DuckDB surfaces as:

FATAL Error: Failed to rollback transaction. Cannot continue operation.

Every subsequent query on every connection then fails with a second, related error ("database has been invalidated because of a previous fatal error... must be restarted") until the process restarts.

Notable differences in what we see

• No ST_Union_Agg or any spatial aggregate involved... a plain per-row ST_GeomFromGeoJSON insert is sufficient.
• We do have one explicit CHECKPOINT call elsewhere in our code, but it targets a separate attached database (an auxiliary metrics/request-log store, not the one with the GEOMETRY column) and only runs when there are stale rows to reclaim. In this specific reproduction that condition is never met, so it doesn't fire during the test. If this is checkpoint-related as you suspected, could it be DuckDB's automatic/implicit checkpointing under write volume, not an explicit call from our code??
• Reproduces even with the connection pool capped at a single connection (though many goroutines are contending for it, so transactions are still issued back-to-back at high frequency). So this doesn't look like it requires genuine multi-connection parallelism, just high-frequency transaction churn against a table with a GEOMETRY column.
• "Position 16" is suspicious given our primary key is a 16-byte UUID... wondering if that's coincidental or a real clue.

Happy to provide more detail if useful for narrowing this down.

[Maxxen]

Hello!
Thanks to you both for helping out - I think I've managed to reproduce the issue and am working on a fix. I think it's related to the PRIMARY KEY. There has been changes in DuckDB core to more efficiently move and re-order row groups with indexes when checkpointing, but this interferes with geometry shredding logic which makes strict assumptions about the lifetime of row groups.