State Recovery and Manual Interventions
Relevant source files
The following files were used as context for generating this wiki page:
State recovery and manual interventions in the ClickHouse state backend involve managing pipeline bookmarks and locks within an OLAP environment. Since ClickHouse lacks traditional transactional primitives like row-level updates or primary key conflict errors, this system utilizes specific engine behaviors and best-effort locking mechanisms to ensure state consistency and allow for recovery during failures. Sources: src/meltano_state_backend_clickhouse/backend.py:1-15, README.md:31-36
The backend persists pipeline state, including incremental bookmarks and full-table markers, into ClickHouse tables. Manual interventions typically include clearing stale locks left by crashed processes or deleting/modifying state IDs to force re-syncs. Sources: README.md:3-8, tests/test_backend.py:84-86
State Storage and Recovery Logic
State is stored using the ReplacingMergeTree engine. This engine handles "updates" by keeping the row with the highest version (defined by updated_at). When recovering or reading state, the backend uses the FINAL modifier to ensure that only the most recent version of a state ID is retrieved, effectively collapsing superseded records. Sources: src/meltano_state_backend_clickhouse/backend.py:180-184, README.md:33-35
Data Schema for State
The state table structure is designed to support versioned recovery. Sources: src/meltano_state_backend_clickhouse/backend.py:209-218
| Field | Type | Description |
|---|---|---|
state_id | String | Unique identifier for the pipeline state. |
partial_state | Nullable(String) | JSON string of incomplete pipeline state. |
completed_state | Nullable(String) | JSON string of successfully persisted state bookmarks. |
updated_at | DateTime64(3) | Versioning key used by ReplacingMergeTree. |
State Retrieval Flow
The following diagram illustrates how the backend retrieves the "current" state during a recovery attempt or new run.
Lock Management and Intervention
Because ClickHouse does not support row-level locking or uniqueness constraints, the backend implements a "best-effort" advisory lock. Manual intervention may be required if a process crashes and leaves a stale lock that prevents subsequent runs. Sources: src/meltano_state_backend_clickhouse/backend.py:326-335, tests/test_backend.py:84-88
Advisory Lock Mechanism
The locking logic uses a "settle-then-decide" pattern to resolve race conditions. Sources: src/meltano_state_backend_clickhouse/backend.py:346-368
Handling Stale Locks
Locks are considered stale after 300 seconds (STALE_LOCK_SECONDS). The backend provides two recovery paths for stale locks:
- Automatic Cleanup: Every call to
acquire_locktriggers_cleanup_stale_locks, which deletes rows wherelocked_at < now() - 300s. Sources: src/meltano_state_backend_clickhouse/backend.py:316-321 - TTL Backstop: The lock table is configured with a
TTLthat allows ClickHouse to automatically reap expired locks during background merges. Sources: src/meltano_state_backend_clickhouse/backend.py:222-231
Manual Intervention Operations
Administrators can perform manual interventions using the following backend operations, which are typically triggered via the Meltano CLI but implemented in the ClickhouseStateStoreManager. Sources: src/meltano_state_backend_clickhouse/backend.py:260-295, tests/test_backend.py:73-82
| Operation | Method | Action |
|---|---|---|
| Delete State | delete(state_id) | Removes all versions of the state for a specific ID. |
| Clear All | clear_all() | Truncates the state table, removing all pipeline bookmarks. |
| List States | get_state_ids(pattern) | Retrieves state IDs, supporting glob patterns for filtering. |
| Force Update | set(state) | Directly inserts a new state row, which will supersede existing ones. |
Configuration for Interventions
Manual overrides can be configured in meltano.yml or via environment variables if the state is stored in a non-default schema or table. Sources: README.md:20-29, src/meltano_state_backend_clickhouse/backend.py:70-138
state_backend:
uri: clickhouse://user:password@host:8123/meltano
clickhouse:
schema: custom_meltano_schema
table: custom_state_table
Summary
State recovery in this backend relies on ClickHouse's ReplacingMergeTree to maintain a versioned history of bookmarks, where only the latest entry is active. Manual interventions are facilitated through specialized methods for deleting and listing state, while a robust advisory locking system prevents concurrent execution conflicts. The system is self-healing regarding stale locks through both synchronous cleanup and background TTL-based reaping. Sources: src/meltano_state_backend_clickhouse/backend.py:10-15, README.md:31-36, src/meltano_state_backend_clickhouse/backend.py:228-231