sqlmesh icon indicating copy to clipboard operation
sqlmesh copied to clipboard
verified publisher icon TobikoData

SQLMesh generates DROP TABLE for PostgreSQL views when using DuckDB engine

Open juqkai opened this issue 3 months ago • 0 comments

Describe the bug

I am using SQLMesh with DuckDB as an intermediate engine to transfer data from a MySQL source to a PostgreSQL destination. This setup is necessary because SQLMesh does not directly support cross-engine reads and writes within a single model.

The issue stems from a known bug in DuckDB's postgres_scanner extension, which incorrectly identifies PostgreSQL views as BASE TABLE instead of VIEW. This is documented in the DuckDB repository here: https://github.com/duckdb/duckdb-postgres/issues/269

This incorrect type information is then passed to SQLMesh. When SQLMesh needs to manage database objects (for example, when applying a plan that replaces an existing model), it relies on the object type reported by the engine to generate the correct DROP statement.

Because DuckDB reports my PostgreSQL view as a TABLE, SQLMesh generates a DROP TABLE IF EXISTS "dwd__dev"."scm_contract" command. This command subsequently fails when executed against PostgreSQL because scm_contract is actually a VIEW, leading to an exception that halts the process.

To Reproduce

  1. Set up a SQLMesh project using DuckDB as the default engine.
  2. Within DuckDB, attach a PostgreSQL database that contains a view.
  3. Create a SQLMesh model that is materialized as a view in the target PostgreSQL database.
  4. Run sqlmesh plan to create the view. This will succeed.
  5. Make any change to the model's SQL definition.
  6. Run sqlmesh plan again. When the plan attempts to apply the changes, it will try to drop the old version of the view in PostgreSQL.
  7. The operation fails because SQLMesh issues a DROP TABLE command against the view.

Expected behavior

SQLMesh should ideally generate the correct DDL statement (DROP VIEW) for the object in the target database, perhaps by querying the target database's catalog directly before dropping, rather than relying solely on the intermediate engine's (DuckDB's) description of the object.

Actual behavior

SQLMesh generates an incorrect DROP TABLE statement for a PostgreSQL view, causing the plan application to fail with an error because the object types do not match.

Environment (please complete the following information):

SQLMesh version: [0.217.0]

Database engines: PostgreSQL [14.1]

OS: [windwos 10]

2025-09-23 14:39:50,579 - MainThread - sqlmesh.core.snapshot.evaluator - INFO - Updating view 'dm.dwd__dev.scm_contract' to point at table 'dm.sqlmesh__dwd.dwd__scm_contract__107443729' (evaluator.py:1732)
2025-09-23 14:39:50,580 - MainThread - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: /* SQLMESH_PLAN: c3d1fd9d53a5463191ab694991f30470 */ SELECT CURRENT_CATALOG() (base.py:2466)
2025-09-23 14:39:50,581 - MainThread - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: /* SQLMESH_PLAN: c3d1fd9d53a5463191ab694991f30470 */ SELECT CURRENT_CATALOG() (base.py:2466)
2025-09-23 14:39:50,582 - MainThread - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: /* SQLMESH_PLAN: c3d1fd9d53a5463191ab694991f30470 */ SELECT table_name AS name, table_schema AS schema, CASE table_type WHEN 'BASE TABLE' THEN 'table' WHEN 'VIEW' THEN 'view' WHEN 'LOCAL TEMPORARY' THEN 'table' END AS type FROM system.information_schema.tables WHERE (table_catalog = 'dm' AND table_schema = 'dwd__dev') AND table_name IN ('scm_contract') (base.py:2466)
2025-09-23 14:39:53,936 - MainThread - sqlmesh.core.engine_adapter.base - WARNING - Target data object '"dm"."dwd__dev"."scm_contract"' is a table and not a view, dropping it (base.py:2732)
2025-09-23 14:39:53,936 - MainThread - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: /* SQLMESH_PLAN: c3d1fd9d53a5463191ab694991f30470 */ DROP TABLE IF EXISTS "dm"."dwd__dev"."scm_contract" (base.py:2466)
2025-09-23 14:39:54,091 - MainThread - sqlmesh.core.context - INFO - Plan application failed. (context.py:1735)
Traceback (most recent call last):
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 234, in sequential_apply_to_dag
    fn(node)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 172, in <lambda>
    lambda s_id: fn(snapshots_by_id[s_id]),
                 ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\snapshot\evaluator.py", line 311, in <lambda>
    lambda s: self._promote_snapshot(
              ^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\snapshot\evaluator.py", line 1184, in _promote_snapshot
    _evaluation_strategy(snapshot, adapter).promote(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\snapshot\evaluator.py", line 1743, in promote
    self.adapter.create_view(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\shared.py", line 312, in internal_wrapper
    return func(*list_args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 1303, in create_view
    self.drop_data_object_on_type_mismatch(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 2738, in drop_data_object_on_type_mismatch
    self.drop_data_object(data_object)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 1080, in drop_data_object
    self.drop_table(data_object.to_table(), exists=ignore_if_not_exists)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\shared.py", line 312, in internal_wrapper
    return func(*list_args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 1095, in drop_table
    self._drop_object(name=table_name, exists=exists, **kwargs)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 1129, in _drop_object
    self.execute(exp.Drop(this=exp.to_table(name), kind=kind, exists=exists, **drop_args))
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 2443, in execute
    self._execute(sql, track_rows_processed, **kwargs)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 2475, in _execute
    self.cursor.execute(sql, **kwargs)
duckdb.duckdb.Error: Failed to execute query "BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DROP Table  IF EXISTS "dwd__dev"."scm_contract"": ERROR:  "scm_contract" is not a table
HINT:  Use DROP VIEW to remove a view.


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

Traceback (most recent call last):
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\context.py", line 1727, in apply
    self._apply(plan, circuit_breaker)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\context.py", line 2519, in _apply
    self._scheduler.create_plan_evaluator(self).evaluate(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\plan\evaluator.py", line 107, in evaluate
    self._evaluate_stages(plan_stages, plan)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\plan\evaluator.py", line 127, in _evaluate_stages
    handler(stage, plan)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\plan\evaluator.py", line 352, in visit_virtual_layer_update_stage
    self._promote_snapshots(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\plan\evaluator.py", line 387, in _promote_snapshots
    self.snapshot_evaluator.promote(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\snapshot\evaluator.py", line 309, in promote
    concurrent_apply_to_snapshots(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 170, in concurrent_apply_to_snapshots
    return concurrent_apply_to_dag(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 205, in concurrent_apply_to_dag
    return sequential_apply_to_dag(dag, fn, raise_on_error)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 240, in sequential_apply_to_dag
    raise error
sqlmesh.utils.concurrency.NodeExecutionFailedError: Execution failed for node SnapshotId<"dm"."dwd"."scm_contract": 2088286219>
2025-09-23 14:39:54,093 - MainThread - sqlmesh.cli - ERROR - Unhandled exception (__init__.py:53)
Traceback (most recent call last):
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 234, in sequential_apply_to_dag
    fn(node)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 172, in <lambda>
    lambda s_id: fn(snapshots_by_id[s_id]),
                 ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\snapshot\evaluator.py", line 311, in <lambda>
    lambda s: self._promote_snapshot(
              ^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\snapshot\evaluator.py", line 1184, in _promote_snapshot
    _evaluation_strategy(snapshot, adapter).promote(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\snapshot\evaluator.py", line 1743, in promote
    self.adapter.create_view(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\shared.py", line 312, in internal_wrapper
    return func(*list_args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 1303, in create_view
    self.drop_data_object_on_type_mismatch(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 2738, in drop_data_object_on_type_mismatch
    self.drop_data_object(data_object)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 1080, in drop_data_object
    self.drop_table(data_object.to_table(), exists=ignore_if_not_exists)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\shared.py", line 312, in internal_wrapper
    return func(*list_args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 1095, in drop_table
    self._drop_object(name=table_name, exists=exists, **kwargs)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 1129, in _drop_object
    self.execute(exp.Drop(this=exp.to_table(name), kind=kind, exists=exists, **drop_args))
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 2443, in execute
    self._execute(sql, track_rows_processed, **kwargs)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 2475, in _execute
    self.cursor.execute(sql, **kwargs)
duckdb.duckdb.Error: Failed to execute query "BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DROP Table  IF EXISTS "dwd__dev"."scm_contract"": ERROR:  "scm_contract" is not a table
HINT:  Use DROP VIEW to remove a view.


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

Traceback (most recent call last):
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\cli\__init__.py", line 51, in _debug_exception_handler
    return func()
           ^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\cli\__init__.py", line 29, in <lambda>
    return handler(sqlmesh_context, lambda: func(*args, **kwargs))
                                            ^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\analytics\__init__.py", line 82, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\cli\main.py", line 561, in plan
    context.plan(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\analytics\__init__.py", line 110, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\context.py", line 1392, in plan
    self.console.plan(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\console.py", line 1853, in plan
    self._show_options_after_categorization(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\console.py", line 1982, in _show_options_after_categorization
    plan_builder.apply()
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\plan\builder.py", line 273, in apply
    self._apply(self.build())
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\context.py", line 1736, in apply
    raise e
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\context.py", line 1727, in apply
    self._apply(plan, circuit_breaker)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\context.py", line 2519, in _apply
    self._scheduler.create_plan_evaluator(self).evaluate(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\plan\evaluator.py", line 107, in evaluate
    self._evaluate_stages(plan_stages, plan)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\plan\evaluator.py", line 127, in _evaluate_stages
    handler(stage, plan)
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\plan\evaluator.py", line 352, in visit_virtual_layer_update_stage
    self._promote_snapshots(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\plan\evaluator.py", line 387, in _promote_snapshots
    self.snapshot_evaluator.promote(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\core\snapshot\evaluator.py", line 309, in promote
    concurrent_apply_to_snapshots(
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 170, in concurrent_apply_to_snapshots
    return concurrent_apply_to_dag(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 205, in concurrent_apply_to_dag
    return sequential_apply_to_dag(dag, fn, raise_on_error)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "E:\tonglkjSqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 240, in sequential_apply_to_dag
    raise error
sqlmesh.utils.concurrency.NodeExecutionFailedError: Execution failed for node SnapshotId<"dm"."dwd"."scm_contract": 2088286219>

juqkai avatar Sep 25 '25 02:09 juqkai