Skip to content

Accept on update arguments for `add_concurrent_foreign_key` migration helper

We're starting to use composable primary/foreign keys on CI resources and one of the requirements is to be able to move resources to other partitions for rebalancing. But since the partitioning key is part of the primary key, we won't be able to update it. For example, in the relation between ci_builds and ci_build_trace_chunks, if we update the partition_id on ci_builds we'd get:

ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR:  update or delete on table "ci_builds" violates foreign key constraint "fk_rails_1013b761f2_p" on table "ci_build_trace_chunks"
DETAIL:  Key (partition_id, id)=(100, 483) is still referenced from table "ci_build_trace_chunks".

and if we start from ci_build_trace_chunks we'd run into:

ActiveRecord::InvalidForeignKey: PG::ForeignKeyViolation: ERROR:  insert or update on table "ci_build_trace_chunks" violates foreign key constraint "fk_rails_1013b761f2_p"
DETAIL:  Key (partition_id, build_id)=(105, 483) is not present in table "ci_builds".

The solution here is to redefine the FKs to include the on update cascade clause:

Analogous to ON DELETE there is also ON UPDATE which is invoked when a referenced column is changed (updated). The possible actions are the same. In this case, CASCADE means that the updated values of the referenced column(s) should be copied into the referencing row(s).

https://www.postgresql.org/docs/12/ddl-constraints.html#DDL-CONSTRAINTS-FK

With the FK updated, we only need to change the partition_id on ci_builds and it will cascade to all other referenced resources.

gitlabhq_development_ci=# \d ci_build_trace_chunks
...
Foreign-key constraints:
    "fk_rails_1013b761f2" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
    "fk_rails_1013b761f2_p" FOREIGN KEY (partition_id, build_id) REFERENCES ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID

gitlabhq_development_ci=# select id, build_id, partition_id from ci_build_trace_chunks where id = 4;
 id | build_id | partition_id
----+----------+--------------
  4 |      483 |          105
(1 row)

gitlabhq_development_ci=# select id, partition_id from ci_builds where id = 483;
 id  | partition_id
-----+--------------
 483 |          105
(1 row)

gitlabhq_development_ci=# update ci_builds set partition_id = 107 where id = 483;
UPDATE 1
gitlabhq_development_ci=# select id, partition_id from ci_builds where id = 483;
 id  | partition_id
-----+--------------
 483 |          107
(1 row)

gitlabhq_development_ci=# select id, build_id, partition_id from ci_build_trace_chunks where id = 4;
 id | build_id | partition_id
----+----------+--------------
  4 |      483 |          107
(1 row)

But the current helper doesn't accept on update arguments.