Remove 27 cross-database foreign keys referencing `projects`
projects is referenced as a foreign key from other 26 other tables in a different database
The intention of this issue is to determine any risks when changing all of the below foreign keys referencing projects to become Loose foreign keys. Loose foreign keys are cleaned up asynchronously which means when we perform a DELETE FROM projects
we will queue a cleanup task that will be executed in ~5 mins to cleanup all of the rows that reference this record. This can mean there are sometimes strange UX bugs where they may see a link to something that no longer exists and clicking it may give an error or it may mean there is a background job that runs and errors due to an invalid foreign key or it may mean a 500 when rendering a certain page containing any of the below tables. Some more details about risks and mitigations are being documented in !76626 (merged) .
The groupsharding will be responsible for actually doing the work to convert the foreign keys to loose foreign keys but will likely need help from impacted teams to asses the risk and potentially determine or implement appropriate mitigation for any risks. Sometimes mitigation may mean refactoring code slightly to avoid errors or explicitly handling certain riskier cases by checking if there is a pending deletion for a record. It is also possible that we can optimize the 5 minute latency for cleanup in certain cases determined to be urgent.
-
ci_builds.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79275 (merged) -
No way for user to access once parent is deleted. Please explain: @DylanGriffith : We already delete all ci_builds before a project is deleted and we're going further to ensure you can't delete a project that has builds in !77418 (closed) as a safety net. We should convert the foreign key to a loose foreign key for consistency guarantees but ultimately it should never have anything to cleanup. -
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_pipelines.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79277 (merged) -
No way for user to access once parent is deleted. Please explain: @mbobin: pipelines are destroyed before destroying a project: https://gitlab.com/gitlab-org/gitlab/-/blob/737ecf8bb171b5256d87078d5e703b4c10723fdb/app/services/projects/destroy_service.rb#L132-173
-
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_sources_pipelines.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79062 (merged) -
No way for user to access once parent is deleted. Please explain: @mbobin
: There are foreign keys to the pipeline table and they should delete these records before the project is deleted. -
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_sources_pipelines.source_project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !78090 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_job_artifacts.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouptesting devopsverify -
MR: !79026 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: @morefice -
I think users will not be able to access [job artifacts](https://docs.gitlab.com/ee/ci/pipelines /job_artifacts.html) if a project has been deleted.
- Need to evaluate impact on object storage. Discussion in #348267 (comment 808873964)
-
-
-
ci_stages.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !78945 (merged) -
No way for user to access once parent is deleted. Please explain: @mbobin
:"fk_fb57e6cc56" FOREIGN KEY (pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
should remove these records along side with the pipelines from the pipeline destroy service. -
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_freeze_periods.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): ~"group::release" ~"devops::release" -
MR: !78155 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
@shinya.maeda
All endpoints are authorized to access the project, thus the project record is not present, users request can't reach the to-be-deleted-rows. Example
-
Possible to access once parent deleted but low user impact. Please explain: -
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_pipeline_schedules.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79270 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_runner_projects.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouprunner devopsverify -
MR: !79022 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: Currently I don't see a way to access a project from a runner directly, other than situations like counting the number of projects that a runner is associated with, where we might get inflated numbers during a few minutes. It will become possible in the near future due to an upcoming
projects
GraphQL field that will be introduced inCiRunnerType
which will allow you to query it for the associated projects, but we can try to exclude the unreachable project from the results. Another minor situation that comes to mind is thatUser.ci_owned_runners
might return runners for projects that have been deleted (exposed through REST API and used inRunnerPolicy
for CRUD permissions). -
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_resource_groups.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): ~"group::release" ~"devops::release" -
MR: !78156 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
@shinya.maeda
All endpoints are authorized to access the project, thus the project record is not present, users request can't reach the to-be-deleted-rows. Example-
Possible to access once parent deleted but low user impact. Please explain: -
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_unit_tests.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouptesting devopsverify -
MR: #235525 (closed), !78146 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: @morefice: This feature is being used to display the number of recent failures of a given test on the MR widget.
-
-
ci_variables.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution / grouppipeline authoring devopsverify -
MR: !78946 (merged) -
No way for user to access once parent is deleted. Please explain: ci_variables
are project variables. And they are only accessed via their projects. And, the removal of the variables depends on the FK. They are not being deleted explicitly via project-destroy-service. Do we need to destroy explicitly? Or are we okay to just convert FK to LFK and wait for the LFK delay?I found one thing interesting that we have
CiPlatformMetric.insert_auto_devops_platform_targets!
. It finds every project variable the keyAUTO_DEVOPS_PLATFORM_TARGET
and processes something on them.@mbobin : I think it's safe to wait because this processing doesn't try to access the project from the variable and it seems to be executed as a daily task
Overall: it looks safe to convert it to LFK
-
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_triggers.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79021 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_refs.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79065 (merged) -
No way for user to access once parent is deleted. Please explain: @mbobin
:ci_refs
are used to persist the status of branch(for sending pipeline notification emails, unlocking artifacts) and are accessed though the pipeline record once it completes. It should be safe to keep them around until the LFK triggers their removal. -
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_builds_metadata.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79039 (merged) -
No way for user to access once parent is deleted. Please explain: @mbobin
: these have an FK to theci_builds
table and this link should be enough to delete them before deleting the project. -
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_daily_build_group_report_results.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouptesting devopsverify -
MR: !24695 (merged), !78149 (merged) -
No way for user to access once parent is deleted. Please explain: @morefice We use
Ci::DailyBuildGroupReportResult.project_id
to build coverage analytic at the project level. Impossible to access the analytic page (groups/group_id/project_id/-/graphs/master/charts
) without aproject_id
. -
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_pending_builds.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !78010 (merged), !78008 (closed) -
No way for user to access once parent is deleted. Please explain: @mbobin: These records are removed through foreign key constraints from
ci_builds
: ("fk_rails_725a2644a3" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
) If we ensure that all builds are deleted before a project is removed, there should not be any problem. -
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_build_report_results.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouptesting devopsverify -
MR: !32991 (merged), !78157 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: @morefice: This feature is being used to display the persist junit data being rendered at the pipeline show page. The user will not be able to visit
/group_id/project_id/-/pipelines/pipeline_id
as the project no longer exists.
-
-
ci_subscriptions_projects.downstream_project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79024 (merged) -
No way for user to access once parent is deleted. Please explain: @mbobin
this is accessed through the project, there's no direct reference to the record. -
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_subscriptions_projects.upstream_project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79042 (merged) -
No way for user to access once parent is deleted. Please explain: @mbobin
this is accessed through the project, there's no direct reference to the record. -
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_sources_projects.source_project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79269 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_job_token_project_scope_links.source_project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79069 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_job_token_project_scope_links.target_project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79268 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_pipeline_artifacts.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouptesting devopsverify -
MR: ae485937, !78058 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: @morefice This is being for users with codequality enabled and code coverage. I think this is fine as if the project gets deleted users should not be able to access merge requests.
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
ci_project_monthly_usages.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79073 (merged) -
No way for user to access once parent is deleted. Please explain: <DETAIL>
-
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: -
@fabiopitino: The record is accessed by
project_id
throughCi::ProjectMirror.by_namespace_id
when inspecting CI/CD minutes usage of the namespace. Via GraphQL we access theCi::Minutes::NamespaceMonthlyUsage
for a given namespace and from it we can access the breakdown of usage per project. The returningProjectMonthlyUsageType
exposes the project name for each record:object.project.name
whereobject
is an instance ofCi::Minutes::ProjectMonthlyUsage
. If project disappears and returnsnil
,object.project.name
could raise an exception on.name
method. We could fix it with:object.project&.name
. - The impact should be small since this GraphQL query is used in the CI minutes quota page. The error would occur if the user visits the page while the project hasn't been removed by the LFK.
-
@fabiopitino: The record is accessed by
-
-
ci_running_builds.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !78026 (closed), !78027 (merged) -
No way for user to access once parent is deleted. Please explain: @mbobin: These records are removed through foreign key constraints from
ci_builds
: ("fk_rails_da45cfa165" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
) If we ensure that all builds are deleted before a project is removed, there should not be any problem. -
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-
-
external_pull_requests.project_id
->projects.id
-ON DELETE CASCADE
-
Best team to review (check off when reviewed): grouppipeline execution devopsverify -
MR: !79019 (merged) -
No way for user to access once parent is deleted. Please explain: @fabiopitino The external pull request is either accessed via the project or via the pipeline. In the first case if the project is deleted, it won't be accessible. In the latter case, pipelines are deleted before deleting a project so any references of ExternalPullRequest#project
won't be called. -
Possible to access once parent deleted but low user impact. Please explain: <DETAIL>
-
Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: <DETAIL>
-
Possible user impact to be evaluated or mitigated. Please explain: <DETAIL>
-