Add foreign key constraint from ci_runner_taggings table to tags table

Similar to p_ci_build_tags, the ci_runner_taggings table should have a FK constraint to the tags table:

gitlabhq_development_ci> \d tags
+----------------+-------------------+----------------------------------------------------+
| Column         | Type              | Modifiers                                          |
|----------------+-------------------+----------------------------------------------------|
| id             | bigint            |  not null default nextval('tags_id_seq'::regclass) |
| name           | character varying |                                                    |
| taggings_count | integer           |  default 0                                         |
+----------------+-------------------+----------------------------------------------------+
Indexes:
    "tags_pkey" PRIMARY KEY, btree (id)
    "index_tags_on_name" UNIQUE, btree (name)
    "index_tags_on_name_trigram" gin (name gin_trgm_ops)
Referenced by:
    TABLE "p_ci_build_tags" CONSTRAINT "fk_rails_8284d35c66" FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
    TABLE "gitlab_partitions_dynamic.ci_build_tags_100" CONSTRAINT "fk_rails_8284d35c66" FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
    TABLE "gitlab_partitions_dynamic.ci_build_tags_101" CONSTRAINT "fk_rails_8284d35c66" FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
    TABLE "gitlab_partitions_dynamic.ci_build_tags_102" CONSTRAINT "fk_rails_8284d35c66" FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
Triggers:
    tags_loose_fk_trigger AFTER DELETE ON tags REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records()

Implementation plan

https://docs.gitlab.com/development/database/foreign_keys/#on-an-existing-column

  • N.M: Add a NOT VALID FK constraint to the column, it will also ensure there are no inconsistent records created or updated.

  • N.M: Add a data migration, to fix or clean up existing records. 2. This can be a regular or post deployment migration if the migration queries lie within the timing guidelines. 3. If not, this has to be done in a batched background migration.

    I think we can skip a migration to delete taggings from non-existent tags, since there are no such occurrences in prod (we have a belongs_to relationship between models enforcing referential integrity at the application level, and also an LFK in place):

    gitlabhq_dblab> SELECT tag_id FROM ci_runner_taggings WHERE NOT EXISTS (SELECT 1 AS one FROM tags WHERE id = tag_id)
+--------+
| tag_id |
|--------|
+--------+
SELECT 0

  • Validate the FK constraint 2. If the data migration was a regular or a post deployment migration, the constraint can be validated in the same milestone. 3. If it was a background migration, then the FK can be validated only after the BBM is finalized. This is required so that the FK validation won’t happen while the data migration is still running in background.

Edited by Pedro Pombeiro