Janitor fails to delete object with dependencies in Postgres

[petrikoro]

When running the Janitor against a PostgreSQL database, objects with dependencies (e.g., views built on tables) aren’t dropped first, causing the subsequent DROP TABLE operation to fail due to those existing dependencies.

Traceback

2025-06-19 15:41:06,011 - MainThread - sqlmesh.cli - ERROR - Unhandled exception (__init__.py:53)
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/sqlmesh/utils/concurrency.py", line 69, in _process_node
    self.fn(node)
File "/usr/local/lib/python3.12/site-packages/sqlmesh/utils/concurrency.py", line 172, in <lambda>
    lambda s_id: fn(snapshots_by_id[s_id]),
                ^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlmesh/core/snapshot/evaluator.py", line 474, in <lambda>
    lambda s: self._cleanup_snapshot(
            ^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlmesh/core/snapshot/evaluator.py", line 1038, in _cleanup_snapshot
    evaluation_strategy.delete(
File "/usr/local/lib/python3.12/site-packages/sqlmesh/core/snapshot/evaluator.py", line 1535, in delete
    self.adapter.drop_table(name)
File "/usr/local/lib/python3.12/site-packages/sqlmesh/core/engine_adapter/shared.py", line 334, in internal_wrapper
    return func(*list_args, **kwargs)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlmesh/core/engine_adapter/base.py", line 931, in drop_table
    self._drop_object(name=table_name, exists=exists)
File "/usr/local/lib/python3.12/site-packages/sqlmesh/core/engine_adapter/base.py", line 959, in _drop_object
    self.execute(exp.Drop(this=exp.to_table(name), kind=kind, exists=exists, **drop_args))
File "/usr/local/lib/python3.12/site-packages/sqlmesh/core/engine_adapter/base.py", line 2166, in execute
    self._execute(sql, **kwargs)
File "/usr/local/lib/python3.12/site-packages/sqlmesh/core/engine_adapter/base.py", line 2187, in _execute
    self.cursor.execute(sql, **kwargs)
psycopg2.errors.DependentObjectsStillExist: cannot drop table sqlmesh__intermediate.intermediate__int_lesson_events__extracted__2348405816 because other objects depend on it
DETAIL:  view sqlmesh__intermediate.intermediate__int_lessons__unioned__2208384159 depends on table sqlmesh__intermediate.intermediate__int_lesson_events__extracted__2348405816
HINT:  Use DROP ... CASCADE to drop the dependent objects too.


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

Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/sqlmesh/cli/__init__.py", line 51, in _debug_exception_handler
    return func()
            ^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlmesh/cli/__init__.py", line 29, in <lambda>
    return handler(sqlmesh_context, lambda: func(*args, **kwargs))
                                            ^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlmesh/core/analytics/__init__.py", line 82, in wrapper
    return func(*args, **kwargs)
            ^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlmesh/cli/main.py", line 570, in janitor
    ctx.obj.run_janitor(ignore_ttl, **kwargs)
File "/usr/local/lib/python3.12/site-packages/sqlmesh/core/analytics/__init__.py", line 110, in wrapper
    return func(*args, **kwargs)
            ^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlmesh/core/context.py", line 820, in run_janitor
    self._run_janitor(ignore_ttl)
File "/usr/local/lib/python3.12/site-packages/sqlmesh/core/context.py", line 2691, in _run_janitor
    self.snapshot_evaluator.cleanup(
File "/usr/local/lib/python3.12/site-packages/sqlmesh/core/snapshot/evaluator.py", line 472, in cleanup
    concurrent_apply_to_snapshots(
File "/usr/local/lib/python3.12/site-packages/sqlmesh/utils/concurrency.py", line 170, in concurrent_apply_to_snapshots
    return concurrent_apply_to_dag(
            ^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlmesh/utils/concurrency.py", line 212, in concurrent_apply_to_dag
    ).run()
    ^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlmesh/utils/concurrency.py", line 64, in run
    self._finished_future.result()
File "/usr/local/lib/python3.12/concurrent/futures/_base.py", line 456, in result
    return self.__get_result()
            ^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/concurrent/futures/_base.py", line 401, in __get_result
    raise self._exception
sqlmesh.utils.concurrency.NodeExecutionFailedError: Execution failed for node SnapshotId<"dwh"."intermediate"."int_lesson_events__extracted": 172656272>

---

sqlmesh v0.192.2, dialect postgres, database postgres

[izeigerman]

At this point this view should not have any downstream dependencies on it. Everything downstream should've been garbage collected. Can you please check what downstream objects still depend on this view?

[florianm]

I'm running into the same error, apologies for jumping onto this issue.

High level I receive CSVs from an external source, need to read these into a postgres db, where I roughly follow a medallion architecture of tables propagating across three schemas.

On a low level, executing sqlmesh run (dev) fails to refresh the full Python model which reads the CSV into the first table.

I'm using SQLMesh v0.196.4.

My config running in a devcontainer with a disposable postgres sidecar (therefore plain credentials):

# A Postgres-based config.
# Current limitation of SQLMesh: cannot cascade drop of table
# so refreshing FULL models fails after initial run.
# We use Ducklake instead.
from sqlmesh.core.config import (
    Config,
    DuckDBConnectionConfig,
    GatewayConfig,
    ModelDefaultsConfig,
    PostgresConnectionConfig,
)
from sqlmesh.core.config.connection import DuckDBAttachOptions

config = Config(
    gateways={
        "duckdb": GatewayConfig(
            connection=DuckDBConnectionConfig(
                catalogs={
                    "peoplewa": DuckDBAttachOptions(
                        type="postgres",
                        path=(
                            "dbname=peoplewa user=peoplewa "
                            "password=peoplewa host=localhost port=5432"
                        ),
                    ),
                },
            ),
            state_connection=PostgresConnectionConfig(
                type="postgres",
                database="peoplewa",
                user="peoplewa",
                password="peoplewa",
                host="localhost",
                port=5432,
            ),
        ),
        "postgres": GatewayConfig(
            connection=PostgresConnectionConfig(
                type="postgres",
                database="peoplewa",
                user="peoplewa",
                password="peoplewa",
                host="localhost",
                port=5432,
            ),
            state_connection=PostgresConnectionConfig(
                type="postgres",
                database="peoplewa",
                user="peoplewa",
                password="peoplewa",
                host="localhost",
                port=5432,
            ),
        ),
    },
    default_gateway="duckdb",
    gateway_managed_virtual_layer=True,
    model_defaults=ModelDefaultsConfig(
        dialect="postgres",
        start="2025-06-21",
    ),
)

My schema "raw" reads CSV files into Postgres tables, using polars as reader, duckdb as engine (much faster than postgres). I've factored out some repetitive bits.

from sqlglot.expressions import to_column
from sqlmesh import model

from scripts import make_coldef, make_model_name, read_csv_in_chunks

# Dataset specific config
csv_fn = "seeds/path/to/TABLENAME.csv"
csv_pk = "variablename"


@model(
    name=make_model_name(csv_fn, prefix="raw"), # generates "peoplewa.raw.TABLENAME"
    kind="full",
    cron="*/5 * * * *",
    # signals=[ext_file_updated(csv_fn, )
    grain=csv_pk,
    columns=make_coldef(csv_fn), # generates col def from CSV using "frictionless describe"
    audits=[
        ("not_null", {"columns": [to_column(csv_pk)]}),
    ],
)
def run(context, csv_fn=csv_fn, **kwargs):
    """Read data from a CSV."""
    return read_csv_in_chunks(csv_fn)  # yields chunks of pandas df

I'm getting the following error for each table (I'm substituting the model name with TABLENAME):

 "peoplewa"."raw"."TABLENAME"

    Error:
      Failed to execute query "DROP Table  IF EXISTS "sqlmesh__raw"."raw__TABLENAME__41048449"": ERROR:  cannot drop table sqlmesh__raw.raw__TABLENAME__41048449 
because other objects depend on it
      DETAIL:  view raw__dev.TABLENAME depends on table sqlmesh__raw.raw__TABLENAME__41048449
      view raw.TABLENAME depends on table sqlmesh__raw.raw__TABLENAME__41048449
      HINT:  Use DROP ... CASCADE to drop the dependent objects too.

I could be wrong, but these dependencies appear to be SQLMesh internal views used to construct the virtual environment "dev". Since all mentioned tables here have the schema "raw" I can tell they do not refer to my downstream dependencies in my "bronze"/"silver"/"gold" schemas.

My expectation is that every time I evaluate these "CSV to pg" models (assuming a signal that can tell whether the file was updated since last execution_ds), the postgres table gets refreshed, and all of my downstream models get refreshed too, if my custom signals say so.

It would be absolutely amazing if this limitation could be addressed!