Bug Report: Inconsistent duckdb.Error - Internal MotherDuck Error with Repro Script Provided

Bug Report: Inconsistent duckdb.Error - Internal MotherDuck Error with Repro Script Provided | MotherDuck Community

10 comments

· Sorted by Oldest

Note that this script was actually me (well, claude) trying to reproduce a DIFFERENT motherduck bug (you may see this bug later if I can get a reliable repro for THAT one 😉). So ignore the comments, but this did give me (on a pulse instance):

$motherduck_token=... uv run 01_truncate_oom.py reusing existing table with 81,000,000 rows built 113,000,000/400,000,000 rows built 145,000,000/400,000,000 rows built 177,000,000/400,000,000 rows built 209,000,000/400,000,000 rows built 241,000,000/400,000,000 rows built 273,000,000/400,000,000 rows built 305,000,000/400,000,000 rows built 337,000,000/400,000,000 rows built 369,000,000/400,000,000 rows Traceback (most recent call last): File "/Users/nc/code/01_truncate_oom.py", line 109, in <module> main() ~~~~^^ File "/Users/nc/code/01_truncate_oom.py", line 91, in main con.sql(f"INSERT INTO big SELECT {sel} FROM range({done}, {hi}) t(i)") ~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ _duckdb.Error: You've encountered an internal MotherDuck error. You can help us diagnose and fix the issue by contacting support via slack or support@motherduck.com and referencing error ID: 1307d13d.

#!/usr/bin/env -S uv run --script
# /// script
# requires-python = ">=3.12"
# dependencies = ["duckdb>=1.5.2"]
# ///
"""
Bug: TRUNCATE TABLE on a large-but-ordinary table runs the duckling out of
memory, so a table can grow past the point where it can ever be truncated.

This script builds a synthetic 69-column table (the shape of our real table)
with chunked INSERT ... FROM range() statements -- which all succeed -- and
then runs TRUNCATE TABLE on it. On a Pulse duckling the TRUNCATE fails with:

    duckdb.duckdb.OutOfMemoryException: Out of Memory Error: failed to pin
    block of size 256.0 KiB (7.4 GiB/953.0 MiB used)

    Consider using a larger duckling size than Pulse or optimizing your query
    to reduce memory usage.

We originally hit this on a real 1.07-billion-row, 69-column table in
database 'fec' (TRUNCATE failed with exactly the message above, June 10-11
2026). DROP TABLE on the same table works fine, so the workaround is
DROP + CREATE, but TRUNCATE (or an unfiltered DELETE) presumably should not
need memory proportional to table size.

Usage: set the motherduck_token env var, then run this file.
The script creates database 'bug_repros' and uses ~30-40 GB of storage. The
table is left in place after the run (so it can be reused on the next run
without rebuilding); re-runs resume building from the existing row count.
Drop it manually when done: DROP TABLE bug_repros.big
"""

import os

import duckdb

DATABASE = "bug_repros"
ROWS = 400_000_000
# Each INSERT is a full MotherDuck transaction commit, so fewer/bigger chunks
# build much faster. INSERT ... FROM range() streams, so memory does not grow
# linearly with chunk size, but if a chunk still OOMs we halve and retry.
MAX_CHUNK = 32_000_000
MIN_CHUNK = 1_000_000

# 69 columns, mostly strings of varying cardinality, like a typical wide
# fact table. (Our real table mixes VARCHAR, enums, DECIMAL, and DATE.)
# The first few strings are high-entropy so the table occupies realistic
# storage (TRUNCATE's memory appetite appears to scale with the table's
# storage size, not just its row count).
N_VARCHAR = 45
N_HIGH_ENTROPY = 5
N_BIGINT = 12
N_DECIMAL = 6
N_DATE = 6


def column_expressions() -> str:
    cols = []
    for j in range(N_HIGH_ENTROPY):
        cols.append(f"to_hex(hash(i + {j})) AS s{j}")
    for j in range(N_HIGH_ENTROPY, N_VARCHAR):
        cardinality = 10 ** (1 + j % 6)  # 10 .. 1_000_000 distinct values
        cols.append(f"concat('s{j}_', i % {cardinality}) AS s{j}")
    for j in range(N_BIGINT):
        cols.append(f"(i * {j + 3}) % 1000000 AS n{j}")
    for j in range(N_DECIMAL):
        cols.append(f"((i % 100000) / 100.0)::DECIMAL(14,2) AS d{j}")
    for j in range(N_DATE):
        cols.append(f"(DATE '2000-01-01' + ((i + {j}) % 9000)::INT) AS t{j}")
    return ",\n        ".join(cols)


def main() -> None:
    md_token = os.environ.get("motherduck_token")
    if not md_token:
        raise RuntimeError("Usage: motherduck_token=... uv run 01_truncate_oom.py")
    con = duckdb.connect("md:")
    con.sql(f"CREATE DATABASE IF NOT EXISTS {DATABASE}")
    con.sql(f"USE {DATABASE}")
    sel = column_expressions()
    con.sql(
        f"CREATE TABLE IF NOT EXISTS big AS SELECT {sel} FROM range(1) t(i) LIMIT 0"
    )
    done = con.sql("SELECT count(*) FROM big").fetchone()[0]
    if done:
        print(f"reusing existing table with {done:,} rows", flush=True)
    chunk = MAX_CHUNK
    while done < ROWS:
        hi = min(done + chunk, ROWS)
        try:
            con.sql(f"INSERT INTO big SELECT {sel} FROM range({done}, {hi}) t(i)")
        except duckdb.OutOfMemoryException:
            if chunk <= MIN_CHUNK:
                raise
            chunk //= 2
            print(f"insert OOMed; retrying with chunk size {chunk:,}", flush=True)
            continue
        done = hi
        print(f"built {done:,}/{ROWS:,} rows", flush=True)
    print(con.sql("SELECT count(*) AS rows_built FROM big"))

    print("running TRUNCATE TABLE big ...", flush=True)
    con.sql("TRUNCATE TABLE big")  # <-- OOMs on a Pulse duckling
    print("TRUNCATE succeeded: bug NOT reproduced at this scale")
    print("note: table is now empty; next run will rebuild it")


if __name__ == "__main__":
    main()

Aedrian E.
·
·
Hey Nick! Thanks for reaching out, I'm having a look and will try your repro!
Aedrian E.
·
·
I ran the script on my end, but didn't have any issues with doing TRUNCATE TABLE big . However, I understand your issue is with the memory usage during truncate. I'll inspect if memory usage on TRUNCATE TABLE is directly proportional to the number of rows on the table. I'll get back to you next week with my findings and confirm if it's a bug
Nick C.
·
·
Aedrian E. did you see how I am saying there are two different errors:
1.
_duckdb.Error: You've encountered an internal MotherDuck error.
2.
duckdb.duckdb.OutOfMemoryException: Out of Memory Error: failed to pin block of size 256.0 KiB (7.4 GiB/953.0 MiB used)
Nick C.
·
·
I originally wrote this repro to look for 2, but during that testing I actually encountered 1. So I'm not sure if this script actually CAN reproduce 2, I only know it can reproduce 1.
Nick C.
·
·
Of course, the two errors may be related to the same underlying cause of OOM. But when you say
I ran the script on my end, but didn't have any issues with doing doing TRUNCATE TABLE big
I want to reiterate that I ALSO never had issues doing that (error 2). As you can see in my original log, the error 1 happened during the INSERT.
Aedrian E.
·
·
Hey Nick C., thanks for the explanation. I am clear on these two issues, so let's tackle them separately. Issue#1: I wasn't able to reproduce the issue with the script so I inspected your duckling around the time the error occurred. The INSERT INTO big SELECT ... query ran for 24 minutes before it got interrupted server-side because it had lost its connection with the client. The client is expected to send a keepalive every 30 seconds. Could you re-run the script and see if you're able to get the error again? Issue#2: I'm able to reproduce this OOM during a TRUNCATE of a table with an extremely large number of rows. I'm working with our team internally to understand the specifics of this issue before raising a bug. We'll keep you updated
Aedrian E.
·
·
Hey Nick C., I wanted to give you an update on Issue #2. In duckdb, TRUNCATE is an alias to DELETE FROM , therefore it is possible to run into an OOM when there is not enough memory allocated to the duckling. Given that, the workaround that you have (DROP + CREATE) is our alternative at the moment
Nick C.
·
·
Thank you! re #1, I'm guessing this was network instability on my end. I was on bad hotspot at the time. So I think that makes sense for the query to crash (the 30s heartbeat seems reasonable), but the error it gave is misleading. It should raise a network error, not the outright misleading "duckdb.Error: You've encountered an internal MotherDuck error." Could you fix this on your end? re #2, sounds good to me. Consider it a wishlist item (that I don't expect you to solve, I bet it requires a general duckdb solution) that the duckdb.duckdb.OutOfMemoryException: Out of Memory Error: failed to pin block of size 256.0 KiB (7.4 GiB/953.0 MiB used) would include the actual SQL statement that was being executed, so it would be easier to track down what part of the users query is actually problematic.
Aedrian E.
·
·
You're welcome! I agree, errors arising out of network instability can be misleading. We have this as part of our roadmap. For the OOM being able to print the actual statement executed, yes, it is more of a duckdb solution. I've also submitted a feature request internally to consider having a "real" TRUNCATE, one that does not just do a DELETE FROM under the hood and does not require a lot of memory to execute.
👍1
❤️

Nick C.

$motherduck_token=... uv run 01_truncate_oom.py reusing existing table with 81,000,000 rows built 113,000,000/400,000,000 rows built 145,000,000/400,000,000 rows built 177,000,000/400,000,000 rows built 209,000,000/400,000,000 rows built 241,000,000/400,000,000 rows built 273,000,000/400,000,000 rows built 305,000,000/400,000,000 rows built 337,000,000/400,000,000 rows built 369,000,000/400,000,000 rows Traceback (most recent call last): File "/Users/nc/code/01_truncate_oom.py", line 109, in <module> main() ~~~~^^ File "/Users/nc/code/01_truncate_oom.py", line 91, in main con.sql(f"INSERT INTO big SELECT {sel} FROM range({done}, {hi}) t(i)") ~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ _duckdb.Error: You've encountered an internal MotherDuck error. You can help us diagnose and fix the issue by contacting support via slack or support@motherduck.com and referencing error ID: 1307d13d.

#!/usr/bin/env -S uv run --script
# /// script
# requires-python = ">=3.12"
# dependencies = ["duckdb>=1.5.2"]
# ///
"""
Bug: TRUNCATE TABLE on a large-but-ordinary table runs the duckling out of
memory, so a table can grow past the point where it can ever be truncated.

This script builds a synthetic 69-column table (the shape of our real table)
with chunked INSERT ... FROM range() statements -- which all succeed -- and
then runs TRUNCATE TABLE on it. On a Pulse duckling the TRUNCATE fails with:

    duckdb.duckdb.OutOfMemoryException: Out of Memory Error: failed to pin
    block of size 256.0 KiB (7.4 GiB/953.0 MiB used)

    Consider using a larger duckling size than Pulse or optimizing your query
    to reduce memory usage.

We originally hit this on a real 1.07-billion-row, 69-column table in
database 'fec' (TRUNCATE failed with exactly the message above, June 10-11
2026). DROP TABLE on the same table works fine, so the workaround is
DROP + CREATE, but TRUNCATE (or an unfiltered DELETE) presumably should not
need memory proportional to table size.

Usage: set the motherduck_token env var, then run this file.
The script creates database 'bug_repros' and uses ~30-40 GB of storage. The
table is left in place after the run (so it can be reused on the next run
without rebuilding); re-runs resume building from the existing row count.
Drop it manually when done: DROP TABLE bug_repros.big
"""

import os

import duckdb

DATABASE = "bug_repros"
ROWS = 400_000_000
# Each INSERT is a full MotherDuck transaction commit, so fewer/bigger chunks
# build much faster. INSERT ... FROM range() streams, so memory does not grow
# linearly with chunk size, but if a chunk still OOMs we halve and retry.
MAX_CHUNK = 32_000_000
MIN_CHUNK = 1_000_000

# 69 columns, mostly strings of varying cardinality, like a typical wide
# fact table. (Our real table mixes VARCHAR, enums, DECIMAL, and DATE.)
# The first few strings are high-entropy so the table occupies realistic
# storage (TRUNCATE's memory appetite appears to scale with the table's
# storage size, not just its row count).
N_VARCHAR = 45
N_HIGH_ENTROPY = 5
N_BIGINT = 12
N_DECIMAL = 6
N_DATE = 6


def column_expressions() -> str:
    cols = []
    for j in range(N_HIGH_ENTROPY):
        cols.append(f"to_hex(hash(i + {j})) AS s{j}")
    for j in range(N_HIGH_ENTROPY, N_VARCHAR):
        cardinality = 10 ** (1 + j % 6)  # 10 .. 1_000_000 distinct values
        cols.append(f"concat('s{j}_', i % {cardinality}) AS s{j}")
    for j in range(N_BIGINT):
        cols.append(f"(i * {j + 3}) % 1000000 AS n{j}")
    for j in range(N_DECIMAL):
        cols.append(f"((i % 100000) / 100.0)::DECIMAL(14,2) AS d{j}")
    for j in range(N_DATE):
        cols.append(f"(DATE '2000-01-01' + ((i + {j}) % 9000)::INT) AS t{j}")
    return ",\n        ".join(cols)


def main() -> None:
    md_token = os.environ.get("motherduck_token")
    if not md_token:
        raise RuntimeError("Usage: motherduck_token=... uv run 01_truncate_oom.py")
    con = duckdb.connect("md:")
    con.sql(f"CREATE DATABASE IF NOT EXISTS {DATABASE}")
    con.sql(f"USE {DATABASE}")
    sel = column_expressions()
    con.sql(
        f"CREATE TABLE IF NOT EXISTS big AS SELECT {sel} FROM range(1) t(i) LIMIT 0"
    )
    done = con.sql("SELECT count(*) FROM big").fetchone()[0]
    if done:
        print(f"reusing existing table with {done:,} rows", flush=True)
    chunk = MAX_CHUNK
    while done < ROWS:
        hi = min(done + chunk, ROWS)
        try:
            con.sql(f"INSERT INTO big SELECT {sel} FROM range({done}, {hi}) t(i)")
        except duckdb.OutOfMemoryException:
            if chunk <= MIN_CHUNK:
                raise
            chunk //= 2
            print(f"insert OOMed; retrying with chunk size {chunk:,}", flush=True)
            continue
        done = hi
        print(f"built {done:,}/{ROWS:,} rows", flush=True)
    print(con.sql("SELECT count(*) AS rows_built FROM big"))

    print("running TRUNCATE TABLE big ...", flush=True)
    con.sql("TRUNCATE TABLE big")  # <-- OOMs on a Pulse duckling
    print("TRUNCATE succeeded: bug NOT reproduced at this scale")
    print("note: table is now empty; next run will rebuild it")


if __name__ == "__main__":
    main()

Aedrian E.
·
·
Hey Nick! Thanks for reaching out, I'm having a look and will try your repro!
Aedrian E.
·
·
I ran the script on my end, but didn't have any issues with doing TRUNCATE TABLE big . However, I understand your issue is with the memory usage during truncate. I'll inspect if memory usage on TRUNCATE TABLE is directly proportional to the number of rows on the table. I'll get back to you next week with my findings and confirm if it's a bug
Nick C.
·
·
Aedrian E. did you see how I am saying there are two different errors:
1.
_duckdb.Error: You've encountered an internal MotherDuck error.
2.
duckdb.duckdb.OutOfMemoryException: Out of Memory Error: failed to pin block of size 256.0 KiB (7.4 GiB/953.0 MiB used)
Nick C.
·
·
I originally wrote this repro to look for 2, but during that testing I actually encountered 1. So I'm not sure if this script actually CAN reproduce 2, I only know it can reproduce 1.
Nick C.
·
·
Of course, the two errors may be related to the same underlying cause of OOM. But when you say
I ran the script on my end, but didn't have any issues with doing doing TRUNCATE TABLE big
I want to reiterate that I ALSO never had issues doing that (error 2). As you can see in my original log, the error 1 happened during the INSERT.
Aedrian E.
·
·
Hey Nick C., thanks for the explanation. I am clear on these two issues, so let's tackle them separately. Issue#1: I wasn't able to reproduce the issue with the script so I inspected your duckling around the time the error occurred. The INSERT INTO big SELECT ... query ran for 24 minutes before it got interrupted server-side because it had lost its connection with the client. The client is expected to send a keepalive every 30 seconds. Could you re-run the script and see if you're able to get the error again? Issue#2: I'm able to reproduce this OOM during a TRUNCATE of a table with an extremely large number of rows. I'm working with our team internally to understand the specifics of this issue before raising a bug. We'll keep you updated
Aedrian E.
·
·
Hey Nick C., I wanted to give you an update on Issue #2. In duckdb, TRUNCATE is an alias to DELETE FROM , therefore it is possible to run into an OOM when there is not enough memory allocated to the duckling. Given that, the workaround that you have (DROP + CREATE) is our alternative at the moment
Nick C.
·
·
Thank you! re #1, I'm guessing this was network instability on my end. I was on bad hotspot at the time. So I think that makes sense for the query to crash (the 30s heartbeat seems reasonable), but the error it gave is misleading. It should raise a network error, not the outright misleading "duckdb.Error: You've encountered an internal MotherDuck error." Could you fix this on your end? re #2, sounds good to me. Consider it a wishlist item (that I don't expect you to solve, I bet it requires a general duckdb solution) that the duckdb.duckdb.OutOfMemoryException: Out of Memory Error: failed to pin block of size 256.0 KiB (7.4 GiB/953.0 MiB used) would include the actual SQL statement that was being executed, so it would be easier to track down what part of the users query is actually problematic.
Aedrian E.
·
·
You're welcome! I agree, errors arising out of network instability can be misleading. We have this as part of our roadmap. For the OOM being able to print the actual statement executed, yes, it is more of a duckdb solution. I've also submitted a feature request internally to consider having a "real" TRUNCATE, one that does not just do a DELETE FROM under the hood and does not require a lot of memory to execute.
👍1
❤️