IOManager tests produce `sqlite3.ProgrammingError` Cannot operate on a closed database

[zaneselvans]

When running the io_manager unit tests, the following error crops up sporadically, but doesn't cause the tests to fail.

Apparently this has been happening for a while, but because it wasn't actually causing any tests to fail, we didn't notice. Seems odd. There are a lot of Alembic logging messages that crop up in the same area of the tests as well.

Running the following set of unit tests repeatedly should eventually surface the errors:

pytest test/unit/io_managers_test.py

On some platforms it seems to happen much more frequently than others.

2023-10-27 13:45:35 [   ERROR] sqlalchemy.pool.impl.NullPool:791 Exception during reset or similar
Traceback (most recent call last):
  File "/Users/zane/miniforge3/envs/pudl-dev/lib/python3.11/site-packages/dagster/_core/execution/api.py", line 293, in ephemeral_instance_if_missing
    yield ephemeral_instance
  File "/Users/zane/miniforge3/envs/pudl-dev/lib/python3.11/site-packages/dagster/_core/execution/build_resources.py", line 108, in build_resources
    yield instantiated_resources.build(
GeneratorExit

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/zane/miniforge3/envs/pudl-dev/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 763, in _finalize_fairy
    fairy._reset(pool, transaction_was_reset)
  File "/Users/zane/miniforge3/envs/pudl-dev/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 1038, in _reset
    pool._dialect.do_rollback(self)
  File "/Users/zane/miniforge3/envs/pudl-dev/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 683, in do_rollback
    dbapi_connection.rollback()
sqlite3.ProgrammingError: Cannot operate on a closed database.
2023-10-27 13:45:35 [   ERROR] sqlalchemy.pool.impl.NullPool:791 Exception during reset or similar
Traceback (most recent call last):
  File "/Users/zane/miniforge3/envs/pudl-dev/lib/python3.11/site-packages/dagster/_core/execution/api.py", line 293, in ephemeral_instance_if_missing
    yield ephemeral_instance
  File "/Users/zane/miniforge3/envs/pudl-dev/lib/python3.11/site-packages/dagster/_core/execution/build_resources.py", line 108, in build_resources
    yield instantiated_resources.build(
GeneratorExit

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/zane/miniforge3/envs/pudl-dev/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 763, in _finalize_fairy
    fairy._reset(pool, transaction_was_reset)
  File "/Users/zane/miniforge3/envs/pudl-dev/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 1038, in _reset
    pool._dialect.do_rollback(self)
  File "/Users/zane/miniforge3/envs/pudl-dev/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 683, in do_rollback
    dbapi_connection.rollback()
sqlite3.ProgrammingError: Cannot operate on a closed database.

[jdangerx]

I was frustrated by this recently and did some more digging, here's what I found:

• this appears to be happening after the pytest run completes, and, correspondingly, after the teardown functions have run
• it seems like maybe dagster is trying to clean up the database for its ephemeral instance, after pytest has already cleaned up that database? not quite sure how the dagster instance lifecycle works in test.

[LouisAuneau]

We are facing the same issue. Did you manage to find a workaround or fix ?

[zaneselvans]

We didn't really figure out what was going on here, but I think we tried to be more careful about always using context managers to ensure the DB connections were getting closed in the IO Managers, and we don't have the problem any more!

Actually maybe we can/should close this issue @jdangerx @bendnorman