Remove x509 records with NULL project_id before adding constraints
What does this MR do and why?
Contributes to #553481
Problem
After BBM migration to populate project_id in x509_certificates and
x509_issuers there are still some records that don't have a
project_id.
It happened because x509_commit_signatures that BBM used to populate
project_id didn't have related commit signatures for some certificates and
issuers. Projects with these commits were probably deleted and left those orphaned records in the database.
Solution
Remove orphaned x509_certificates and x509_issuers records that have
NULL project_id values. These records cannot be properly associated
with projects and would violate the upcoming NOT NULL constraints.
These records can safely be removed, as the application automatically creates them if they’re missing. They serve as a cache to reduce Gitaly calls when loading commit lists.
Database
Data to remove:
- 456 records in
x509_certificates: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/45066/commands/138340 - 24 records in
x509_issuers: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/45066/commands/138341
This data can safely be removed, as it will automatically be recreated upon accessing X509 signed commits.
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Related to #553481