You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "YoannAbriel (via GitHub)" <gi...@apache.org> on 2026/03/16 14:08:58 UTC

[PR] fix: skip dag_version rows still referenced by task_instance in db clean [airflow]

YoannAbriel opened a new pull request, #63728:
URL: https://github.com/apache/airflow/pull/63728

   `airflow db clean` crashes with `ForeignKeyViolation` when deleting `dag_version` rows that are still referenced by `task_instance` (which has `ondelete=RESTRICT` on `dag_version_id`).
   
   This happens because each table is cleaned based on its own recency column — `dag_version` uses `created_at` while `task_instance` uses `start_date`. A dag_version created before the cutoff can still be referenced by task instances created after it, so the dependent-table cleanup doesn't remove those TIs.
   
   Added `fk_check_columns` to `_TableConfig` — a list of `(child_table, child_fk_col, parent_pk_col)` tuples. When set, `_build_query` adds `NOT EXISTS` subqueries to skip rows still referenced by surviving child rows. Applied it to `dag_version` for the `task_instance.dag_version_id` FK.
   
   Added a test that reproduces the exact scenario: dag_version created before cutoff, task_instance referencing it created after cutoff — cleanup should skip that dag_version instead of crashing.
   
   Closes: #63703
   
   <!-- SPDX-License-Identifier: Apache-2.0
         https://www.apache.org/licenses/LICENSE-2.0 -->
   
   <!--
   Thank you for contributing!
   
   Please provide above a brief description of the changes made in this pull request.
   Write a good git commit message following this guide: http://chris.beams.io/posts/git-commit/
   
   Please make sure that your code changes are covered with tests.
   And in case of new features or big changes remember to adjust the documentation.
   
   Feel free to ping (in general) for the review if you do not see reaction for a few days
   (72 Hours is the minimum reaction time you can expect from volunteers) - we sometimes miss notifications.
   
   In case of an existing issue, reference it using one of the following:
   
   * closes: #ISSUE
   * related: #ISSUE
   -->
   
   ---
   
   ##### Was generative AI tooling used to co-author this PR?
   
   - [X] Yes — Claude Code (Opus 4, claude-opus-4-6)
   
   Generated-by: Claude Code (Opus 4, claude-opus-4-6) 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.
   


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] fix: skip dag_version rows still referenced by task_instance in db clean [airflow]

Posted by "potiuk (via GitHub)" <gi...@apache.org>.
potiuk commented on PR #63728:
URL: https://github.com/apache/airflow/pull/63728#issuecomment-4383004708

   @YoannAbriel This draft PR has been inactive for 49 days since the last triage comment and no response from the author. Closing to keep the queue clean.
   
   You are welcome to reopen this PR when you resume work, or to open a new one addressing the issues previously raised. There is no rush — take your time.
   
   ---
   
   _Note: This comment was drafted by an AI-assisted triage tool and may contain mistakes. Once you have addressed the points above, an Apache Airflow maintainer — a real person — will take the next look at your PR. We use this [two-stage triage process](https://github.com/apache/airflow/blob/main/contributing-docs/25_maintainer_pr_triage.md#why-the-first-pass-is-automated) so that our maintainers' limited time is spent where it matters most: the conversation with you._
   


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] fix: skip dag_version rows still referenced by task_instance in db clean [airflow]

Posted by "potiuk (via GitHub)" <gi...@apache.org>.
potiuk commented on PR #63728:
URL: https://github.com/apache/airflow/pull/63728#issuecomment-4071892428

   @YoannAbriel This PR has been converted to **draft** because it does not yet meet our [Pull Request quality criteria](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#pull-request-quality-criteria).
   
   **Issues found:**
   - :x: **Provider tests**: Failing: Special tests / Pendulum2 test: providers / All-prov:Pendulum2-Postgres:14:3.10:-amazon,celer...standard. Run provider tests with `breeze run pytest <provider-test-path> -xvs`. See [Provider tests docs](https://github.com/apache/airflow/blob/main/contributing-docs/12_provider_distributions.rst).
   - :x: **Other failing CI checks**: Failing: Postgres tests: core / DB-core:Postgres:14:3.10:API...Serialization, MySQL tests: core / DB-core:MySQL:8.0:3.10:API...Serialization, Sqlite tests: core / DB-core:Sqlite:3.10:API...Serialization, Special tests / Min SQLAlchemy test: core / DB-core:MinSQLAlchemy-Postgres:14:3.10:API...Serialization, Special tests / Latest SQLAlchemy test: core / DB-core:LatestSQLAlchemy-Postgres:14:3.10:API...Serialization (+3 more). Run `prek run --from-ref main` locally to reproduce. See [static checks docs](https://github.com/apache/airflow/blob/main/contributing-docs/08_static_code_checks.rst).
   
   > **Note:** Your branch is **19 commits behind `main`**. Some check failures may be caused by changes in the base branch rather than by your PR. Please rebase your branch and push again to get up-to-date CI results.
   
   **What to do next:**
   - The comment informs you what you need to do.
   - Fix each issue, then mark the PR as "Ready for review" in the GitHub UI - but only after making sure that all the issues are fixed.
   - There is no rush — take your time and work at your own pace. We appreciate your contribution and are happy to wait for updates.
   - Maintainers will then proceed with a normal review.
   
   Converting a PR to draft is **not** a rejection — it is an invitation to bring the PR up to the project's standards so that maintainer review time is spent productively. There is no rush — take your time and work at your own pace. We appreciate your contribution and are happy to wait for updates. If you have questions, feel free to ask on the [Airflow Slack](https://s.apache.org/airflow-slack).


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] fix: skip dag_version rows still referenced by task_instance in db clean [airflow]

Posted by "Copilot (via GitHub)" <gi...@apache.org>.
Copilot commented on code in PR #63728:
URL: https://github.com/apache/airflow/pull/63728#discussion_r3066477950


##########
airflow-core/tests/unit/utils/test_db_cleanup.py:
##########
@@ -422,6 +422,65 @@ def test_cleanup_with_dag_id_filtering(self, dag_ids, exclude_dag_ids, expected_
                 f"Expected {expected_remaining_dag_ids} to remain, but got {remaining_dag_ids}"
             )
 
+    def test_dag_version_cleanup_skips_referenced_rows(self):
+        """
+        Verify that dag_version rows still referenced by task_instance are not deleted,
+        preventing ForeignKeyViolation (issue #63703).
+
+        Scenario: dag_version created before the cutoff, but a task_instance created
+        after the cutoff still references it via dag_version_id (RESTRICT FK).
+        """
+        base_date = pendulum.DateTime(2022, 1, 1, tzinfo=pendulum.timezone("UTC"))
+
+        with create_session() as session:
+            bundle_name = "testing"
+            session.add(DagBundleModel(name=bundle_name))
+            session.flush()
+
+            dag_id = "test-dag-version-fk"
+            dag = DAG(dag_id=dag_id)
+            dm = DagModel(dag_id=dag_id, bundle_name=bundle_name)
+            session.add(dm)
+            SerializedDagModel.write_dag(LazyDeserializedDAG.from_dag(dag), bundle_name=bundle_name)
+            dag_version = DagVersion.get_latest_version(dag.dag_id)

Review Comment:
   This test intends `dag_version.created_at` to be before `clean_before_date`, but `created_at` is never set/frozen—so it will typically be ‘now’ and therefore *after* the cutoff, meaning the row wouldn’t be eligible for deletion in the first place. To ensure the test actually reproduces the FK-violation scenario, freeze time for the dag_version creation or explicitly set `dag_version.created_at` to `base_date` (and assert the precondition `dag_version.created_at < clean_before_date`).



##########
airflow-core/tests/unit/utils/test_db_cleanup.py:
##########
@@ -422,6 +422,65 @@ def test_cleanup_with_dag_id_filtering(self, dag_ids, exclude_dag_ids, expected_
                 f"Expected {expected_remaining_dag_ids} to remain, but got {remaining_dag_ids}"
             )
 
+    def test_dag_version_cleanup_skips_referenced_rows(self):
+        """
+        Verify that dag_version rows still referenced by task_instance are not deleted,
+        preventing ForeignKeyViolation (issue #63703).
+
+        Scenario: dag_version created before the cutoff, but a task_instance created
+        after the cutoff still references it via dag_version_id (RESTRICT FK).
+        """
+        base_date = pendulum.DateTime(2022, 1, 1, tzinfo=pendulum.timezone("UTC"))

Review Comment:
   This test intends `dag_version.created_at` to be before `clean_before_date`, but `created_at` is never set/frozen—so it will typically be ‘now’ and therefore *after* the cutoff, meaning the row wouldn’t be eligible for deletion in the first place. To ensure the test actually reproduces the FK-violation scenario, freeze time for the dag_version creation or explicitly set `dag_version.created_at` to `base_date` (and assert the precondition `dag_version.created_at < clean_before_date`).



##########
airflow-core/tests/unit/utils/test_db_cleanup.py:
##########
@@ -422,6 +422,65 @@ def test_cleanup_with_dag_id_filtering(self, dag_ids, exclude_dag_ids, expected_
                 f"Expected {expected_remaining_dag_ids} to remain, but got {remaining_dag_ids}"
             )
 
+    def test_dag_version_cleanup_skips_referenced_rows(self):
+        """
+        Verify that dag_version rows still referenced by task_instance are not deleted,
+        preventing ForeignKeyViolation (issue #63703).
+
+        Scenario: dag_version created before the cutoff, but a task_instance created
+        after the cutoff still references it via dag_version_id (RESTRICT FK).
+        """
+        base_date = pendulum.DateTime(2022, 1, 1, tzinfo=pendulum.timezone("UTC"))
+
+        with create_session() as session:
+            bundle_name = "testing"
+            session.add(DagBundleModel(name=bundle_name))
+            session.flush()
+
+            dag_id = "test-dag-version-fk"
+            dag = DAG(dag_id=dag_id)
+            dm = DagModel(dag_id=dag_id, bundle_name=bundle_name)
+            session.add(dm)
+            SerializedDagModel.write_dag(LazyDeserializedDAG.from_dag(dag), bundle_name=bundle_name)
+            dag_version = DagVersion.get_latest_version(dag.dag_id)
+
+            # Create a dag_run and task_instance with start_date AFTER the cutoff,
+            # but referencing the old dag_version (created before cutoff).
+            run_start = base_date.add(days=20)
+            dag_run = DagRun(
+                dag.dag_id,
+                run_id="run_after_cutoff",
+                run_type=DagRunType.MANUAL,
+                start_date=run_start,
+            )
+            ti = create_task_instance(
+                PythonOperator(task_id="dummy-task", python_callable=print),
+                run_id=dag_run.run_id,
+                dag_version_id=dag_version.id,
+            )
+            ti.dag_id = dag.dag_id
+            ti.start_date = run_start
+            session.add(dag_run)
+            session.add(ti)
+            session.commit()
+
+            # Clean with a cutoff that is after the dag_version creation but before
+            # the task_instance start_date.
+            clean_before_date = base_date.add(days=10)

Review Comment:
   This test intends `dag_version.created_at` to be before `clean_before_date`, but `created_at` is never set/frozen—so it will typically be ‘now’ and therefore *after* the cutoff, meaning the row wouldn’t be eligible for deletion in the first place. To ensure the test actually reproduces the FK-violation scenario, freeze time for the dag_version creation or explicitly set `dag_version.created_at` to `base_date` (and assert the precondition `dag_version.created_at < clean_before_date`).



##########
airflow-core/src/airflow/utils/db_cleanup.py:
##########
@@ -373,6 +382,21 @@ def _build_query(
         )
         conditions.append(column(max_date_col_name).is_(None))
     query = query.where(and_(*conditions))
+
+    # Exclude rows still referenced by child tables (FK with RESTRICT).
+    fk_check_columns = kwargs.get("fk_check_columns")
+    if fk_check_columns:
+        for child_table_name, child_fk_col, parent_pk_col in fk_check_columns:
+            child_table = table(child_table_name, column(child_fk_col))
+            parent_pk = getattr(base_table, parent_pk_col)
+            query = query.where(
+                ~exists(
+                    select(literal(1))
+                    .select_from(child_table)
+                    .where(child_table.c[child_fk_col] == parent_pk)
+                )
+            )

Review Comment:
   `base_table` is an aliased selectable and columns are accessed via `.c[...]` elsewhere in this function. Using `getattr(base_table, parent_pk_col)` is likely to fail (or produce the wrong SQL expression) because `id`/PK columns generally aren’t exposed as attributes on these alias objects. Use `base_table.c[parent_pk_col]` (and consider raising a clear error if the PK column isn’t present in the selectable) to ensure the FK check is actually correlated correctly.



##########
airflow-core/src/airflow/utils/db_cleanup.py:
##########
@@ -373,6 +382,21 @@ def _build_query(
         )
         conditions.append(column(max_date_col_name).is_(None))
     query = query.where(and_(*conditions))
+
+    # Exclude rows still referenced by child tables (FK with RESTRICT).
+    fk_check_columns = kwargs.get("fk_check_columns")
+    if fk_check_columns:

Review Comment:
   `fk_check_columns` is now a first-class behavior change in `_build_query`, but it’s threaded via `**kwargs`, which makes the API harder to understand and type-check. Consider adding an explicit, typed keyword parameter (e.g. `fk_check_columns: list[tuple[str, str, str]] | None = None`) to `_build_query` (and similarly to `_cleanup_table` if needed) rather than relying on `kwargs.get`.
   ```suggestion
       if fk_check_columns is not None:
   ```



##########
airflow-core/src/airflow/utils/db_cleanup.py:
##########
@@ -88,6 +88,11 @@ class _TableConfig:
     # because the relationships are unlikely to change and the number of tables is small.
     # Relying on automation here would increase complexity and reduce maintainability.
     dependent_tables: list[str] | None = None
+    # Columns in other tables that hold FK references to this table's primary key.
+    # Used to add NOT EXISTS guards so we don't delete rows still referenced by
+    # surviving child rows (e.g. task_instance.dag_version_id → dag_version.id).
+    # Each entry is (child_table_name, child_fk_column_name, parent_pk_column_name).
+    fk_check_columns: list[tuple[str, str, str]] | None = None

Review Comment:
   Since `fk_check_columns` changes which rows are eligible for deletion, it would be helpful for `dry_run`/config output to surface it (similar to `dependent_tables` and `keep_last*` outputs). Consider including `fk_check_columns` in the human-readable config that gets printed so operators can understand why some parent rows are being skipped.



-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] fix: skip dag_version rows still referenced by task_instance in db clean [airflow]

Posted by "potiuk (via GitHub)" <gi...@apache.org>.
potiuk closed pull request #63728: fix: skip dag_version rows still referenced by task_instance in db clean
URL: https://github.com/apache/airflow/pull/63728


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] fix: skip dag_version rows still referenced by task_instance in db clean [airflow]

Posted by "zoomingrocket (via GitHub)" <gi...@apache.org>.
zoomingrocket commented on PR #63728:
URL: https://github.com/apache/airflow/pull/63728#issuecomment-4329762903

   A fix was already committed to resolve this, it pending review - https://github.com/apache/airflow/pull/62023


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org