Unexpected behaviour with FULL model kinds (DuckDB+Postgres) #5083
Description
SQLMesh is attempting to delete a physical layer table, which fails in Postgres due to the virtual layer view still pointing to it. Error will look something like: cannot drop table ... because other objects depend on it
I expect SQLMesh to behave like:
- on new interval, create new physical table
- recreate view to point to new physical table
- (either now or later by janitor) delete old physical table
Right now it seems to be trying to delete the old/existing physical table first.
SQLMesh version 0.192.0
Model config looks like:
MODEL (
kind FULL,
name postgresdb.schema.table,
gateway duckdb,
dialect duckdb,
cron '@daily',
start '2025-07-28'
);
With a project config like:
config = Config(
gateways={
"athena": GatewayConfig(
connection=AthenaConnectionConfig(XXXX),
state_connection=PostgresConnectionConfig(XX),
state_schema="X",
),
"duckdb": GatewayConfig(
connection=DuckDBConnectionConfig(
catalogs={
"postgresdb": DuckDBAttachOptions(
type="postgres",
path=f"XXX"
),
},
extensions=["iceberg", "aws", "postgres", "avro", "httpfs"],
)
),
},
default_gateway="athena",
disable_anonymized_analytics=True,
gateway_managed_virtual_layer=True,
)