kaxil opened a new pull request, #75327: URL: https://github.com/apache/airflow/pull/75327
`DbApiHook.inspector` keeps a database connection open as long as the hook lives. For a task, that means until the worker process exits. SQLAlchemy's `inspect(engine)` connects when it builds the Inspector and on each reflection call, and hands each connection back to the engine's default `QueuePool`, which keeps it open. Since #62594 the inspector is a `cached_property`, so the engine and its pool live as long as the hook, and nothing calls `dispose()`. This showed up with common.ai's `SQLToolset` against Snowflake. After the agent called `list_tables` and `get_schema`, snowflake-connector-python 5.0 logged `Connection was not explicitly closed before process exit` at worker exit and then closed the session from its atexit handler. The warning says that auto-cleanup will be disabled by default in a future version. After that, the session would stay open on the server until Snowflake's idle timeout. The `query` tool isn't involved, because it goes through `run()`, which closes its connection. The fix builds the inspector's engine with `poolclass=NullPool`, so each reflection call closes its connection when it finishes. `run()` and `get_records()` already work this way. The inspector itself stays cached, so #62594's one engine per hook is kept. All in-tree `get_sqlalchemy_engine` overrides (Snowflake, Redshift, BigQuery, Spanner, ODBC, JDBC) pass `engine_kwargs` through to `create_engine`, so they all get the fix. I reproduced it with a file-backed SQLite connection. After SQLToolset's `list_tables`, `get_schema` and `query`, the pool showed 1 DBAPI connection opened and 0 closed before the fix, and 3 opened and 3 closed after it. The new test fails without the change with the same symptom. **Why fix it in common.sql and not in SQLToolset.** The leak isn't specific to the toolset. `Dialect.get_column_names` and `get_primary_keys` read `hook.inspector`, so `insert_rows` resolving its target fields, the Postgres and Db2 upsert paths, and `LLMSchemaCompareOperator` all hold the same connection open. Disposing the engine in SQLToolset, either per call or from the toolset's `__aexit__`, would fix one caller and leave the rest. **Cost.** Each reflection call now opens its own connection instead of reusing a pooled one. On Snowflake that means one login per call. A task makes a handful of reflection calls, and the Inspector caches results per object, so the cost is a few extra round trips per task. `DataFusionToolset` doesn't use `hook.inspector`, since it runs an in-process DataFusion engine, so it isn't affected. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
