Skip to content

ForeignKeyViolation error when doing airflow db clean #56192

Closed
#68339
Closed
ForeignKeyViolation error when doing airflow db clean#56192
#68339
Labels
affected_version:3.0Issues Reported for 3.0affected_version:3.1Issues Reported for 3.1area:MetaDBMeta Database related issues.area:corekind:bugThis is a clearly a bug

Description

@hket-ianchu
hket-ianchu
opened on Sep 29, 2025

Apache Airflow version

3.1.0

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

No response

What happened?

The command is used to clean up the records created for 30 days+ :

airflow db clean --clean-before-timestamp "$(date -d "30 days ago" +%Y-%m-%dT%H:%M)" --skip-archive --yes

The error :

Checking table dag_version
Found 155 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__dag_version__20250929024208
    self.dialect.do_execute(
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, 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)=(0198edf9-f9f1-7d73-92c1-fac5359c10b7) is still referenced from table "task_instance".

For the DAG Version ID 0198edf9-f9f1-7d73-92c1-fac5359c10b7, I found :-

  • 1 record in dag_version with last_updated = 2025-08-28 (more than 1 month ago, should be scoped)
  • some records in task_instance with updated_at = 2025-09-02 (less than 1 month ago, should be not scoped)

Seem I can workaround by retrying it after 2025-10-02 so all mentioned records will be scoped to remove.

What you think should happen instead?

It should skip the problematic records / tables, or aggressively remove the referencing records as well.

How to reproduce

n/a

Operating System

AlmaLinux release 9.6 (Sage Margay)

Versions of Apache Airflow Providers

No response

Deployment

Official Apache Airflow Helm Chart

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

Activity

  1. hket-ianchu
    added
    kind:bugThis is a clearly a bug
    area:core
    needs-triagelabel for new issues that we didn't triage yet
    on Sep 29, 2025

  2. boring-cyborg commented on Sep 29, 2025

    @boring-cyborg
    boring-cyborgBot
    on Sep 29, 2025 – with 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.

  3. dosubot
    added
    area:MetaDBMeta Database related issues.
    on Sep 29, 2025

  4. HsiuChuanHsu commented on Oct 11, 2025

    @HsiuChuanHsu
    HsiuChuanHsu
    on Oct 11, 2025
    Contributor

    Hi @hket-ianchu,
    I was able to reproduce the issue you provided. Below are the reproduction results:

    Create testing data in dag_version & task_instance table.

    • dag_version: created_at | 2025-09-01
    Image
    • task_instance-1: start_date | 2025-09-06
    Image
    • task_instance-2: start_date | 2025-10-01
    Image

    Use airflow db clean to clean up the records

    airflow db clean --clean-before-timestamp "2025-09-21T10:30" --skip-archive --yes

    Result

    airflow db clean --clean-before-timestamp "2025-09-21T10:30" --skip-archive --yes
/opt/airflow/airflow-core/src/airflow/dag_processing/dagbag.py:40 DeprecationWarning: airflow.exceptions.AirflowDagCycleException is deprecated. Use airflow.sdk.exceptions.AirflowDagCycleException instead.
2025-10-11T08:33:45.238607Z [warning  ] Table celery_tasksetmeta not found.  Skipping. [airflow.utils.db_cleanup] loc=db_cleanup.py:555
2025-10-11T08:33:45.238729Z [warning  ] Table celery_taskmeta not found.  Skipping. [airflow.utils.db_cleanup] loc=db_cleanup.py:555

Checking table task_instance_history
Found 0 rows meeting deletion criteria.

Checking table xcom
Found 0 rows meeting deletion criteria.

Checking table task_instance
Found 1 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__task_instance__20251011083345
Finished Performing Delete

Checking table deadline
Found 0 rows meeting deletion criteria.

Checking table dag_run
Found 1 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__dag_run__20251011083345
Finished Performing Delete

Checking table dag_version
Found 1 rows meeting deletion criteria.
Performing Delete...
Moving data to table _airflow_deleted__dag_version__20251011083345
Traceback (most recent call last):
  File "/usr/python/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/usr/python/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, 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)=(018c5e5e-7a8b-7000-8000-000000000001) is still referenced from table "task_instance".


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

Traceback (most recent call last):
  File "/usr/python/bin/airflow", line 10, in <module>
    sys.exit(main())
             ^^^^^^
  File "/opt/airflow/airflow-core/src/airflow/__main__.py", line 55, in main
    args.func(args)
  File "/opt/airflow/airflow-core/src/airflow/cli/cli_config.py", line 49, in command
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/airflow/airflow-core/src/airflow/utils/cli.py", line 115, in wrapper
    return f(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^
  File "/opt/airflow/airflow-core/src/airflow/utils/providers_configuration_loader.py", line 54, in wrapped_function
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/airflow/airflow-core/src/airflow/cli/commands/db_command.py", line 297, in cleanup_tables
    run_cleanup(
  File "/opt/airflow/airflow-core/src/airflow/utils/session.py", line 100, in wrapper
    return func(*args, session=session, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/airflow/airflow-core/src/airflow/utils/db_cleanup.py", line 544, in run_cleanup
    _cleanup_table(
  File "/opt/airflow/airflow-core/src/airflow/utils/db_cleanup.py", line 371, in _cleanup_table
    _do_delete(
  File "/opt/airflow/airflow-core/src/airflow/utils/db_cleanup.py", line 261, in _do_delete
    raise e
  File "/opt/airflow/airflow-core/src/airflow/utils/db_cleanup.py", line 257, in _do_delete
    session.execute(delete)
  File "/usr/python/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1717, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/python/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/python/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/python/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/python/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/python/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
    util.raise_(
  File "/usr/python/lib/python3.12/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/python/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/usr/python/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, 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)=(018c5e5e-7a8b-7000-8000-000000000001) is still referenced from table "task_instance".

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

  5. HsiuChuanHsu commented on Oct 11, 2025

    @HsiuChuanHsu
    HsiuChuanHsu
    on Oct 11, 2025
    Contributor

    I will sumbit a PR to refine Cleanup Logic.

    Refined Cleanup Logic:

    • Before removing a dag_version, we must check for any remaining task_instance references.
    • If references exist, the dag_version will be skipped (similar to the suggested "skip problematic records" approach).
  6. potiuk
    added
    affected_version:3.0Issues Reported for 3.0
    affected_version:3.1Issues Reported for 3.1
    on Oct 13, 2025

  7. r-richmond commented on Oct 21, 2025

    @r-richmond
    r-richmond
    on Oct 21, 2025
    Contributor

    @HsiuChuanHsu Thank you for submitting a PR for this. Just wanted to say that I've experienced this in production as well.

    Stack Trace

    airlfow db clean --clean-before-timestamp --skip-archive TIMESTAMP

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

Traceback (most recent call last):
  File "/home/airflow/.local/bin/airflow", line 10, 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 297, 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 544, in run_cleanup
    _cleanup_table(
  File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/db_cleanup.py", line 371, in _cleanup_table
    _do_delete(
  File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/db_cleanup.py", line 261, in _do_delete
    raise e
  File "/home/airflow/.local/lib/python3.12/site-packages/airflow/utils/db_cleanup.py", line 257, in _do_delete
    session.execute(delete)
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1717, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
    self._handle_dbapi_exception(
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
    util.raise_(
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/home/airflow/.local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, 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)=(0198ce50-b4df-74a5-ab94-a262404cf623) is still referenced from table "task_instance".

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

  8. HsiuChuanHsu commented on Oct 21, 2025

    @HsiuChuanHsu
    HsiuChuanHsu
    on Oct 21, 2025
    Contributor

    @r-richmond Yes, exact same error message! Thanks for letting me know.

  9. hket-ianchu commented on Oct 27, 2025

    @hket-ianchu
    hket-ianchu
    on Oct 27, 2025
    Author

    @HsiuChuanHsu Thanks for your help~!

  10. vikramkoka
    assigned
    HsiuChuanHsu
    on Oct 31, 2025
  11. vikramkoka
    removed
    needs-triagelabel for new issues that we didn't triage yet
    on Oct 31, 2025

  12. j-cull commented on Dec 12, 2025

    @j-cull
    j-cull
    on Dec 12, 2025

    Is this going to be added into 3.1.5?

  13. 9 remaining items

  14. kang-seongbeom commented on May 7, 2026

    @kang-seongbeom
    kang-seongbeom
    on May 7, 2026

    I'm not sure if this is the right way to use it, but dropping the foreign key constraint on dag_version in the task_instance table did resolve the above issue.....

    ALTER TABLE public.task_instance DROP CONSTRAINT task_instance_dag_version_id_fkey;

  15. sw-cyderes commented on May 13, 2026

    @sw-cyderes
    sw-cyderes
    on May 13, 2026

    I'm not sure if this is the right way to use it, but dropping the foreign key constraint on dag_version in the task_instance table did resolve the above issue.....

    ALTER TABLE public.task_instance DROP CONSTRAINT task_instance_dag_version_id_fkey;

    Is this safe to do?

  16. kang-seongbeom commented on May 13, 2026

    @kang-seongbeom
    kang-seongbeom
    on May 13, 2026

    I'm not sure if this is the right way to use it, but dropping the foreign key constraint on dag_version in the task_instance table did resolve the above issue.....

    ALTER TABLE public.task_instance DROP CONSTRAINT task_instance_dag_version_id_fkey;

    Is this safe to do?

    This is unsafe and can cause problems during version upgrades. Although there is a workaround, it requires forcibly modifying existing database data.

  17. TKul6 commented on May 17, 2026

    @TKul6
    TKul6
    on May 17, 2026

    @sw-cyderes I did a safer (and more dummy) patch (until the issue will be fixed).

    the cleaner can get an array of tables names to purge, I just provided all the table except the dag_version.

    so the table won't be cleaned, but at least in my case it does not contain a lot of data, so as a patch it is good enough for me.

  18. kang-seongbeom commented on May 17, 2026

    @kang-seongbeom
    kang-seongbeom
    on May 17, 2026

    @sw-cyderes I did a safer (and more dummy) patch (until the issue will be fixed).

    the cleaner can get an array of tables names to purge, I just provided all the table except the dag_version.

    so the table won't be cleaned, but at least in my case it does not contain a lot of data, so as a patch it is good enough for me.

    In the #63703 issue, there is a command to perform a cleanup excluding the dag_version and dag tables.

    airflow db clean --clean-before-timestamp "$(date -d '-15 days' +%Y-%m-%dT%H:%M:%S)" --skip-archive -y --tables "_xcom_archive,asset_event,callback_request,celery_taskmeta,celery_tasksetmeta,dag_run,deadline,import_error,job,log,sla_miss,task_instance,task_instance_history,task_reschedule,trigger,xcom"

  19. j-cull commented on Jun 5, 2026

    @j-cull
    j-cull
    on Jun 5, 2026

    What am I missing here? Why is this not seen as an important fix? An airflow instance will have degraded performance over time if airflow db clean is not ran regularly.

  20. alkismavridis commented on Jun 8, 2026

    @alkismavridis
    alkismavridis
    on Jun 8, 2026
    Contributor

    We are also affected by this. Are there any news? Seems like a non-trivial bug.

  21. potiuk
    linked a pull request that will close this issueSkip FK-referenced dag_version rows during db clean #68339on Jun 14, 2026

  22. potiuk commented on Jun 14, 2026

    @potiuk
    potiuk
    on Jun 14, 2026
    Member

    Fix is coming by #68339

  23. ephraimbuddy
    closed this as completedin #68339on Jun 14, 2026
  24. shahar1
    marked airflow db clean crashes with ForeignKeyViolation when deleting dag_version in Airflow 3.1.8 #63703 as a duplicate of this issue on Jun 19, 2026
  25. shahar1
    marked airflow db clean command errors when attempting to delete rows still being referenced #59474 as a duplicate of this issue on Jun 19, 2026

  26. igorlovich commented on Jul 17, 2026

    @igorlovich
    igorlovich
    on Jul 17, 2026

    Still failing with 3.3.0

    exec airflow db clean --clean-before-timestamp "${CLEAN_TS}" --yes --skip-archive --verbose

    sqlalchemy.exc.IntegrityError: (psycopg2.errors.RestrictViolation) update or delete on table "dag_version" violates RESTRICT setting of foreign key constraint "task_instance_dag_version_id_fkey" on table "task_instance" DETAIL: Key (id)=(019a2639-6675-74ec-bafd-a4f6b7a58a07) is referenced from table "task_instance".

  27. wufh43 commented on Aug 4, 2026

    @wufh43
    wufh43
    on Aug 4, 2026

    @ephraimbuddy We also experience that the issue remains in 3.3.0

  28. ephraimbuddy commented on Aug 4, 2026

    @ephraimbuddy
    ephraimbuddy
    on Aug 4, 2026
    Contributor

    @igorlovich @wufh43 , this particular issue is about cleaning the dag version table. If you had used --tables and specifically selected dag version it will work. Now the one you experience now seems to come from the dag. I will look into it or you can create a new issue with full log

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    affected_version:3.0Issues Reported for 3.0affected_version:3.1Issues Reported for 3.1area:MetaDBMeta Database related issues.area:corekind:bugThis is a clearly a bug

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions