psycopg2.errors.DependentObjectsStillExist - HINT: Use DROP ... CASCADE to drop the dependent objects too.

[wahmedswl]

Hi!

SQLMesh is failing on sqlmesh run with following error

psycopg2.errors.DependentObjectsStillExist: cannot drop table sqlmesh__data_pipeline.data_pipeline__external_patient_statement__4025377354__dev because other objects depend on it
DETAIL:  view sqlmesh__patient_management.patient_management__v_patient_statement_detail__2364103992__dev depends on table sqlmesh__data_pipeline.data_pipeline__external_patient_statement__4025377354__dev
view sqlmesh__patient_management.patient_management__v_patient_statement__897401398__dev depends on table sqlmesh__data_pipeline.data_pipeline__external_patient_statement__4025377354__dev
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

SQLMesh wasn't giving any detail and then I have to run sqlmesh --debug run to get this error. I don't have any *__dev environment and only prod environment.

@model(
    "data_pipeline.external_patient_statement",
    kind=dict(name=ModelKindName.INCREMENTAL_UNMANAGED, on_destructive_change="warn"),
    columns={
        "external_patient_statement_id": "string",
        "account_no": "string",
        "statement_no": "string",
        "statement_date": "datetime",
        "patient_name": "string",
        "patient_credit": "float",
        "total_patient_due": "float",
        "total_insurance_due": "float",
        "current_balance": "float",
        "over_30_days": "float",
        "over_60_days": "float",
        "over_90_days": "float",
        "over_120_days": "float",
        "practice_name": "string",
        "full_data": "string",
        "client_name": "string",
        "file_name": "string",
        "file_checksum": "string",
        "inserted_at": "datetime",
        "inserted_by": "string",
    },
    cron="@daily",
    grain="(external_patient_statement_id)",
    allow_partials=True,
    on_virtual_update=["@SFTP_PROCESSED()"],
    post_statements=[
        "CREATE INDEX IF NOT EXISTS idx_dp_epat_stat_external_patient_statement_id ON @this_model USING btree (external_patient_statement_id);",
        "CREATE INDEX IF NOT EXISTS idx_dp_epat_stat_account_no ON @this_model USING btree (account_no);",
        "CREATE INDEX IF NOT EXISTS idx_dp_epat_stat_statement_no ON @this_model USING btree (statement_no);",
        "CREATE INDEX IF NOT EXISTS idx_dp_epat_stat_patient_name ON @this_model USING btree (patient_name);",
        "CREATE INDEX IF NOT EXISTS idx_dp_epat_stat_practice_name ON @this_model USING btree (practice_name);",
        "CREATE INDEX IF NOT EXISTS idx_dp_epat_stat_client_name ON @this_model USING btree (client_name);",
    ],
)

This is an incremental model, so what is the way to clean this up?

Thanks

[wahmedswl]

This is linked to #4304.