Drop duplicate index ci_pipeline_variables

What does this MR do and why?

Following up !191736 (comment 2525118587).

This MR drops a duplicate index on the child partition ci_pipeline_variables.

We moved the partition in a different schema, so when we attempted to add this index back on each partition it used a different name and recreated it 😞

[0] pry(#<ActiveRecord::Migration>)> partition_name = 'public.ci_pipeline_variables'
[1] pry(#<ActiveRecord::Migration>)> object_name("#{partition_name}_#{index_name}", 'index') 
=> "index_ad9d0b5002"
[2] pry(#<ActiveRecord::Migration>)> partition_name = 'gitlab_partitions_dynamic.ci_pipeline_variables'
=> "gitlab_partitions_dynamic.ci_pipeline_variables"
[3] pry(#<ActiveRecord::Migration>)> object_name("#{partition_name}_#{index_name}", 'index')
=> "index_d45be46b0d"

Click to expand

gitlabhq_dblab=# \d+ gitlab_partitions_dynamic.ci_pipeline_variables
                                     Table "gitlab_partitions_dynamic.ci_pipeline_variables"
        Column        |       Type        | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
----------------------+-------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 key                  | character varying |           | not null |         | extended |             |              |
 value                | text              |           |          |         | extended |             |              |
 encrypted_value      | text              |           |          |         | extended |             |              |
 encrypted_value_salt | character varying |           |          |         | extended |             |              |
 encrypted_value_iv   | character varying |           |          |         | extended |             |              |
 variable_type        | smallint          |           | not null | 1       | plain    |             |              |
 partition_id         | bigint            |           | not null |         | plain    |             |              |
 raw                  | boolean           |           | not null | false   | plain    |             |              |
 id                   | bigint            |           | not null |         | plain    |             |              |
 pipeline_id          | bigint            |           | not null |         | plain    |             |              |
 project_id           | bigint            |           |          |         | plain    |             |              |
Partition of: p_ci_pipeline_variables FOR VALUES IN ('100', '101')
Partition constraint: ((partition_id IS NOT NULL) AND (partition_id = ANY (ARRAY['100'::bigint, '101'::bigint])))
Indexes:
    "ci_pipeline_variables_pkey" PRIMARY KEY, btree (id, partition_id)
-   "index_ad9d0b5002" btree (project_id)
    "index_d45be46b0d" btree (project_id)
    "index_pipeline_variables_on_pipeline_id_key_partition_id_unique" UNIQUE, btree (pipeline_id, key, partition_id)
Check constraints:
    "check_6e932dbabf" CHECK (project_id IS NOT NULL)
Foreign-key constraints:
    TABLE "p_ci_pipeline_variables" CONSTRAINT "fk_rails_507416c33a_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
    assign_p_ci_pipeline_variables_id_trigger BEFORE INSERT ON gitlab_partitions_dynamic.ci_pipeline_variables FOR EACH ROW EXECUTE FUNCTION assign_p_ci_pipeline_variables_id_value(), ON TABLE p_ci_pipeline_variables
Access method: heap

gitlabhq_dblab=# \d+ gitlab_partitions_dynamic.ci_pipeline_variables
                                     Table "gitlab_partitions_dynamic.ci_pipeline_variables"
        Column        |       Type        | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
----------------------+-------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 key                  | character varying |           | not null |         | extended |             |              |
 value                | text              |           |          |         | extended |             |              |
 encrypted_value      | text              |           |          |         | extended |             |              |
 encrypted_value_salt | character varying |           |          |         | extended |             |              |
 encrypted_value_iv   | character varying |           |          |         | extended |             |              |
 variable_type        | smallint          |           | not null | 1       | plain    |             |              |
 partition_id         | bigint            |           | not null |         | plain    |             |              |
 raw                  | boolean           |           | not null | false   | plain    |             |              |
 id                   | bigint            |           | not null |         | plain    |             |              |
 pipeline_id          | bigint            |           | not null |         | plain    |             |              |
 project_id           | bigint            |           |          |         | plain    |             |              |
Partition of: p_ci_pipeline_variables FOR VALUES IN ('100', '101')
Partition constraint: ((partition_id IS NOT NULL) AND (partition_id = ANY (ARRAY['100'::bigint, '101'::bigint])))
Indexes:
    "ci_pipeline_variables_pkey" PRIMARY KEY, btree (id, partition_id)
    "index_ad9d0b5002" btree (project_id)
    "index_d45be46b0d" btree (project_id)
    "index_pipeline_variables_on_pipeline_id_key_partition_id_unique" UNIQUE, btree (pipeline_id, key, partition_id)
Check constraints:
    "check_6e932dbabf" CHECK (project_id IS NOT NULL)
Foreign-key constraints:
    TABLE "p_ci_pipeline_variables" CONSTRAINT "fk_rails_507416c33a_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
    assign_p_ci_pipeline_variables_id_trigger BEFORE INSERT ON gitlab_partitions_dynamic.ci_pipeline_variables FOR EACH ROW EXECUTE FUNCTION assign_p_ci_pipeline_variables_id_value(), ON TABLE p_ci_pipeline_variables
Access method: heap

gitlabhq_dblab=# begin;
BEGIN
Time: 179.562 ms
gitlabhq_dblab=*# DROP INDEX IF EXISTS gitlab_partitions_dynamic.index_ad9d0b5002;
DROP INDEX
Time: 179.968 ms
gitlabhq_dblab=*# \d+ gitlab_partitions_dynamic.ci_pipeline_variables
rollback                                     Table "gitlab_partitions_dynamic.ci_pipeline_variables"
        Column        |       Type        | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
----------------------+-------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 key                  | character varying |           | not null |         | extended |             |              |
 value                | text              |           |          |         | extended |             |              |
 encrypted_value      | text              |           |          |         | extended |             |              |
 encrypted_value_salt | character varying |           |          |         | extended |             |              |
 encrypted_value_iv   | character varying |           |          |         | extended |             |              |
 variable_type        | smallint          |           | not null | 1       | plain    |             |              |
 partition_id         | bigint            |           | not null |         | plain    |             |              |
 raw                  | boolean           |           | not null | false   | plain    |             |              |
 id                   | bigint            |           | not null |         | plain    |             |              |
 pipeline_id          | bigint            |           | not null |         | plain    |             |              |
 project_id           | bigint            |           |          |         | plain    |             |              |
Partition of: p_ci_pipeline_variables FOR VALUES IN ('100', '101')
Partition constraint: ((partition_id IS NOT NULL) AND (partition_id = ANY (ARRAY['100'::bigint, '101'::bigint])))
Indexes:
    "ci_pipeline_variables_pkey" PRIMARY KEY, btree (id, partition_id)
    "index_d45be46b0d" btree (project_id)
    "index_pipeline_variables_on_pipeline_id_key_partition_id_unique" UNIQUE, btree (pipeline_id, key, partition_id)
Check constraints:
    "check_6e932dbabf" CHECK (project_id IS NOT NULL)
Foreign-key constraints:
    TABLE "p_ci_pipeline_variables" CONSTRAINT "fk_rails_507416c33a_p" FOREIGN KEY (partition_id, pipeline_id) REFERENCES p_ci_pipelines(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
    assign_p_ci_pipeline_variables_id_trigger BEFORE INSERT ON gitlab_partitions_dynamic.ci_pipeline_variables FOR EACH ROW EXECUTE FUNCTION assign_p_ci_pipeline_variables_id_value(), ON TABLE p_ci_pipeline_variables
Access method: heap