Virtual layer update error: (Execution failed for SnapshotID<xxx: 123456789>)

Hi, I'm running into issues whenever I run plan on a dev environment

here is the log output

~\Projects\sqlmesh via 🐍 v3.13.3 (sqlmesh) took 6s 
❯ sqlmesh plan dev

Differences from the `dev` environment:

Models:
└── Removed:
    └── activity__dev.test_variables
Apply - Virtual Update [y/n]: y

SKIP: No physical layer updates to perform

SKIP: No model batches to execute

Updating virtual layer  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 3/3 • 0:00:00

✔ Virtual layer updated


~\Projects\sqlmesh via 🐍 v3.13.3 (sqlmesh) took 5s 
❯ sqlmesh plan dev

Differences from the `dev` environment:

Models:
└── Added:
    └── activity__dev.test_variables
Models needing backfill:
└── activity__dev.test_variables: [recreate view]
Apply - Backfill Tables [y/n]: y

Updating physical layer ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 1/1 • 0:00:00

✔ Physical layer updated

[1/1] activity__dev.test_variables   [recreate view]   0.30s   
Executing model batches ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100.0% • 1/1 • 0:00:00                                                                                                                         
                                                                                                                                                                                                                
✔ Model batches executed

Updating virtual layer  ━━━━━━━━━━━━━━━━━━━━━━━━━━╸━━━━━━━━━━━━━ 66.7% • 2/3 • 0:00:00
Error: Execution failed for node SnapshotId<"postgres"."activity"."test_variables": 3659143814>

And here is the content of the model itself

MODEL (
  name activity.test_variables,
  kind FULL,
  grain (joint_code)
);

SELECT
  joint_code,
  COUNT(*) AS entries,
  SUM(sales) AS agg_sales
FROM commercial_feed
WHERE
  sales > @MIN_THRESHOLD_SALES
GROUP BY
  1
ORDER BY
  3 DESC

With min_threshold_sales declared within the config.yaml file.

What could be happening?

Can you please run the plan with --debug, eg sqlmesh --debug plan dev and check the output of the most recent file in logs/ ?

There should be a stack trace or some indication of what the underlying error was

Sure, here are the logs with the --debug flag enabled:

❯ sqlmesh --debug plan dev
[10:17:15] Environment Difference Summary:                                                                                                                                                       console.py:3396
           Model Difference Summary:                                                                                                                                                             console.py:3396
Apply - Virtual Update [y/n]: y
[10:17:17] Starting plan e05de81bfa38448ab468ae27a6609488                                                                                                                                        console.py:3396
           Stopping creation with success=False                                                                                                                                                  console.py:3396
                                                                                                                                                                                                 console.py:3396
           SKIP: No physical layer updates to perform                                                                                                                                                           
           SKIP: No model batches to execute                                                                                                                                                     console.py:3396
           Starting promotion for 2 snapshots                                                                                                                                                    console.py:3396
           Promoting "postgres"."activity"."sales_over_time"                                                                                                                                     console.py:3396
           Stopping promotion with success=False                                                                                                                                                 console.py:3396
           Stopping plan                                                                                                                                                                         console.py:3396
Traceback (most recent call last):
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 69, in _process_node
    self.fn(node)
    ~~~~~~~^^^^^^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 172, in <lambda>
    lambda s_id: fn(snapshots_by_id[s_id]),
                 ~~^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\snapshot\evaluator.py", line 261, in <lambda>
    lambda s: self._promote_snapshot(
              ~~~~~~~~~~~~~~~~~~~~~~^
        s,
        ^^
    ...<7 lines>...
        on_complete=on_complete,
        ^^^^^^^^^^^^^^^^^^^^^^^^
    ),
    ^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\snapshot\evaluator.py", line 985, in _promote_snapshot
    _evaluation_strategy(snapshot, adapter).promote(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        table_name=table_name,
        ^^^^^^^^^^^^^^^^^^^^^^
    ...<4 lines>...
        **render_kwargs,
        ^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\snapshot\evaluator.py", line 1443, in promote
    self.adapter.create_view(
    ~~~~~~~~~~~~~~~~~~~~~~~~^
        view_name,
        ^^^^^^^^^^
    ...<3 lines>...
        view_properties=model.render_virtual_properties(**render_kwargs),
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\shared.py", line 334, in internal_wrapper
    return func(*list_args, **kwargs)
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base_postgres.py", line 113, in create_view
    super().create_view(
    ~~~~~~~~~~~~~~~~~~~^
        view_name,
        ^^^^^^^^^^
    ...<8 lines>...
        **create_kwargs,
        ^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\shared.py", line 319, in internal_wrapper
    return func(*list_args, **kwargs)
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 1104, in create_view
    self.execute(
    ~~~~~~~~~~~~^
        exp.Create(
        ^^^^^^^^^^^
    ...<6 lines>...
        quote_identifiers=self.QUOTE_IDENTIFIERS_IN_VIEWS,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 2153, in execute
    self._execute(sql, **kwargs)
    ~~~~~~~~~~~~~^^^^^^^^^^^^^^^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\engine_adapter\base.py", line 2174, in _execute
    self.cursor.execute(sql, **kwargs)
    ~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "history_is_linear"
DETAIL:  Key (schema, parent)=(activity__dev, sql_6550f1a7eb18bf) already exists.
CONTEXT:  SQL statement "INSERT INTO "pgroll".migrations (schema, name, migration, resulting_schema, done, parent, migration_type, created_at, updated_at)
        VALUES (schemaname, migration_id, pg_catalog.json_build_object('name', migration_id, 'operations', (
                    SELECT
                        pg_catalog.json_agg(pg_catalog.json_build_object('sql', pg_catalog.json_build_object('up', pg_catalog.current_query()))))),
            "pgroll".read_schema (schemaname),
            TRUE,
            "pgroll".latest_version (schemaname),
            'inferred',
            statement_timestamp(),
            statement_timestamp())"
PL/pgSQL function raw_migration() line 68 at SQL statement


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

Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Scripts\sqlmesh.exe\__main__.py", line 10, in <module>
    sys.exit(cli())
             ~~~^^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\click\core.py", line 1442, in __call__
    return self.main(*args, **kwargs)
           ~~~~~~~~~^^^^^^^^^^^^^^^^^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\click\core.py", line 1363, in main
    rv = self.invoke(ctx)
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\click\core.py", line 1830, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\click\core.py", line 1226, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\click\core.py", line 794, in invoke
    return callback(*args, **kwargs)
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\click\decorators.py", line 34, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\cli\__init__.py", line 29, in wrapper
    return handler(sqlmesh_context, lambda: func(*args, **kwargs))
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\cli\__init__.py", line 51, in _debug_exception_handler
    return func()
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\cli\__init__.py", line 29, in <lambda>
    return handler(sqlmesh_context, lambda: func(*args, **kwargs))
                                            ~~~~^^^^^^^^^^^^^^^^^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\analytics\__init__.py", line 82, in wrapper
    return func(*args, **kwargs)
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\cli\main.py", line 481, in plan
    context.plan(
    ~~~~~~~~~~~~^
        environment,
        ^^^^^^^^^^^^
    ...<4 lines>...
        **kwargs,
        ^^^^^^^^^
    )
    ^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\analytics\__init__.py", line 110, in wrapper
    return func(*args, **kwargs)
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\context.py", line 1307, in plan
    self.console.plan(
    ~~~~~~~~~~~~~~~~~^
        plan_builder,
        ^^^^^^^^^^^^^
    ...<3 lines>...
        no_prompts=no_prompts if no_prompts is not None else self.config.plan.no_prompts,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\console.py", line 1638, in plan
    self._show_options_after_categorization(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        plan_builder, auto_apply, default_catalog=default_catalog, no_prompts=no_prompts
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\console.py", line 1769, in _show_options_after_categorization
    self._prompt_promote(plan_builder)
    ~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\console.py", line 1955, in _prompt_promote
    plan_builder.apply()
    ~~~~~~~~~~~~~~~~~~^^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\plan\builder.py", line 226, in apply
    self._apply(self.build())
    ~~~~~~~~~~~^^^^^^^^^^^^^^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\context.py", line 1593, in apply
    raise e
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\context.py", line 1584, in apply
    self._apply(plan, circuit_breaker)
    ~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\context.py", line 2353, in _apply
    self._scheduler.create_plan_evaluator(self).evaluate(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        plan.to_evaluatable(), circuit_breaker=circuit_breaker
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\plan\evaluator.py", line 182, in evaluate
    self._update_views(
    ~~~~~~~~~~~~~~~~~~^
        plan, snapshots, promotion_result, deployability_index_for_evaluation
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\plan\evaluator.py", line 406, in _update_views
    self._promote_snapshots(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        plan,
        ^^^^^
    ...<4 lines>...
        snapshots=snapshots,
        ^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\plan\evaluator.py", line 436, in _promote_snapshots
    self.snapshot_evaluator.promote(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        target_snapshots,
        ^^^^^^^^^^^^^^^^^
    ...<12 lines>...
        on_complete=on_complete,
        ^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\core\snapshot\evaluator.py", line 259, in promote
    concurrent_apply_to_snapshots(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        target_snapshots,
        ^^^^^^^^^^^^^^^^^
    ...<11 lines>...
        self.ddl_concurrent_tasks,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 170, in concurrent_apply_to_snapshots
    return concurrent_apply_to_dag(
        dag if not reverse_order else dag.reversed,
    ...<2 lines>...
        raise_on_error=raise_on_error,
    )
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 212, in concurrent_apply_to_dag
    ).run()
      ~~~^^
  File "C:\Users\adrivn\Projects\sqlmesh\.venv\Lib\site-packages\sqlmesh\utils\concurrency.py", line 64, in run
    self._finished_future.result()
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^
  File "C:\Users\adrivn\AppData\Local\Programs\Python\Python313\Lib\concurrent\futures\_base.py", line 456, in result
    return self.__get_result()
           ~~~~~~~~~~~~~~~~~^^
  File "C:\Users\adrivn\AppData\Local\Programs\Python\Python313\Lib\concurrent\futures\_base.py", line 401, in __get_result
    raise self._exception
sqlmesh.utils.concurrency.NodeExecutionFailedError: Execution failed for node SnapshotId<"postgres"."activity"."test_variables": 102939838>

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "history_is_linear"

This looks like an issue in your data / some trigger that is running INSERT INTO "pgroll".migrations and not SQLMesh?

This looks like an issue in your data / some trigger that is running INSERT INTO "pgroll".migrations and not SQLMesh?

Yep, you are right. There were triggers laying around that affected all schemas and tables, including those of new creation (like sqlmesh's environments). I dropped those and now the plan dev runs as expected.

Closing this, and thank you for your patience.

Navigation Menu

Repository navigation

Virtual layer update error: (Execution failed for SnapshotID<xxx: 123456789>) #4577

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Virtual layer update error: (Execution failed for SnapshotID<xxx: 123456789>) #4577

Description

Activity

erindru commented on May 29, 2025

adrivn commented on May 30, 2025

erindru commented on Jun 2, 2025

adrivn commented on Jun 3, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions