load_file replacing existing tables fails if there are views on the tables

[mpgreg]

load_file(if_exists='replace') works the first time a DAG is run. Upstream tasks subsequently create views on that DAG. Rerunning the DAG fails as it cannot drop the existing tables because of the dependent views.

[2023-04-30, 09:32:12 UTC] {taskinstance.py:1776} ERROR - Task failed with exception Traceback (most recent call last): File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) psycopg2.errors.DependentObjectsStillExist: cannot drop table tmp_astro.stg_customers because other objects depend on it DETAIL: view tmp_astro.customers depends on table tmp_astro.stg_customers HINT: Use DROP ... CASCADE to drop the dependent objects too.

Version

• Astro: 1.5.3
• OS: quay.io/astronomer/astro-runtime:7.4.2-base

To Reproduce

Example DAG is at: https://github.com/astronomer/airflow-ml-demo/blob/c2e150649301458ef4fe26d8b6bd6c3f30836137/dags/customer_analytics.py#L197

Upstream tasks that create views are at: https://github.com/astronomer/airflow-ml-demo/blob/c2e150649301458ef4fe26d8b6bd6c3f30836137/dags/customer_analytics.py#L239

Expected behavior if_exists='replace' should drop the table even if views exist

[tatiana]

Thanks for reporting the issue, @mpgreg ! I added it to the 1.7 milestone. cc @jlaneve