Reinstate: Cells: Add composite index index_ssh_signatures_on_project_id_and_commit_sha for ssh_signatures table

Context

The original merge request !214739 (merged) was reverted in !215953 (merged) due to a production incident (#inc-6216) that caused 500 errors from SSH signature endpoints.

Original MR: !214739 (merged)
Revert MR: !215953 (merged)

Error 

ActiveRecord::RecordNotUnique
PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_ssh_signatures_on_commit_sha" (ActiveRecord::RecordNotUnique)
DETAIL:  Key (commit_sha)=(\\x112345) already exists.

Root cause

The changes introduced in !214739 (merged) include updates to SSH signature code handling along with post-migration index updates.

However, due to the nature of post-migrations, they are not guaranteed to execute at the time of new code deployment.

To ensure safe deployment, we need to separate migration from code changes and release the migration first.

What needs to be done

The original MR attempted to:

  • Drop index_ssh_signatures_on_commit_sha and redundant index_ssh_signatures_on_project_id indexes
  • Add a new composite index index_ssh_signatures_on_project_id_and_commit_sha

This change is needed for Cells to ensure that the unique index by commit_sha is still unique for the ssh_signatures table.

Before reinstating, please:

  1. Investigate why the unique constraint violation occurred (see above #583622 (closed))
  2. 18.7 - MR 1: Recreate a migration from !214739 (merged), but add extra checks to verify that the updated indexes exist or are missing. This will help prevent issues if GitLab.com applied the migration before the revert of !215953 (merged).
  3. 18.8 - MR 2: Update code that relies on new indexes

Related

Edited by 🤖 GitLab Bot 🤖