Error `InvalidColumnNameException["_id" Cannot write to system column]`

[amotl]

Problem

A testing slot on CI recently started failing. It uses CrateDB nightly, so it's likely related to a recent change.
The first occurrance was three days ago, i.e. Wed, 22 Oct 2025 03:26 GMT.

FAILED tests/adapter/test_pymongo.py::test_pymongo_insert_many - crate.client.exceptions.ProgrammingError: InvalidColumnNameException["_id" Cannot write to system column]
FAILED tests/adapter/test_pymongo.py::test_example_program - crate.client.exceptions.ProgrammingError: InvalidColumnNameException["_id" Cannot write to system column]
FAILED tests/adapter/test_pymongo.py::test_pymongo_tutorial - crate.client.exceptions.ProgrammingError: InvalidColumnNameException["_id" Cannot write to system column]

-- https://github.com/crate/cratedb-toolkit/actions/runs/18704389768/job/53339592788

Details

Commands

Those let you debug what's going on.

uv venv --python 3.12 --seed .venv312
source .venv312/bin/activate
uv pip install --upgrade --editable='.[pymongo,test]'
pytest --no-cov -m pymongo

Drill down

export CRATEDB_CLUSTER_URL=crate://crate@localhost:53234/
ctk tail -n 3 --follow --format=log-pretty sys.jobs_log

pytest --no-cov tests/adapter/test_pymongo.py -k test_pymongo_insert_many

Regression

CREATE TABLE "testdrive-data".foobar (x FLOAT, y FLOAT) WITH (column_policy = 'dynamic');
INSERT INTO "testdrive-data".foobar ("_id", x, y) VALUES (666, 42.42, 84.84);

InvalidColumnNameException["_id" Cannot write to system column]

References

• Likely related: SQL DDL error InvalidColumnNameException["_foo" conflicts with system column pattern] crate#15161

[amotl]

We need to adjust the PyMongo adapter code to ignore / not forward the _id column to CrateDB, i.e. make it read-only in its role as a surrogate to MongoDB's object id. When sending the column to CrateDB for write operations, it will raise errors going forward. Thanks @matriv.

• INSERT error with column_policy = 'dynamic' DDL and _id column: InvalidColumnNameException["_id" Cannot write to system column] crate#18587 (comment)