EngineAdapter.with_settings() drops pre_ping when creating the SnapshotEvaluator adapter

[jessica-ol]

Description

The pre_ping connection setting is lost when SQLMesh creates the engine adapter used by SnapshotEvaluator.

The initial adapter is correctly created with pre_ping=True. However, Context.snapshot_evaluator clones it using:

adapter.with_settings(execute_log_level=logging.INFO)

EngineAdapter.with_settings() constructs a new adapter but does not pass the existing _pre_ping value. Because the constructor defaults pre_ping to False, the cloned adapter silently has pre-ping disabled.

This means pre_ping: true does not protect model evaluation or before_all/after_all environment statements from stale connections.

Environment

• SQLMesh: 0.233.0
• Python: 3.13
• Engine: Amazon Redshift Serverless
• Redshift connector: 2.1.7
• Scheduler: built-in
• concurrent_tasks: default (4)

The same omission also appears to be present on the current main branch.

Configuration

gateways:
  redshift_prod:
    connection:
      type: redshift
      host: example.redshift-serverless.amazonaws.com
      port: 5439
      user: sqlmesh
      password: example
      database: data_core
      is_serverless: true
      timeout: 10800
      pre_ping: true

after_all:
  - "GRANT USAGE ON SCHEMA example TO ROLE example_role"

Observed behaviour

Our production sqlmesh run takes approximately two hours.

During model evaluation, the coordinator Redshift connection is idle long enough to exceed the Redshift Serverless idle-session timeout. All model evaluations complete successfully, but the connection has been closed by the server when SQLMesh attempts to execute the after_all statement.

The run then fails while beginning the after_all transaction:

Stopping evaluation with success=True

redshift_connector.error.InterfaceError:
BrokenPipe: server socket closed.

sqlmesh.utils.errors.SQLMeshError:
An error occurred during execution of the following 'after_all' statement:

GRANT USAGE ON SCHEMA example TO ROLE example_role

The traceback shows that the failure happens while the Redshift connector is executing BEGIN TRANSACTION, before the GRANT itself is executed.

Enabling pre_ping did not change this behaviour.

Minimal reproduction

The setting is lost without needing to connect to a database:

from sqlmesh.core.engine_adapter.base import EngineAdapter

adapter = EngineAdapter(
    lambda: None,
    pre_ping=True,
)

assert adapter._pre_ping is True

cloned_adapter = adapter.with_settings()

# Currently fails because cloned_adapter._pre_ping is False
assert cloned_adapter._pre_ping is True

It can also be observed through a Context:

from sqlmesh.core.context import Context

context = Context(
    paths=["path/to/project"],
    gateway="redshift_prod",
    load=False,
)

assert context.connection_config.pre_ping is True
assert context.engine_adapter._pre_ping is True

# Currently False
assert context.snapshot_evaluator.adapter._pre_ping is True

Root cause

Context.snapshot_evaluator creates its adapters using with_settings():

https://github.com/SQLMesh/sqlmesh/blob/v0.233.0/sqlmesh/core/context.py#L487-L498

The adapter constructor defaults pre_ping to False:

https://github.com/SQLMesh/sqlmesh/blob/v0.233.0/sqlmesh/core/engine_adapter/base.py#L126-L160

EngineAdapter.with_settings() copies several adapter settings, but not pre_ping:

https://github.com/SQLMesh/sqlmesh/blob/v0.233.0/sqlmesh/core/engine_adapter/base.py#L168-L191

As a result, the new adapter falls back to pre_ping=False.

Expected behaviour

An adapter created through with_settings() should preserve the original adapter’s pre_ping value unless explicitly overridden.

The adapter used by SnapshotEvaluator, including for after_all statements, should therefore have pre-ping enabled when the connection configuration contains:

pre_ping: true

Suggested fix

Preserve pre_ping when constructing the cloned adapter:

def with_settings(self, **kwargs: t.Any) -> EngineAdapter:
    extra_kwargs = {
        "null_connection": True,
        "pre_ping": kwargs.pop("pre_ping", self._pre_ping),
        "execute_log_level": kwargs.pop(
            "execute_log_level", self._execute_log_level
        ),
        "correlation_id": kwargs.pop(
            "correlation_id", self.correlation_id
        ),
        "query_execution_tracker": kwargs.pop(
            "query_execution_tracker", self._query_execution_tracker
        ),
        **self._extra_config,
        **kwargs,
    }

    ...

A regression test could verify both preservation and explicit overriding:

adapter = EngineAdapter(lambda: None, pre_ping=True)

assert adapter.with_settings()._pre_ping is True
assert adapter.with_settings(pre_ping=False)._pre_ping is False

Current workaround

We can avoid the failure by increasing the Redshift user’s idle-session timeout beyond the maximum expected SQLMesh run duration:

ALTER USER sqlmesh SESSION TIMEOUT 14400;

However, preserving pre_ping when cloning the adapter would address the underlying problem and match the documented behaviour of the setting.