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!

[erindru]

I've been trying to repro this (and I think #5083 is a duplicate). It happens on sqlmesh run because the first thing sqlmesh run does is collect garbage by running the janitor. It then proceeds to load new data.

The janitor processes things in the following order:

• drop environment-specific schemas / views that point to the physical tables
• drop snapshots (physical tables OR views for kind: view)

The assumption it makes is that once the virtual data environment views are dropped then there are no more references to the tables and those can now be dropped. Since tables don't reference each other, it assumes the tables can be dropped in any order.

However - kind: VIEW models create views at the physical layer instead of tables. In this instance, the drop order matters.

As far as I can tell, we don't currently build a DAG to ensure that things are dropped in a specific order - we just pass reverse_order=true to the cleanup task with the assumption that the list returned from state is in creation order so should be cleaned up in the reverse order.

One thing we also do is execute ddl_concurrent_tasks number of parallel tasks to drop objects. This mirrors the connection concurrent_tasks so defaults to 4 for postgres.

I haven't yet been able to reproduce a scenario locally where this fails, but I can see how it might if there were views in the mix. We may drop in the wrong order, or since we split amongst ddl_concurrent_tasks workers one worker might issue the DROP TABLE before the other worker issues DROP VIEW for a view that depends on the table - causing an error.

Question: Downstream of your peoplewa.raw.TABLENAME model, do you have any kind: VIEW models?

Also, if you set concurrent_tasks: 1 on your connection configs, does the issue still happen?

[erindru]

Oh, no, im talking rubbish. concurrent_apply_to_snapshots does build a dag

Still - i'd be interested to know if setting concurrent_tasks: 1 changes anything

[florianm]

@erindru thanks for getting onto this!

I did not have any VIEW models in my failing setup.

Unfortunately otherwise I'm struggling to repro on my end - I've moved to use blueprinted SQL models, updated SQLMesh to v 0.200.*, and am using DuckLake instead of Postgres. Everything works like a dream, and two OOM faster than Python models writing to Postgres, too.

[erindru]

Update: I managed to repro this, but only with views (which I guess was the original issue description).

Steps:

• You create a FULL model A (expiry: 7 days from now)
• 2 days later, you create a VIEW model B (expiry: 7 days from now so 2 days after Model A)
• 6 days later (after Model A expiry but before Model B expiry), you invalidate the virtual environment and run the janitor

This causes the drop error, because Model A is dropped due to being expired but Model B is not considered at all because it hasn't expired yet.

The janitor does drop in DAG order, but because it is only considering expired objects, Model B is not part of the DAG. This is fine on most engines (although arguably still wrong because the Model B view can no longer be queried once Model A is dropped) but Postgres does schema binding by default and it cant be turned off so this issue is especially pronounced in Postgres