Migration failure: insert or update on table "ci_build_tags_100" violates foreign key constraint "fk_rails_8284d35c66"

Description

The FinalizeCopyTaggingsToPCiBuildTags post migration fails due to a missing tag:

ERROR:  insert or update on table "ci_build_tags_100" violates foreign key constraint "fk_rails_8284d35c66"
DETAIL:  Key (tag_id)=(1676) is not present in table "tags".

Environment

Impacted offerings:

GitLab Self-Managed

Impacted versions:

17.4.0 and later

Solution

This is a known bug awaiting fix.

Workaround 1

Backup the PostgreSQL database before proceeding.
Download the Ruby script to your GitLab instance (or one of the rails nodes if you have multiple nodes).
```
curl -o /tmp/dedupe.rb https://gitlab.com/gitlab-org/gitlab/-/snippets/3700665/raw/main/dedupe.rb
```
Launch the rails console: gitlab-rails console
Execute the script by running:
```
load '/tmp/dedupe.rb'
```
If the ruby script ran successfully, launch the PostgreSQL console:
```
gitlab-psql
```

Drop the duplicate entries and reindex the tags:

DELETE FROM tags
WHERE id NOT IN (
    SELECT MIN(id)
    FROM tags
    GROUP BY name
);
REINDEX INDEX CONCURRENTLY index_tags_on_name;
REINDEX INDEX CONCURRENTLY index_tags_on_name_trigram;

If the process completes successfully, re-run the migration.

Workaround 2

If workaround 1 does not resolve the issue, we can add new tag_ids to the missing tags to allow the migration to complete.

Note

CI/CD jobs with these particular tags will not be retry-able.

Launch the Postgres console:
```
gitlab-psql
```

Run the following query to confirm how many records are affected by this issue:

select count(*) from taggings where not exists(select 1 from tags where tags.id = taggings.tag_id limit 1);

Re-create the missing tags to allow the background migration to finalize.

insert into tags(id, name) select tag_id, 'missing tag ' || tag_id from taggings where not exists(select 1 from tags where tags.id = taggings.tag_id limit 1) group by 1, 2;

Re-run the migration.

Cause

There is an attempt to insert or update a record in the ci_build_tags_100 partition table with a tag_id that doesn't exist in the table. In this case, the tag_id is 1676.
Upgrades to operating systems on PostgreSQL, may result in index corruption due to https://docs.gitlab.com/ee/administration/postgresql/upgrading_os.html.

Additional Information

One of the most significant problems that results from this index corruption is that runners often don't pick up jobs that have tags. The tags table UNIQUE INDEX on name no longer functions properly.
The ci_build_tags_100 is a partition of the p_ci_build_tags table that was created in migration CreatePartitionsForPCiBuildTags.
The foreign key constraint fk_rails_8284d35c66 was added in migration AddFkFromPCiBuildTagsToTags and it enforces that every tag_id in the p_ci_build_tags table must exist in the tags table.
The Ci::Tag model uses the tags table to store CI tag data and the Ci::BuildTag model (p_ci_build_tags table) establishes a relationship between CI builds and tags.
The taggings table is being dropped in a future release.