Error when running airflow db clean on dag_version table

[mdnncz]

Apache Airflow version

3.1.6

If "Other Airflow 3 version" selected, which one?

3.1.3

What happened?

when running airflow db clean --clean-before '2025-10-15' --yes --verbose, error is reported:

Checking table task_instance_history
Found 2 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__task_instance_history__20260203124900
Finished Performing Delete

Checking table celery_taskmeta
Found 13 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__celery_taskmeta__20260203124900
Finished Performing Delete

Checking table callback_request
Found 0 rows meeting deletion criteria.

Checking table session
Found 0 rows meeting deletion criteria.

Checking table xcom
Found 12 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__xcom__20260203124900
Finished Performing Delete

Checking table task_instance
Found 17 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__task_instance__20260203124900
Finished Performing Delete

Checking table deadline
Found 0 rows meeting deletion criteria.

Checking table dag_run
Found 3 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__dag_run__20260203124900
Finished Performing Delete

Checking table dag_version
Found 46 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__dag_version__20260203124900
Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
    cursor.execute(statement, parameters)
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)=(0199ce36-3943-7f32-af3b-3976a2646dc8) is still referenced from table "task_instance".


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/airflow/.local/bin/airflow", line 7, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/airflow/__main__.py", line 55, in main
    args.func(args)
  File "/home/airflow/.local/lib/python3.12/site-packages/airflow/cli/cli_config.py", line 49, in command
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/cli.py", line 114, in wrapper
    return f(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/providers_configuration_loader.py", line 54, in wrapped_function
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/airflow/cli/commands/db_command.py", line 296, in cleanup_tables
    run_cleanup(
  File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/session.py", line 100, in wrapper
    return func(*args, session=session, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/db_cleanup.py", line 541, in run_cleanup
    _cleanup_table(
  File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/db_cleanup.py", line 372, in _cleanup_table
    _do_delete(
  File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/db_cleanup.py", line 262, in _do_delete
    raise e
  File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/db_cleanup.py", line 258, in _do_delete
    session.execute(delete)
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2351, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2258, in _execute_internal
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1419, in execute
    return meth(
           ^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 527, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1641, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2363, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
    cursor.execute(statement, parameters)
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)=(0199ce36-3943-7f32-af3b-3976a2646dc8) is still referenced from table "task_instance".

[SQL: DELETE FROM dag_version USING _airflow_deleted__dag_version__20260203124900 WHERE dag_version.id = _airflow_deleted__dag_version__20260203124900.id]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

What you think should happen instead?

no error shall be reported

How to reproduce

i can provide postgresql db backup as it is development env

Operating System

windows11/docker desktop/official airflow linux image

Versions of Apache Airflow Providers

irrelevant

Deployment

Docker-Compose

Deployment details

No response

Anything else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.

[mdnncz]

There is similar issue #52147. But even when running:

airflow db clean -t task_instance --clean-before '2025-10-15' --yes
airflow db clean -t dag_version --clean-before '2025-10-15' --yes

It does not help and the same error is reported.

[mdnncz]

Found the logical issue after using AIRFLOW__LOGGING__LOGGING_LEVEL=DEBUG and seing queries issued against the db.
The "old rows query" is invalid for the dag_version table.
It has only simple WHERE base.created_at < :created_at_1 condition. But you can't delete version, which has task_intances (started after the version has been created obviously).

IMHO the query should be:

SELECT base.*
FROM dag_version as base
WHERE base.created_at < :created_at_1
AND base.id NOT IN (SELECT DISTINCT dag_version_id FROM task_instance)

[tirkarthi]

Related #59474

[mdnncz]

Indeed this is duplicate. Thanks @tirkarthi .