fernando-luiz-effecti commented on issue #71864:
URL: https://github.com/apache/airflow/issues/71864#issuecomment-6145101819
Same issue here on **Airflow 3.3.1** (official chart 1.22.0, PostgreSQL 17).
I found the cause and was able to reproduce it on a fresh install.
### Root cause
The rows that block the delete are **task instances that never started**:
`state IS NULL`, `start_date IS NULL`.
They belong to a DAG that is paused and later becomes stale (its file was
removed).
1. The `task_instance` cleanup filters on `start_date`.
It is NULL for these rows, so they are never eligible.
The same happens to their `dag_run`, which stays `queued` with
`start_date` NULL.
2. The `dag` cleanup picks stale DAGs by `last_parsed_time`.
Deleting those rows cascades to `dag_version` (`dag_version_dag_id_fkey
... ON DELETE CASCADE`).
3. `task_instance_dag_version_id_fkey` is `ON DELETE RESTRICT`, so the whole
`DELETE FROM dag` fails.
Nothing checks whether the DAG's versions are still referenced before
deleting.
Once a DAG reaches this state, every later run of `db clean` fails on the
`dag` table.
Each failed run also leaves an `_airflow_deleted__dag__*` table behind.
In production, 76 DAGs were eligible for deletion.
2 of them had 7 task instances in this state, and that was enough to block
the `dag` table for every run.
### Reproduction (fresh 3.3.1 DB, chart 1.22.0,
`dags_are_paused_at_creation=True`)
1. Add this DAG file and wait until it is parsed. It is created paused.
```python
from datetime import datetime
from airflow.providers.standard.operators.empty import EmptyOperator
from airflow.sdk import DAG
with DAG(dag_id="repro_never_started", schedule=None,
start_date=datetime(2026, 1, 1), catchup=False):
EmptyOperator(task_id="a") >> EmptyOperator(task_id="b")
```
2. Trigger it while it is still paused: `airflow dags trigger
repro_never_started`.
The result is a `dag_run` with `state=queued` and `start_date` NULL, plus
2 task instances with `state` NULL and `start_date` NULL.
3. Delete the DAG file and wait for `is_stale = true`.
4. Run `airflow db clean --clean-before-timestamp "$(date -u
+%Y-%m-%dT%H:%M:%S)" --yes --verbose`:
```
Checking table task_instance
Found 0 rows meeting deletion criteria.
...
Checking table dag_run
Found 0 rows meeting deletion criteria.
Checking table dag_version
Found 0 rows meeting deletion criteria.
Checking table dag
Found 1 rows meeting deletion criteria.
Moving data to table _airflow_deleted__dag__20261013115501
sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation)
update or delete on table "dag_version" violates foreign key constraint
"task_instance_dag_version_id_fkey" on table "task_instance"
DETAIL: Key (id)=(01a1351c-...) is still referenced from table
"task_instance".
[SQL: DELETE FROM dag USING _airflow_deleted__dag__20261013115501 WHERE
dag.dag_id = _airflow_deleted__dag__20261013115501.dag_id]
```
### Workaround (verified)
Delete the never-started task instances of the stale DAGs, then run `db
clean` again.
With this, the run completes with exit 0:
```sql
DELETE FROM task_instance ti
USING dag_version v, dag d
WHERE v.id = ti.dag_version_id AND d.dag_id = v.dag_id
AND d.is_stale AND d.last_parsed_time < '<clean_before_ts>'
AND ti.start_date IS NULL;
```
The `queued` dag_run is left behind as an orphan, because nothing cleans
runs with a NULL `start_date`.
--
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]
