load_file replacing existing tables fails if there are views on the tables
mpgreg opened this issue · 1 comments
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