DavidAmsellem opened a new pull request, #75127: URL: https://github.com/apache/airflow/pull/75127
The asset orphanage pass runs on every scheduler loop and deletes orphaned assets from `asset_active` with a `DELETE` built from a CTE. MySQL-compatible backends whose planner does not support `WITH` in DML (Vitess, PlanetScale) reject that statement with `VT12001: unsupported: WITH expression in DELETE statement`, so the scheduler crash-loops on them. The orphan query now uses a subquery (a derived table) instead of a CTE. The activation query stays a CTE, because it only feeds `SELECT` statements. The new test captures the `DELETE` the orphanage pass sends and checks that, compiled with the MySQL dialect, it has no `WITH` clause, as suggested in the review of #72188. It fails without the change. This picks up #72188, which took the same approach and was closed as stale. closes: #72187 Tested: - The asset orphanage tests in `test_scheduler_job.py` (`-k asset_orphaning`, 4 tests) pass on SQLite, MySQL and Postgres (Breeze). - Static checks pass (`prek`, pre-commit and manual stages). - Core DB tests on MySQL (Breeze) for the `Core`, `Always`, `CLI` and `Serialization` test types: 6,388 tests, 0 failures. I stopped the run before `API` and `Other` finished, so those, the non-DB tests and the docs build were not run locally. Performance (the scheduler loop is performance-sensitive): the `DELETE` with the CTE and with the subquery, timed alternately in the same process, 5,000 assets with 2,500 orphaned, median of 7 runs, rolled back after each run. The control is a `SELECT count(*)` on `asset`. | Backend | CTE (before) | Subquery (after) | Control | |---|---|---|---| | Postgres | 13.15 ms | 13.33 ms | 0.78 ms | | MySQL | 499.28 ms | 498.04 ms | 0.73 ms | On Postgres the `EXPLAIN` plans of the two statements are identical, since Postgres already inlines a CTE that is referenced once. --- ##### Was generative AI tooling used to co-author this PR? - [X] Yes — Claude Code (Opus 5.5) Generated-by: Claude Code (Opus 5.5) following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions) --- * Read the **[Pull Request Guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#pull-request-guidelines)** for more information. Note: commit author/co-author name and email in commits become permanently public when merged. * For fundamental code changes, an Airflow Improvement Proposal ([AIP](https://cwiki.apache.org/confluence/display/AIRFLOW/Airflow+Improvement+Proposals)) is needed. * When adding dependency, check compliance with the [ASF 3rd Party License Policy](https://www.apache.org/legal/resolved.html#category-x). * For significant user-facing changes create newsfragment: `{pr_number}.significant.rst`, in [airflow-core/newsfragments](https://github.com/apache/airflow/tree/main/airflow-core/newsfragments). You can add this file in a follow-up commit after the PR is created so you know the PR number. 🤖 Generated with [Claude Code](https://claude.com/claude-code) -- 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]
