Drop ci_runner_machines_archived table

What does this MR do and why?

Following up !188902 (merged), this MR drops the ci_runner_machines_archived table and transfers the old names of the indices to the new partitioned table (which was still using the cryptic and sometimes shortened index_ci_runner_machines_687967fa8a_on_* names. The down method rebuilds the table and relationships, but does not recreate data, since that is lost in the up method.

I didn't add specs since the documentation mentions:

We don’t enforce tests on post migrations that only perform schema changes.

Changelog: other

References

• Drop ci_runners_archived and ci_runner_machines... (#516060 - closed)

Table schemas

Before

                   Partitioned table "public.ci_runner_machines"
┌──────────────────┬──────────────────────────┬───────────┬──────────┬─────────────┐
│      Column      │           Type           │ Collation │ Nullable │   Default   │
├──────────────────┼──────────────────────────┼───────────┼──────────┼─────────────┤
│ id               │ bigint                   │           │ not null │             │
│ runner_id        │ bigint                   │           │ not null │             │
│ sharding_key_id  │ bigint                   │           │          │             │
│ created_at       │ timestamp with time zone │           │ not null │             │
│ updated_at       │ timestamp with time zone │           │ not null │             │
│ contacted_at     │ timestamp with time zone │           │          │             │
│ creation_state   │ smallint                 │           │ not null │ 0           │
│ executor_type    │ smallint                 │           │          │             │
│ runner_type      │ smallint                 │           │ not null │             │
│ config           │ jsonb                    │           │ not null │ '{}'::jsonb │
│ system_xid       │ text                     │           │ not null │             │
│ platform         │ text                     │           │          │             │
│ architecture     │ text                     │           │          │             │
│ revision         │ text                     │           │          │             │
│ ip_address       │ text                     │           │          │             │
│ version          │ text                     │           │          │             │
│ runtime_features │ jsonb                    │           │ not null │ '{}'::jsonb │
└──────────────────┴──────────────────────────┴───────────┴──────────┴─────────────┘
Partition key: LIST (runner_type)
Indexes:
    "ci_runner_machines_pkey" PRIMARY KEY, btree (id, runner_type)
    "idx_ci_runner_machines_687967fa8a_on_contacted_at_desc_id_desc" btree (contacted_at DESC, id DESC)
    "idx_ci_runner_machines_687967fa8a_on_sharding_key_where_notnull" btree (sharding_key_id) WHERE sharding_key_id IS NOT NULL
    "idx_uniq_ci_runner_machines_687967fa8a_on_runner_id_system_xid" UNIQUE, btree (runner_id, runner_type, system_xid)
    "index_ci_runner_machines_687967fa8a_on_created_at_and_id_desc" btree (created_at, id DESC)
    "index_ci_runner_machines_687967fa8a_on_major_version" btree ("substring"(version, '^\d+\.'::text), version, runner_id)
    "index_ci_runner_machines_687967fa8a_on_minor_version" btree ("substring"(version, '^\d+\.\d+\.'::text), version, runner_id)
    "index_ci_runner_machines_687967fa8a_on_patch_version" btree ("substring"(version, '^\d+\.\d+\.\d+'::text), version, runner_id)
    "index_ci_runner_machines_687967fa8a_on_version" btree (version)
    "index_ci_runner_machines_on_executor_type" btree (executor_type)
Check constraints:
    "check_3d8736b3af" CHECK (char_length(system_xid) <= 64)
    "check_5bad2a6944" CHECK (char_length(revision) <= 255)
    "check_7dc4eee8a5" CHECK (char_length(version) <= 2048)
    "check_b1e456641b" CHECK (char_length(ip_address) <= 1024)
    "check_c788f4b18a" CHECK (char_length(platform) <= 255)
    "check_f3d25ab844" CHECK (char_length(architecture) <= 255)
Triggers:
    assign_ci_runner_machines_id_trigger BEFORE INSERT ON ci_runner_machines FOR EACH ROW EXECUTE FUNCTION assign_ci_runner_machines_id_value()
    ci_runner_machines_loose_fk_trigger AFTER DELETE ON ci_runner_machines REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records_override_table('ci_runner_machines')
    table_sync_trigger_bc3e7b56bd AFTER INSERT OR DELETE OR UPDATE ON ci_runner_machines FOR EACH ROW EXECUTE FUNCTION table_sync_function_e438f29263()
Number of partitions: 3 (Use \d+ to list them.)

After

                   Partitioned table "public.ci_runner_machines"
┌──────────────────┬──────────────────────────┬───────────┬──────────┬─────────────┐
│      Column      │           Type           │ Collation │ Nullable │   Default   │
├──────────────────┼──────────────────────────┼───────────┼──────────┼─────────────┤
│ id               │ bigint                   │           │ not null │             │
│ runner_id        │ bigint                   │           │ not null │             │
│ sharding_key_id  │ bigint                   │           │          │             │
│ created_at       │ timestamp with time zone │           │ not null │             │
│ updated_at       │ timestamp with time zone │           │ not null │             │
│ contacted_at     │ timestamp with time zone │           │          │             │
│ creation_state   │ smallint                 │           │ not null │ 0           │
│ executor_type    │ smallint                 │           │          │             │
│ runner_type      │ smallint                 │           │ not null │             │
│ config           │ jsonb                    │           │ not null │ '{}'::jsonb │
│ system_xid       │ text                     │           │ not null │             │
│ platform         │ text                     │           │          │             │
│ architecture     │ text                     │           │          │             │
│ revision         │ text                     │           │          │             │
│ ip_address       │ text                     │           │          │             │
│ version          │ text                     │           │          │             │
│ runtime_features │ jsonb                    │           │ not null │ '{}'::jsonb │
└──────────────────┴──────────────────────────┴───────────┴──────────┴─────────────┘
Partition key: LIST (runner_type)
Indexes:
    "ci_runner_machines_pkey" PRIMARY KEY, btree (id, runner_type)
    "index_ci_runner_machines_on_contacted_at_desc_and_id_desc" btree (contacted_at DESC, id DESC)
    "index_ci_runner_machines_on_created_at_and_id_desc" btree (created_at, id DESC)
    "index_ci_runner_machines_on_executor_type" btree (executor_type)
    "index_ci_runner_machines_on_major_version_trigram" btree ("substring"(version, '^\d+\.'::text), version, runner_id)
    "index_ci_runner_machines_on_minor_version_trigram" btree ("substring"(version, '^\d+\.\d+\.'::text), version, runner_id)
    "index_ci_runner_machines_on_patch_version_trigram" btree ("substring"(version, '^\d+\.\d+\.\d+'::text), version, runner_id)
    "index_ci_runner_machines_on_runner_id_and_type_and_system_xid" UNIQUE, btree (runner_id, runner_type, system_xid)
    "index_ci_runner_machines_on_sharding_key_id_when_not_null" btree (sharding_key_id) WHERE sharding_key_id IS NOT NULL
    "index_ci_runner_machines_on_version" btree (version)
Check constraints:
    "check_3d8736b3af" CHECK (char_length(system_xid) <= 64)
    "check_5bad2a6944" CHECK (char_length(revision) <= 255)
    "check_7dc4eee8a5" CHECK (char_length(version) <= 2048)
    "check_b1e456641b" CHECK (char_length(ip_address) <= 1024)
    "check_c788f4b18a" CHECK (char_length(platform) <= 255)
    "check_f3d25ab844" CHECK (char_length(architecture) <= 255)
Triggers:
    assign_ci_runner_machines_id_trigger BEFORE INSERT ON ci_runner_machines FOR EACH ROW EXECUTE FUNCTION assign_ci_runner_machines_id_value()
    ci_runner_machines_loose_fk_trigger AFTER DELETE ON ci_runner_machines REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records_override_table('ci_runner_machines')
Number of partitions: 3 (Use \d+ to list them.)

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.