pathwaycom/pathway · error · ValueError

detach_between_batches=True cannot be used with database=":m

Error message

detach_between_batches=True cannot be used with database=":memory:": an in-memory DuckDB database is dropped when its last connection closes, so all data would be lost after every batch. Use an on-disk database file instead.

What it means

Raised by pw.io.duckdb.write when detach_between_batches=True is combined with database=":memory:". An in-memory DuckDB database is dropped when its last connection closes, so detaching after every batch would silently lose all written data — the connector rejects the combination instead of producing an empty database.

Source

Thrown at python/pathway/io/duckdb/__init__.py:278

        # and can place the insertion before the deletion, so the upsert is
        # immediately wiped by the delete — silently losing rows. sort_by has no
        # effect on a snapshot's final (unordered, keyed) state anyway, so reject
        # the combination instead of corrupting the output.
        raise ValueError(
            "sort_by cannot be used with the snapshot table type: a snapshot "
            "reflects the current state of the table regardless of the order "
            "changes are applied within a minibatch, and reordering would corrupt "
            "the upsert/delete sequence. Remove sort_by, or use "
            'output_table_type="stream_of_changes".'
        )

    database_str = fspath(database)
    _reject_directory_path(database_str)

    if detach_between_batches and database_str == IN_MEMORY_DATABASE:
        # An in-memory database ceases to exist when its last connection closes,
        # so detaching after every batch would silently drop all written data.
        raise ValueError(
            'detach_between_batches=True cannot be used with database=":memory:": '
            "an in-memory DuckDB database is dropped when its last connection "
            "closes, so all data would be lost after every batch. Use an on-disk "
            "database file instead."
        )

    value_fields = _format_output_value_fields(table)

    # DuckDB matches identifiers case-insensitively, so two schema columns whose
    # names differ only in case would make CREATE TABLE fail with a raw
    # "Column with name ... already exists" catalog error. Surface it here.
    case_groups: dict[str, list[str]] = {}
    for field in value_fields:
        case_groups.setdefault(field.name.lower(), []).append(field.name)
    case_collisions = [
        sorted(names) for names in case_groups.values() if len(names) > 1
    ]
    if case_collisions:

View on GitHub (pinned to fa2f74a464)

Solutions

Use an on-disk database file: pw.io.duckdb.write(t, table_name="t", database="out.duckdb", detach_between_batches=True).
Or keep database=":memory:" and drop detach_between_batches=True (leave it False/None).

Example fix

# before
pw.io.duckdb.write(t, table_name="t", database=":memory:", detach_between_batches=True)

# after
pw.io.duckdb.write(t, table_name="t", database="out.duckdb", detach_between_batches=True)

Defensive patterns

Strategy: validation

Validate before calling

if detach_between_batches and database == ":memory:":
    raise ValueError("in-memory database cannot survive detach_between_batches")

Prevention

Use a temp file (tempfile.NamedTemporaryFile) instead of :memory: for tests that need detach_between_batches.
Keep detach_between_batches out of default configs; enable it only with an on-disk path.

When it happens

Trigger: pw.io.duckdb.write(t, table_name="t", database=":memory:", detach_between_batches=True).

Common situations: Prototyping with :memory: and enabling detach_between_batches (often to free locks or work around file-lock issues seen on disk); copying settings between configs when moving from file-based to in-memory testing.

Related errors

AI-assisted analysis of pathwaycom/pathway@fa2f74a464 (2026-08-15). Data as JSON: /api/errors/43c934ac948fc6c5. Report an issue: GitHub.