ClickHouse / ClickHouse/dbt-clickhouse
Race condition: catchup insert for refreshable MV with external target sometimes fails with TABLE_IS_READ_ONLY (error 242)
- Dominant language
- Python
- Stars
- 362
- Forks
- 176
- Avg merge
- 2d 10h
- Merged PRs (30d)
- 8
Description
### Describe the bug
The `materialized_view` materialization in external-target mode (`materialization_target_table()`) has a race condition when the MV is **refreshable**. On first creation, the materialization runs two statements back to back:
1. `CREATE MATERIALIZED VIEW REFRESH EVERY ... TO AS `
2. A catchup insert: `INSERT INTO (...) ` (enabled by default via `catchup: True`)
A refreshable MV created without the `EMPTY` keyword schedules its **first refresh immediately after creation**, in the background. For a non-`APPEND` refreshable MV, a refresh does not insert into the target table — it builds a temporary table, then runs `EXCHANGE TABLES` with the target and **drops the old target table**.
These two operations race. If the initial refresh completes its exchange while the catchup insert is still running, the insert is bound to the old (now dropped) table object and fails:
```
Code: 242. DB::Exception: Table is shutting down (zookeeper path: /clickhouse/tables//default). (TABLE_IS_READ_ONLY)
```
Beyond the race, the catchup insert is inherently wrong for refreshable MVs:
- **Non-`APPEND`:** even when the insert wins the race, its rows are discarded moments later when the initial refresh exchanges the target table. The insert is dead work.
- **`APPEND`:** the initial refresh inserts the query result too, so the catchup insert **duplicates the data**.
### Steps to reproduce
Flaky failure of `tests/integration/adapter/materialized_view/test_refreshable_materialized_view_external_target.py::TestBasicExternalTargetRefreshableMV::test_create` against ClickHouse Cloud, e.g. [this CI run](https://github.com/ClickHouse/dbt-clickhouse/actions/runs/32855404042/job/97826025569). It reproduces intermittently because it only fails when the initial refresh finishes inside the short window of the catchup insert. Cloud (SharedMergeTree, multiple replicas, fast refresh scheduling) makes the race much more likely than a local single-node server, where the insert almost always wins.
Root cause location: `dbt/include/clickhouse/macros/materializations/materialized_view.sql`, in `clickhouse__materialized_view_with_external_target` — the `catchup` block runs `clickhouse__insert_into(target_table_relation, ...)` unconditionally after `clickhouse__create_mv(...)`, even when the MV is refreshable.
### Expected behaviour
`dbt run` creates the refreshable MV and its external target table reliably; the target table ends up populated exactly once (by the MV's initial refresh).
### Proposed fix
**Skipping the catchup insert alone would trade one race for another.**
The initial refresh scheduled by `CREATE MATERIALIZED VIEW ... REFRESH ...` runs asynchronously — the DDL returns immediately. Without the catchup insert, dbt marks the MV model successful right away, and a downstream model can read the target table before the first refresh finishes (on first creation: an empty table). The catchup insert is currently what gives dbt its "model done ⇒ data queryable" guarantee.
Two ways to close the gap:
**Option A — `SYSTEM WAIT VIEW .` after the `CREATE`.** Blocks until the in-flight refresh completes and throws if it failed (bonus: a broken initial refresh fails the dbt run instead of failing silently). Caveats: with `depends_on` the wait can block indefinitely, and its behavior on Cloud (refresh may run on a different replica) needs testing.
**Option B — create with `EMPTY` and keep the catchup insert.** `EMPTY` suppresses the initial refresh, so the existing insert becomes race-free and correct for both modes: non-`APPEND` isn't wiped mid-insert, `APPEND` isn't duplicated. No new wait machinery, no Cloud unknowns, composes fine with `depends_on`.
**Proposal — hybrid on the existing `catchup` config:**
- `catchup: true` (default): add `EMPTY`, keep the insert. Deterministic and no user-visible change.
- `catchup: false`: keep today's DDL so users relying on the MV self-populating after creation don't regress; optionally add `SYSTEM WAIT VIEW` later once its Cloud behavior is confirmed.
Implementation note: verify `EMPTY` isn't persisted in `create_table_query` (it's a creation-time-only modifier), since `test_update_refresh_params` asserts on the stored DDL.
### Configuration
#### Environment
* dbt version: 1.12.3
* dbt-clickhouse version: 1.10.2
* clickhouse-connect version (if using http): 1.7.2
* Python version: 3.11.16
* Operating system: Ubuntu (GitHub Actions runner)
#### ClickHouse server
* ClickHouse Server version: ClickHouse Cloud (SharedMergeTree) 26.3
* `CREATE TABLE` statements for tables involved:
```sql
-- target table (dbt model, materialized='table')
CREATE TABLE .hackers_target (department String, average Float64)
ENGINE = MergeTree ORDER BY (department);
-- MV (dbt model, materialized='materialized_view', refreshable={"interval": "EVERY 2 MINUTE"})
CREATE MATERIALIZED VIEW .hackers
REFRESH EVERY 2 MINUTE
TO .hackers_target
AS SELECT department, avg(age) AS average FROM .people GROUP BY department;
```
Contributor guide
Research direction
Start in dbt/include/clickhouse/macros/materializations/materialized_view.sql, especially clickhouse__materialized_view_with_external_target and its catchup block. Run tests/integration/adapter/materialized_view/test_refreshable_materialized_view_external.py::TestBasicExternalTargetRefreshableMV::test_create, then inspect test_update_refresh_params for the stored DDL expectation. Done means refreshable external-target creation is reliable, the target is populated exactly once, and the existing DDL assertion remains correct.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- clickhouse, python, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 64/100