ITADN

bug: SQLAlchemy reflection cache is defeated by creating new Inspector instances per call

#3478Openedgarrmondragon 创建于 2026-02-09
kind/BugSQLType/Target
E
edgarrmondragoncommented
### Singer SDK Version 0.53.5 ### Is this a regression? - [ ] Yes ### Python Version NA ### Bug scope Targets (data type handling, batching, SQL object generation, etc.) ### Operating System _No response_ ### Description Three methods in `SQLConnector` create a new `sa.inspect(self._engine)` on every call. Since SQLAlchemy's reflection cache (`info_cache`) is an instance-level dictionary on `Inspector`, each new instance starts with an empty cache and the previous one is garbage collected — completely defeating dialect-level caching optimizations. #### Affected methods - [`table_exists()`](https://github.com/meltano/sdk/blob/c3176a4ec22872993c5542c5b7443f62012650c1/singer_sdk/sql/connector.py#L1206) - [`schema_exists()`](https://github.com/meltano/sdk/blob/c3176a4ec22872993c5542c5b7443f62012650c1/singer_sdk/sql/connector.py#L1217) - [`get_table_columns()`](https://github.com/meltano/sdk/blob/c3176a4ec22872993c5542c5b7443f62012650c1/singer_sdk/sql/connector.py#L1235) — most critical, called frequently during data loading For contrast, [`discover_catalog_entries()`](https://github.com/meltano/sdk/blob/c3176a4ec22872993c5542c5b7443f62012650c1/singer_sdk/sql/connector.py#L1116) already correctly creates a single Inspector and reuses it throughout the method. #### How SQLAlchemy's reflection cache works SQLAlchemy stores reflection results in `Inspector.info_cache`, a plain dictionary. The `@reflection.cache` decorator on dialect methods (e.g., Snowflake's `_get_schema_columns()`) uses this dict to avoid redundant queries. **New Inspector = new empty dict = all cached results lost.** #### Real-world impact (Snowflake) The Snowflake SQLAlchemy dialect optimizes by querying **all columns in a schema** at once (`SELECT ... FROM information_schema.columns WHERE table_schema = :schema`) and caching the result. Subsequent `get_columns()` calls for other tables in the same schema should return from cache with zero queries. Because the SDK creates a throwaway Inspector each time, this optimization is completely negated. This affects any SQL dialect that uses `@reflection.cache` for optimization, not just Snowflake. #### Suggested fix Cache the Inspector instance at the connector level (e.g., a lazy `_inspector` property) so the `info_cache` persists across calls. A `clear_cache()` escape hatch can be provided for edge cases where schema changes during a sync. ### Link to Slack/Linen _No response_
0 条评论