UI shows Foreign Key Error when deleting a dag

[MatrixManAtYrService]

Apache Airflow version

2.3.0b1 (pre-release)

What happened

I tried to delete a dag from the grid view, and I saw this instead

Ooops!
Something bad has happened.
...
Python version: 3.7.13
Airflow version: 2.3.0b1
Node: airflow-webserver-7c4f49f5dd-h74w2
-------------------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1706, in _execute_context
    cursor, statement, parameters, context
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.ForeignKeyViolation: update or delete on table "dag" violates foreign key constraint "dag_tag_dag_id_fkey" on table "dag_tag"
DETAIL:  Key (dag_id)=(core_todo) is still referenced from table "dag_tag".


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

Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.7/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/airflow/.local/lib/python3.7/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/airflow/.local/lib/python3.7/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/airflow/.local/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/home/airflow/.local/lib/python3.7/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/airflow/.local/lib/python3.7/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/www/auth.py", line 40, in decorated
    return func(*args, **kwargs)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/www/decorators.py", line 80, in wrapper
    return f(*args, **kwargs)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/www/views.py", line 1812, in delete
    delete_dag.delete_dag(dag_id)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/utils/session.py", line 71, in wrapper
    return func(*args, session=session, **kwargs)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/api/common/delete_dag.py", line 80, in delete_dag
    .delete(synchronize_session='fetch')
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3111, in delete
    execution_options={"synchronize_session": synchronize_session},
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1670, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1520, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 314, in _execute_on_connection
    self, multiparams, params, execution_options
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1399, in _execute_clauseelement
    cache_hit=cache_hit,
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1749, in _execute_context
    e, statement, parameters, cursor, context
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1930, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1706, in _execute_context
    cursor, statement, parameters, context
  File "/home/airflow/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table "dag" violates foreign key constraint "dag_tag_dag_id_fkey" on table "dag_tag"
DETAIL:  Key (dag_id)=(core_todo) is still referenced from table "dag_tag".

[SQL: DELETE FROM dag WHERE dag.dag_id IN (%(dag_id_1_1)s) RETURNING dag.dag_id]
[parameters: {'dag_id_1_1': 'core_todo'}]
(Background on this error at: http://sqlalche.me/e/14/gkpj)

Also, here are the database pod logs:

│ 2022-04-25 01:42:14.185 GMT [155] STATEMENT:  INSERT INTO log (dttm, dag_id, task_id, map_index, event, execution_date, owner, extra) VALUES ('2022-04-25T01:42:14.178085+00:00'::timestamptz, NULL, NULL, NULL, 'cli_upgradedb', NULL, ' │
│ 2022-04-25 01:42:14.371 GMT [155] ERROR:  relation "connection" does not exist at character 55                                                                                                                                            │
│ 2022-04-25 01:42:14.371 GMT [155] STATEMENT:  SELECT connection.conn_id AS connection_conn_id                                                                                                                                             │
│     FROM connection GROUP BY connection.conn_id                                                                                                                                                                                           │
│     HAVING count(*) > 1                                                                                                                                                                                                                   │
│ 2022-04-25 01:42:14.372 GMT [155] ERROR:  relation "connection" does not exist at character 55                                                                                                                                            │
│ 2022-04-25 01:42:14.372 GMT [155] STATEMENT:  SELECT connection.conn_id AS connection_conn_id                                                                                                                                             │
│     FROM connection                                                                                                                                                                                                                       │
│     WHERE connection.conn_type IS NULL                                                                                                                                                                                                    │
│ 2022-04-25 01:42:16.489 GMT [158] ERROR:  relation "log" does not exist at character 13                                                                                                                                                   │
│ 2022-04-25 01:42:16.489 GMT [158] STATEMENT:  INSERT INTO log (dttm, dag_id, task_id, map_index, event, execution_date, owner, extra) VALUES ('2022-04-25T01:42:16.482543+00:00'::timestamptz, NULL, NULL, NULL, 'cli_check', NULL, 'airf │
│ 2022-04-25 01:42:17.917 GMT [160] ERROR:  column "map_index" of relation "log" does not exist at character 41                                                                                                                             │
│ 2022-04-25 01:42:17.917 GMT [160] STATEMENT:  INSERT INTO log (dttm, dag_id, task_id, map_index, event, execution_date, owner, extra) VALUES ('2022-04-25T01:42:17.910396+00:00'::timestamptz, NULL, NULL, NULL, 'cli_flower', NULL, 'air │
│ 2022-04-25 03:18:33.631 GMT [24494] ERROR:  update or delete on table "dag" violates foreign key constraint "dag_tag_dag_id_fkey" on table "dag_tag"                                                                                      │
│ 2022-04-25 03:18:33.631 GMT [24494] DETAIL:  Key (dag_id)=(core_todo) is still referenced from table "dag_tag".                                                                                                                           │
│ 2022-04-25 03:18:33.631 GMT [24494] STATEMENT:  DELETE FROM dag WHERE dag.dag_id IN ('core_todo') RETURNING dag.dag_id                                                                                                                    │
│ 2022-04-25 03:31:18.858 GMT [24760] ERROR:  update or delete on table "dag" violates foreign key constraint "dag_tag_dag_id_fkey" on table "dag_tag"                                                                                      │
│ 2022-04-25 03:31:18.858 GMT [24760] DETAIL:  Key (dag_id)=(core_todo) is still referenced from table "dag_tag".                                                                                                                           │
│ 2022-04-25 03:31:18.858 GMT [24760] STATEMENT:  DELETE FROM dag WHERE dag.dag_id IN ('core_todo') RETURNING dag.dag_id    

What you think should happen instead

The dag gets deleted, no error

How to reproduce

I'm not sure if I can replicate it, but I'll report back here if I can. So far as I remember the steps were:

1. run a (large) dag
2. the dag failed for unrelated reasons
3. delete the dag from the grid view
4. see error page

Operating System

kubernetes/debian

Versions of Apache Airflow Providers

n/a

Deployment

Official Apache Airflow Helm Chart

Deployment details

Deployed via helm into a microk8s cluster, which was running in a VM, which was deployed by CircleCI.

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

[uranusjr]

See also #22967.

[bbovenzi]

Does this only happen on Grid view? Not graph or other DAG views?

[MatrixManAtYrService]

@bbovenzi Sorry i didn't test multiple, but based on the traceback I kind of doubt it's specific to the view.

It's kind of tricky because it came up in a dag that itself deploys other airflows and runs other dags in them (i.e. a framework, not a test). It was so unwieldly that I've since broken it up in to smaller pieces.

I can recreate the monster and see if it it breaks again. I'll dump scheduler, webserver, and postgres logs while I do--should I be on the look out for anything else?

[ephraimbuddy]

I ran into this, it's irregular because after I stopped my airflow instance and restarted it, it didn't happen again.
The DAG has a tag on it, one failed dag run among multiple dagruns.

Looking at the code, I feel we should add delete to the cascade options here:

airflow/airflow/models/dag.py

Line 2684 in 7c9852f

tags = relationship('DagTag', cascade='all,delete-orphan', backref=backref('dag'))

So it becomes:

tags = relationship('DagTag', cascade='all, delete, delete-orphan', backref=backref('dag'))

We have both delete and delete-orphan in dagrun-taskinstance relationship:

airflow/airflow/models/dagrun.py

Line 160 in 7c9852f

TI, back_populates="dag_run", cascade='save-update, merge, delete, delete-orphan'

From SQLAlchemy:

delete cascade on one-to-many relationships is often combined with delete-orphan cascade, which will emit a DELETE for the related row if the “child” object is deassociated from the parent. The combination of delete and delete-orphan cascade covers both situations where SQLAlchemy has to decide between setting a foreign key column to NULL versus deleting the row entirely.

I feel we should add it, but I'm not sure how to test the change since the issue is hard to reproduce

cc: @ashb