Fix ci_finished_builds engine swap on deferred post-deploy

Narendranrequested to merge
narendran-kannan/593129/fix-ci-finished-builds-engine-swap into master

What does this MR do and why?

The post-deployment migration 20260108073937_alter_ci_finished_builds_engine_with_version swaps the ci_finished_builds ClickHouse table engine from ReplacingMergeTree to ReplacingMergeTree(version, deleted) by creating a tmp table with the new engine, attaching all source partitions to it, and then swapping the tables.

On Self-Managed upgrades from 18.8.x to 18.9.x, customers hit:

Code: 122. DB::Exception: Tables have different structure. (INCOMPATIBLE_COLUMNS)

Root cause: post-deployment migrations are deferred during Self-Managed deploys (SKIP_POST_DEPLOYMENT_MIGRATIONS=true). Regular migrations with later timestamps (20260113124352 group_name, 20260115071644 projection, 20260127170903 11 new columns) therefore run before this post-deploy migration. The tmp table was built from a hardcoded CREATE TABLE statement that reflected the column list at the time the migration was authored, so it ended up missing 12 columns and one projection that the live ci_finished_builds already had. ATTACH PARTITION FROM then failed the MergeTreeData::checkStructureAndGetMergeTreeData physical-column count check.

The fix replaces the hardcoded column list with ClickHouse's CREATE TABLE ... AS source_table ENGINE = ... SETTINGS ... syntax. This clones all columns, projections, partition key, and sorting key from the live source while explicitly overriding only the engine and settings. The tmp table is now structurally identical to whatever ci_finished_builds actually looks like at run time, regardless of which regular migrations have already added columns or projections.

The migration's up/down/attach_partitions/exchange_tables/drop_tmp_table flow is otherwise unchanged.

Database changes

ClickHouse only. The post-deploy migration db/click_house/post_migrate/main/20260108073937_alter_ci_finished_builds_engine_with_version.rb is changed in-place — it has already shipped in 18.9.0 but fails on customers with deferred post-deploy ordering, so amending it is safe (the original is unrunnable on those instances anyway). No new migration files. No PostgreSQL changes.

The CREATE statement for the tmp table changes from a 50-line hardcoded definition to:

CREATE TABLE IF NOT EXISTS ci_finished_builds_tmp AS ci_finished_builds
  ENGINE = #{engine}
  SETTINGS #{settings};

SETTINGS is a full replacement (not merge), so the three settings the migration wants are restated: index_granularity = 8192, use_async_block_ids_cache = true, and conditionally deduplicate_merge_projection_mode = 'rebuild' for ClickHouse 24.1+.

Screenshots or screen recordings

N/A — backend ClickHouse migration, no UI.

References

How to set up and validate locally

The bug only reproduces when the source ci_finished_builds table has columns or projections that the migration's tmp-table definition does not. Reproduce this with a sandbox ClickHouse database that mimics the customer's state on 18.8.5 (the engine has not yet been swapped, but later columns are already added):

-- 1. In clickhouse-client, build a customer-like ci_finished_builds in a sandbox DB.
CREATE DATABASE IF NOT EXISTS repro_593129;
DROP TABLE IF EXISTS repro_593129.ci_finished_builds SYNC;

CREATE TABLE repro_593129.ci_finished_builds
(
    `id` UInt64 DEFAULT 0,
    `project_id` UInt64 DEFAULT 0,
    `finished_at` DateTime64(6, 'UTC') DEFAULT 0,
    `version` DateTime64(6, 'UTC') DEFAULT now(),
    `deleted` Bool DEFAULT false
)
ENGINE = ReplacingMergeTree
PARTITION BY toYear(finished_at)
ORDER BY (id)
SETTINGS index_granularity = 8192,
         use_async_block_ids_cache = true,
         deduplicate_merge_projection_mode = 'rebuild';

INSERT INTO repro_593129.ci_finished_builds (id, project_id, finished_at)
VALUES (1, 100, now());

-- 2. Simulate later-timestamped regular migrations that ran first.
ALTER TABLE repro_593129.ci_finished_builds
  ADD COLUMN IF NOT EXISTS `group_name` String DEFAULT '',
  ADD COLUMN IF NOT EXISTS `namespace_path` String DEFAULT '0/';

ALTER TABLE repro_593129.ci_finished_builds
  ADD PROJECTION IF NOT EXISTS projection_by_project
    ( SELECT project_id, count() AS total GROUP BY project_id );

-- 3. Run the FIXED migration's CREATE statement.
CREATE TABLE IF NOT EXISTS repro_593129.ci_finished_builds_tmp AS repro_593129.ci_finished_builds
  ENGINE = ReplacingMergeTree(version, deleted)
  SETTINGS index_granularity = 8192,
           use_async_block_ids_cache = true,
           deduplicate_merge_projection_mode = 'rebuild';

-- 4. ATTACH PARTITION (this is the step that used to fail).
ALTER TABLE repro_593129.ci_finished_builds_tmp ATTACH PARTITION 2026 FROM repro_593129.ci_finished_builds;

-- 5. Verify: the tmp table has all 7 columns and the projection cloned from the source.
SELECT count() FROM repro_593129.ci_finished_builds_tmp;  -- expect 1
SELECT engine_full FROM system.tables WHERE name = 'ci_finished_builds_tmp';
SHOW CREATE TABLE repro_593129.ci_finished_builds_tmp;

Before the fix, step 3 created a tmp table with only the original column set, and step 4 raised Code: 122. DB::Exception: Tables have different structure. (INCOMPATIBLE_COLUMNS). After the fix, both succeed.

You can also run the new RSpec suite, which exercises the same scenario end-to-end through the actual migration class:

bundle exec rspec spec/db/click_house/post_migrate/20260108073937_alter_ci_finished_builds_engine_with_version_spec.rb

The suite covers: engine swap, row preservation, SETTINGS preservation, and the regression case (extra columns + projection added before the migration runs).

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Merge request reports