GroupDestroyWorker failure - ci_pipeline_variables

Summary

Related to &7171

On Gitlab.com, GroupDestroyWorker sometimes fails due to a non-deleteable project. The failures are tracked in this Kibana dashboard.

We have narrowed down the failures to ten distinct Project#delete_error values. This issue deals with project deletion errors due to

PG::QueryCanceled: ERROR:  canceling statement due to statement timeout
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."ci_pipeline_variables" WHERE $1 OPERATOR(pg_catalog.=) "pipeline_id""

The full list of delete_errors can be found here: https://gitlab.com/gitlab-org/gitlab/-/issues/342692#note_737332055

Impact

Recommendation

In the past week, GroupDestroyWorker has failed ~1500 times due to 138 projects attempting to be deleted over and over again.

Investigation paths

@abrandl provides several possibilities that may explain the cause of this issue in #346170 (comment 753598204)

Verification

Investigation

Description	Issue link
GroupDestroyWorker failure - ci_pipeline_variables	👈 You are here
Sidekiq Execution Apdex: slow jobs for Ci::ExternalPullRequests::CreatePipelineWorker	#349976 (closed)
Puma Errors: failing requests for GraphqlController#execute	#349973 (closed)
Puma Apdex: slow requests GET /api/:version/projects/:id/ci/lint	#349971 (closed)
Puma Errors: failing requests for GET /api/:version/projects/:id/ci/lint	#349972 (closed)
Puma Errors: failing requests for Projects::Ci::LintsController#create	#349974 (closed)
Puma Errors: failing requests Projects::Ci::PipelineEditorController#show	#349975 (closed)
Puma Apdex: slow requests GET /api/:version/projects/:id/variables	#349970 (closed)
Puma Apdex: slow requests for GET /api/:version/projects/:id/pipelines/:pipeline_id/variables	#349969 (closed)
Puma Apdex: slow requests for GraphqlController#execute: getCiConfigData and getPipeline	#349968 (closed)
Puma Apdex: failing requests for GraphqlController#execute: getCiConfigData - conversion exceptions	#362547 (closed)