Plan replaces a VIEW and CASCADE-drops dependents it won't fix later

[jleight]

Summary

With a Postgres gateway, plan will DROP ... CASCADE views in the physical layer and then not replace those downstream views causing the plan to fail.

I ran into this a few times over the last couple weeks while I've been attempting to migrate a Synapse project over to SQLMesh. This minimal reproduction fails on the first attempt. It will succeed on a second attempt unless you destroy everything and start over. However, I have managed to get my prod environment into a state where a second run also fails, though I can't figure out a minimal reproduction of that issue.

Environment

• sqlmesh 0.236.1 (also reproduced on 0.235.4)
• postgres 17 (also reproduced on 16)

Reproduction

Set up the four models:

models/raw_items.sql:

MODEL (
  name repro.raw_items,
  kind FULL,
);

SELECT
  1 AS id,
  'alpha' AS name,
  FALSE AS __is_deleted
UNION ALL
SELECT
  2 AS id,
  'beta' AS name,
  TRUE AS __is_deleted

models/stg_items_base.sql:

MODEL (
  name repro.stg_items_base,
  kind VIEW,
);

SELECT
  id,
  name,
  __is_deleted
FROM repro.raw_items

models/stg_items.sql:

MODEL (
  name repro.stg_items,
  kind VIEW,
);

SELECT
  id,
  name
FROM repro.stg_items_base
WHERE
  NOT __is_deleted

models/audit_items.sql:

MODEL (
  name repro.audit_items,
  kind INCREMENTAL_BY_PARTITION,
  partitioned_by id,
);

SELECT
  id,
  name,
  __is_deleted
FROM repro.stg_items_base

Configure a postgres gateway:

config.yaml:

gateways:
  pg:
    connection:
      type: postgres
      host: 127.0.0.1
      port: 55432
      user: sqlmesh
      password: sqlmesh
      database: sqlmesh

default_gateway: pg

model_defaults:
  dialect: postgres
  start: 2024-01-01

Run a plan:

sqlmesh plan --no-prompts --auto-apply

Expected

The plan applies cleanly and sqlmesh__repro ends up holding a physical view for both models:

• repro__stg_items_base__3104120918
• repro__stg_items__1550973986

Actual

`prod` environment will be initialized

Models:
└── Added:
    ├── repro.audit_items
    ├── repro.raw_items
    ├── repro.stg_items
    └── repro.stg_items_base
Models needing backfill:
├── repro.audit_items: [2024-01-01 - 2026-08-13]
├── repro.raw_items: [full refresh]
├── repro.stg_items: [recreate view]
└── repro.stg_items_base: [recreate view]
[1/1] repro.raw_items        [full refresh (2 rows)]                    0.06s
[1/1] repro.stg_items_base   [recreate view]                            0.05s
[1/1] repro.stg_items        [recreate view]                            0.02s
Executing model batches ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 3/3 • 0:00:00

✔ Model batches executed

[1/1] repro.audit_items   [insert partitions (2 rows)]                   0.04s
Executing model batches ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 1/1 • 0:00:00

✔ Model batches executed

Updating virtual layer  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╺━━━━━━━━━ 75.0% • 3/4 • 0:00:00
Error: Execution failed for node SnapshotId<"sqlmesh"."repro"."stg_items": 2125706226>

Details

There is a full Claude writeup with a lot more detail located in my jleight/sqlmesh-view-over-view-bug repository. That has the actual details for the code path that's causing the issue, along with some potential ideas for solutions. It also notes several other open bugs that may also be caused by this.

PR #2440 seems to be part of the root cause. The description notes:

We always want a cascade delete for physical objects, as only other physical objects within SQLMesh can refer to it at the time of deletion.

... which sounds reasonable, but SQLMesh is only checking ancestors that it needs to recreate in this case, even though it dropped descendants.

It also says:

When it comes to deleting views in the virtual layer, we still want to do this in a non-cascade fashion to prevent the accidental removal of views created outside SQLMesh that happen to reference a SQLMesh view model.

... which also sounds reasonable, but then it still CASCADEs in the virtual layer and ends up dropping downstream, non-sqlmesh-managed views. There's additional steps in my repo reproducing that.