Include all finished deployments in Environments stop actions (!141216) · Merge requests · GitLab.org / GitLab

Pam Artiaga requested to merge 435128-environment-stop-finished-deployments into master Jan 08, 2024

What does this MR do and why?

An Environment has "stop jobs" defined through the on_stop configuration in the .gitlab-ci.yml. These "stop jobs" (or stop_actions) are run when an Environment is stopped. Currently, the Environment#stop_actions picks up the stop jobs of successful deployments in the latest successful pipeline.

This means that:

if an Environment is new and its only pipeline has failed, Environment#stop_actions is empty
if an Environment is old and has multiple pipelines, but its latest pipeline failed, it will pick up the stop jobs of a previously successful pipeline

This MR changes the Environment#stop_actions so that it picks up the stop jobs of finished deployments in the latest finished pipeline. "Finished" here means successful, failed, or canceled.

We are introducing this change to fix Environment `auto_stop_in` doesn't engage when ... (#382549 - closed). Essentially, customers expect an Environment's stop jobs to run even if the deployment has failed because a failed deployment could still have running resources that need to be stopped. For more information on why we arrived on this solution, please read the investigation thread: #429616 (comment 1663211493).

This change is also a first step towards better Environment state representation (#368558).

This change is behind a Feature Flag (environment_stop_actions_include_all_finished_deployments)

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

For all deployments, the "before the change" and "FF disabled" behavior are the same.

Successful deployment

For successful deployments, the "before the change", "FF disabled", and "after with FF enabled" behavior are the same.

Area	Before or FF disabled	After with FF enabled
Stop env popup
Rails console

Failed deployment

Area	Before or FF disabled	After with FF enabled
Stop env popup
Rails console

Canceled deployment

Area	Before or FF disabled	After with FF enabled
Stop env popup
Rails console

How to set up and validate locally

Setup

In a new or existing project, set up the .gitlab-ci.yml to create 3 different environments with successful, failed, and canceled deployments. Each of these environments must have a connected "stop job".

configuration for successful deployment

deploy_review:
  stage: deploy
  script:
    - echo "Spinning up Review Environment for review/successful_deployment..."
  environment:
    name: review/successful_deployment
    action: start
    on_stop: stop_review
    auto_stop_in: 1 day

stop_review:
  stage: deploy
  script:
    - echo "Tearing down Review Environment successful_deployment."
  when: manual
  environment:
    name: review/successful_deployment
    action: stop

configuration for failed deployment

deploy_review:
  stage: deploy
  script:
    - echo "Spinning up Review Environment for review/failed_deployment..."; exit 1
  environment:
    name: review/failed_deployment
    action: start
    on_stop: stop_review
    auto_stop_in: 1 day

stop_review:
  stage: deploy
  script:
    - echo "Tearing down Review Environment failed_deployment."
  when: manual
  environment:
    name: review/failed_deployment
    action: stop

configuration for canceled deployment

You must cancel the pipeline for this manually. The sleep 300 will give you time to cancel the pipeline.

deploy_review:
  stage: deploy
  script:
    - echo "Spinning up Review Environment for review/canceled_deployment..."; sleep 300
  environment:
    name: review/canceled_deployment
    action: start
    on_stop: stop_review
    auto_stop_in: 1 day

stop_review:
  stage: deploy
  script:
    - echo "Tearing down Review Environment canceled_deployment."
  when: manual
  environment:
    name: review/canceled_deployment
    action: stop

Test

To test the "before" behavior, make sure that you're on the master branch.
To test the "after with FF disabled" behavior, check out 435128-environment-stop-finished-deployments and make sure that the environment_stop_actions_include_all_finished_deployments FF is disabled
To test the "after with FF enabled" bheavior, check out 435128-environment-stop-finished-deployments and make sure that the environment_stop_actions_include_all_finished_deployments FF is enabled

Test the "stop environment" popup in the Environments list.
1. Go to "Project -> Operate -> Environments"
2. Click the "Stop Environment" button
3. Check the presence of the ...no “stop environment action” being defined... warning message.
  - If "before the change" or "after with FF disabled"
    - the successful deployment should not have a warning message
    - the failed and canceled deployments should have a warning message
  - If "after with FF disabled"
    - the all finished deployments should not have a warning message
Note: you must test on the Environments list page. The single-Environment page has a bug in the stop actions popup, which will be addressed in another issue.
Test the Environment#stop_actions in the console
1. Go on the rails console (bundle exec rails console)
2. Find the relevant Environment record (e.g.: env = Environment.find(<id>))
3. Check the Environment#stop_actions (e.g.: env.stop_actions)
  - If "before the change" or "after with FF disabled"
    - the successfully deployed environment should have a stop action
    - the environments with failed or canceled deployments should have no stop actions
  - If "after with FF disabled"
    - all environments with finished deployments should have stop actions

Please see the screenshots above for an illustration of the different behaviors/outcomes.

Query Plan

These are the query steps comparison for before and after the change:

Step Before vs After

1- Query the relevant jobs in a pipeline

The IDs of the resulting jobs will be used in the next step. Environment#last_deployment&.deployable&.pipeline&.latest_successful_jobs vs
Environment#last_finished_deployment&.deployable&.pipeline&.latest_finished_jobs

2- Query the associated deployment records of the "deploy jobs"

There is some batch-loading done in rails.
The before and after query here is the same, with only the <job_ids> input (from step 1) being different.

The preloaded records are last_deployment_group_associations = {:deployable=>{:pipeline=>{:manual_actions=>[]}}}. This means all manual_actions of the deployment (including the stop_actions are preloaded. Deployment.where(deployable_type: 'CommitStatus', deployable_id: <job_ids>).preload(last_deployment_group_associations)

Step	Before vs After
1- Query the relevant jobs in a pipeline The IDs of the resulting jobs will be used in the next step.	`Environment#last_deployment&.deployable&.pipeline&.latest_successful_jobs` vs `Environment#last_finished_deployment&.deployable&.pipeline&.latest_finished_jobs`
2- Query the associated deployment records of the "deploy jobs" There is some batch-loading done in rails. The before and after query here is the same, with only the `<job_ids>` input (from step 1) being different. The preloaded records are `last_deployment_group_associations = {:deployable=>{:pipeline=>{:manual_actions=>[]}}}`. This means all `manual_actions` of the deployment (including the `stop_actions` are preloaded.	`Deployment.where(deployable_type: 'CommitStatus', deployable_id: <job_ids>).preload(last_deployment_group_associations)`

In the query plans below, I've indicated which ones apply before the change, after the change, and are the same queries before and after the change. The query plans use the GitLab.com pages environment (ID: 6444461).

Query 1-1 - Environment->deployment

Environment#last_deployment vs Environment#last_finished_deployment

last_deployment here is actually scoped by status=success. So last_deployment can be the same as last_finished_deployment because the success status is a subset of the finished statuses.

[BEFORE] Environment#last_deployment - Database Lab analysis
[AFTER] Environment#last_finished_deployment - Database Lab analysis

expand for queries

BEFORE: Environment#last_deployment

SELECT "deployments"."id", "deployments"."iid", "deployments"."project_id", "deployments"."environment_id", "deployments"."ref", "deployments"."tag", "deployments"."sha", "deployments"."user_id", "deployments"."deployable_type", "deployments"."created_at", "deployments"."updated_at", "deployments"."on_stop", "deployments"."status", "deployments"."finished_at", "deployments"."deployable_id", "deployments"."archived"
FROM "deployments"
WHERE "deployments"."environment_id" = 6444461 AND "deployments"."status" = 2
ORDER BY "deployments"."finished_at" DESC
LIMIT 1

AFTER: Environment#last_finished_deployment

SELECT "deployments"."id", "deployments"."iid", "deployments"."project_id", "deployments"."environment_id", "deployments"."ref", "deployments"."tag", "deployments"."sha", "deployments"."user_id", "deployments"."deployable_type", "deployments"."created_at", "deployments"."updated_at", "deployments"."on_stop", "deployments"."status", "deployments"."finished_at", "deployments"."deployable_id", "deployments"."archived" 
FROM "deployments" 
WHERE "deployments"."environment_id" = 6444461 AND "deployments"."status" IN (2, 3, 4) 
ORDER BY "deployments"."finished_at" DESC LIMIT 1

Query 1-2 - Environment->deployment->deployable

Environment#last_deployment&.deployable vs Environment#last_finished_deployment&.deployable

This is the same query with possibly different inputs in the WHERE clause if Environment#last_deployment and Environment#last_finished_deployment result in different records.

Database Lab analysis

expand for query

SELECT "p_ci_builds"."status", "p_ci_builds"."finished_at", "p_ci_builds"."created_at", "p_ci_builds"."updated_at", "p_ci_builds"."started_at", "p_ci_builds"."runner_id", "p_ci_builds"."coverage", "p_ci_builds"."commit_id", "p_ci_builds"."name", "p_ci_builds"."options", "p_ci_builds"."allow_failure", "p_ci_builds"."stage", "p_ci_builds"."trigger_request_id", "p_ci_builds"."stage_idx", "p_ci_builds"."tag", "p_ci_builds"."ref", "p_ci_builds"."user_id", "p_ci_builds"."type", "p_ci_builds"."target_url", "p_ci_builds"."description", "p_ci_builds"."project_id", "p_ci_builds"."erased_by_id", "p_ci_builds"."erased_at", "p_ci_builds"."artifacts_expire_at", "p_ci_builds"."environment", "p_ci_builds"."when", "p_ci_builds"."yaml_variables", "p_ci_builds"."queued_at", "p_ci_builds"."lock_version", "p_ci_builds"."coverage_regex", "p_ci_builds"."auto_canceled_by_id", "p_ci_builds"."retried", "p_ci_builds"."protected", "p_ci_builds"."failure_reason", "p_ci_builds"."scheduled_at", "p_ci_builds"."token_encrypted", "p_ci_builds"."upstream_pipeline_id", "p_ci_builds"."resource_group_id", "p_ci_builds"."waiting_for_resource_at", "p_ci_builds"."processed", "p_ci_builds"."scheduling_type", "p_ci_builds"."id", "p_ci_builds"."stage_id", "p_ci_builds"."partition_id", "p_ci_builds"."auto_canceled_by_partition_id"
FROM "p_ci_builds" WHERE "p_ci_builds"."id" = 5964032931 
LIMIT 1

Query 1-3 - Environment->deployment->deployable->pipeline

Environment#last_deployment&.deployable&.pipeline vs Environment#last_finished_deployment&.deployable&.pipeline

This is the same query with possibly different inputs in the WHERE clause if Environment#last_deployment&.deployable and Environment#last_finished_deployment&.deployable result in different records.

Database Lab analysis

expand for query

SELECT "ci_pipelines"."id", "ci_pipelines"."ref", "ci_pipelines"."sha", "ci_pipelines"."before_sha", "ci_pipelines"."created_at", "ci_pipelines"."updated_at", "ci_pipelines"."tag", "ci_pipelines"."yaml_errors", "ci_pipelines"."committed_at", "ci_pipelines"."project_id", "ci_pipelines"."status", "ci_pipelines"."started_at", "ci_pipelines"."finished_at", "ci_pipelines"."duration", "ci_pipelines"."user_id", "ci_pipelines"."lock_version", "ci_pipelines"."pipeline_schedule_id", "ci_pipelines"."source", "ci_pipelines"."config_source", "ci_pipelines"."protected", "ci_pipelines"."failure_reason", "ci_pipelines"."iid", "ci_pipelines"."merge_request_id", "ci_pipelines"."source_sha", "ci_pipelines"."target_sha", "ci_pipelines"."external_pull_request_id", "ci_pipelines"."ci_ref_id", "ci_pipelines"."locked", "ci_pipelines"."partition_id", "ci_pipelines"."auto_canceled_by_id"
FROM "ci_pipelines"
WHERE "ci_pipelines"."id" = 1143802721 AND "ci_pipelines"."partition_id" = 101
LIMIT 1

Query 1-4 - Environment->deployment->deployable->pipeline->jobs

Environment#last_deployment&.deployable&.pipeline&.latest_successful_jobs vs Environment#last_finished_deployment&.deployable&.pipeline&.latest_finished_jobs

The difference between the latest_success_jobs and latest_successful_jobs queries is in the status filter in the WHERE clause condition "p_ci_builds"."status" IN (<status>).

[BEFORE] Environment#last_deployment&.deployable&.pipeline&.latest_successful_jobs - Database Lab analysis
[AFTER] Environment#last_finished_deployment&.deployable&.pipeline&.latest_finished_jobs - Database Lab analysis

expand for queries

BEFORE Environment#last_deployment&.deployable&.pipeline&.latest_successful_jobs

SELECT "p_ci_builds"."status" AS t0_r0, "p_ci_builds"."finished_at" AS t0_r1, "p_ci_builds"."created_at" AS t0_r2, "p_ci_builds"."updated_at" AS t0_r3, "p_ci_builds"."started_at" AS t0_r4, "p_ci_builds"."runner_id" AS t0_r5, "p_ci_builds"."coverage" AS t0_r6, "p_ci_builds"."commit_id" AS t0_r7, "p_ci_builds"."name" AS t0_r8, "p_ci_builds"."options" AS t0_r9, "p_ci_builds"."allow_failure" AS t0_r10, "p_ci_builds"."stage" AS t0_r11, "p_ci_builds"."trigger_request_id" AS t0_r12, "p_ci_builds"."stage_idx" AS t0_r13, "p_ci_builds"."tag" AS t0_r14, "p_ci_builds"."ref" AS t0_r15, "p_ci_builds"."user_id" AS t0_r16, "p_ci_builds"."type" AS t0_r17, "p_ci_builds"."target_url" AS t0_r18, "p_ci_builds"."description" AS t0_r19, "p_ci_builds"."project_id" AS t0_r20, "p_ci_builds"."erased_by_id" AS t0_r21, "p_ci_builds"."erased_at" AS t0_r22, "p_ci_builds"."artifacts_expire_at" AS t0_r23, "p_ci_builds"."environment" AS t0_r24, "p_ci_builds"."when" AS t0_r25, "p_ci_builds"."yaml_variables" AS t0_r26, "p_ci_builds"."queued_at" AS t0_r27, "p_ci_builds"."lock_version" AS t0_r28, "p_ci_builds"."coverage_regex" AS t0_r29, "p_ci_builds"."auto_canceled_by_id" AS t0_r30, "p_ci_builds"."retried" AS t0_r31, "p_ci_builds"."protected" AS t0_r32, "p_ci_builds"."failure_reason" AS t0_r33, "p_ci_builds"."scheduled_at" AS t0_r34, "p_ci_builds"."token_encrypted" AS t0_r35, "p_ci_builds"."upstream_pipeline_id" AS t0_r36, "p_ci_builds"."resource_group_id" AS t0_r37, "p_ci_builds"."waiting_for_resource_at" AS t0_r38, "p_ci_builds"."processed" AS t0_r39, "p_ci_builds"."scheduling_type" AS t0_r40, "p_ci_builds"."id" AS t0_r41, "p_ci_builds"."stage_id" AS t0_r42, "p_ci_builds"."partition_id" AS t0_r43, "p_ci_builds"."auto_canceled_by_partition_id" AS t0_r44, "p_ci_builds_metadata"."project_id" AS t1_r0, "p_ci_builds_metadata"."timeout" AS t1_r1, "p_ci_builds_metadata"."timeout_source" AS t1_r2, "p_ci_builds_metadata"."interruptible" AS t1_r3, "p_ci_builds_metadata"."config_options" AS t1_r4, "p_ci_builds_metadata"."config_variables" AS t1_r5, "p_ci_builds_metadata"."has_exposed_artifacts" AS t1_r6, "p_ci_builds_metadata"."environment_auto_stop_in" AS t1_r7, "p_ci_builds_metadata"."expanded_environment_name" AS t1_r8, "p_ci_builds_metadata"."secrets" AS t1_r9, "p_ci_builds_metadata"."build_id" AS t1_r10, "p_ci_builds_metadata"."id" AS t1_r11, "p_ci_builds_metadata"."runtime_runner_features" AS t1_r12, "p_ci_builds_metadata"."id_tokens" AS t1_r13, "p_ci_builds_metadata"."partition_id" AS t1_r14, "p_ci_builds_metadata"."debug_trace_enabled" AS t1_r15

FROM "p_ci_builds"
INNER JOIN "p_ci_builds_metadata" ON "p_ci_builds_metadata"."build_id" = "p_ci_builds"."id"

WHERE "p_ci_builds"."type" IN ('Ci::Processable', 'Ci::Build')
  AND "p_ci_builds"."commit_id" = 1143802721 /* this is the pipeline ID */
  AND "p_ci_builds"."partition_id" = 101 /* this is the pipeline.partition_id */
  AND ("p_ci_builds"."retried" = FALSE OR "p_ci_builds"."retried" IS NULL)
  AND ("p_ci_builds"."status" IN ('success'))

AFTER Environment#last_finished_deployment&.deployable&.pipeline&.latest_finished_jobs

SELECT "p_ci_builds"."status" AS t0_r0, "p_ci_builds"."finished_at" AS t0_r1, "p_ci_builds"."created_at" AS t0_r2, "p_ci_builds"."updated_at" AS t0_r3, "p_ci_builds"."started_at" AS t0_r4, "p_ci_builds"."runner_id" AS t0_r5, "p_ci_builds"."coverage" AS t0_r6, "p_ci_builds"."commit_id" AS t0_r7, "p_ci_builds"."name" AS t0_r8, "p_ci_builds"."options" AS t0_r9, "p_ci_builds"."allow_failure" AS t0_r10, "p_ci_builds"."stage" AS t0_r11, "p_ci_builds"."trigger_request_id" AS t0_r12, "p_ci_builds"."stage_idx" AS t0_r13, "p_ci_builds"."tag" AS t0_r14, "p_ci_builds"."ref" AS t0_r15, "p_ci_builds"."user_id" AS t0_r16, "p_ci_builds"."type" AS t0_r17, "p_ci_builds"."target_url" AS t0_r18, "p_ci_builds"."description" AS t0_r19, "p_ci_builds"."project_id" AS t0_r20, "p_ci_builds"."erased_by_id" AS t0_r21, "p_ci_builds"."erased_at" AS t0_r22, "p_ci_builds"."artifacts_expire_at" AS t0_r23, "p_ci_builds"."environment" AS t0_r24, "p_ci_builds"."when" AS t0_r25, "p_ci_builds"."yaml_variables" AS t0_r26, "p_ci_builds"."queued_at" AS t0_r27, "p_ci_builds"."lock_version" AS t0_r28, "p_ci_builds"."coverage_regex" AS t0_r29, "p_ci_builds"."auto_canceled_by_id" AS t0_r30, "p_ci_builds"."retried" AS t0_r31, "p_ci_builds"."protected" AS t0_r32, "p_ci_builds"."failure_reason" AS t0_r33, "p_ci_builds"."scheduled_at" AS t0_r34, "p_ci_builds"."token_encrypted" AS t0_r35, "p_ci_builds"."upstream_pipeline_id" AS t0_r36, "p_ci_builds"."resource_group_id" AS t0_r37, "p_ci_builds"."waiting_for_resource_at" AS t0_r38, "p_ci_builds"."processed" AS t0_r39, "p_ci_builds"."scheduling_type" AS t0_r40, "p_ci_builds"."id" AS t0_r41, "p_ci_builds"."stage_id" AS t0_r42, "p_ci_builds"."partition_id" AS t0_r43, "p_ci_builds"."auto_canceled_by_partition_id" AS t0_r44, "p_ci_builds_metadata"."project_id" AS t1_r0, "p_ci_builds_metadata"."timeout" AS t1_r1, "p_ci_builds_metadata"."timeout_source" AS t1_r2, "p_ci_builds_metadata"."interruptible" AS t1_r3, "p_ci_builds_metadata"."config_options" AS t1_r4, "p_ci_builds_metadata"."config_variables" AS t1_r5, "p_ci_builds_metadata"."has_exposed_artifacts" AS t1_r6, "p_ci_builds_metadata"."environment_auto_stop_in" AS t1_r7, "p_ci_builds_metadata"."expanded_environment_name" AS t1_r8, "p_ci_builds_metadata"."secrets" AS t1_r9, "p_ci_builds_metadata"."build_id" AS t1_r10, "p_ci_builds_metadata"."id" AS t1_r11, "p_ci_builds_metadata"."runtime_runner_features" AS t1_r12, "p_ci_builds_metadata"."id_tokens" AS t1_r13, "p_ci_builds_metadata"."partition_id" AS t1_r14, "p_ci_builds_metadata"."debug_trace_enabled" AS t1_r15

FROM "p_ci_builds"
INNER JOIN "p_ci_builds_metadata" ON "p_ci_builds_metadata"."build_id" = "p_ci_builds"."id"

WHERE "p_ci_builds"."type" IN ('Ci::Processable', 'Ci::Build')
  AND "p_ci_builds"."commit_id" = 1143802721 /* this is the pipeline ID */
  AND "p_ci_builds"."partition_id" = 101 /* this is the pipeline.partition_id */
  AND ("p_ci_builds"."retried" = FALSE OR "p_ci_builds"."retried" IS NULL)
  AND ("p_ci_builds"."status" IN ('success','failed','canceled'))

Query 2-1 - Fetching deployment records given the latest_successful_jobs (before) or latest_finished_jobs (after)

Deployment.where(deployable_type: 'CommitStatus', deployable_id: <job_ids>)

This is the same query before and after the change, but with different inputs. The Environment#latest_finished_jobs (after) will often have more records than Environment#latest_successful_jobs.

[BEFORE] with Environment#latest_successful_jobs as input - Database Lab analysis
[AFTER] with Environment#latest_finished_jobs as input - Database Lab analysis

expand for query

SELECT "deployments"."id", "deployments"."iid", "deployments"."project_id", "deployments"."environment_id", "deployments"."ref", "deployments"."tag", "deployments"."sha", "deployments"."user_id", "deployments"."deployable_type", "deployments"."created_at", "deployments"."updated_at", "deployments"."on_stop", "deployments"."status", "deployments"."finished_at", "deployments"."deployable_id", "deployments"."archived" FROM "deployments"
WHERE "deployments"."deployable_type" = 'CommitStatus'

  /* with Environment#latest_successful_jobs as input */
  AND "deployments"."deployable_id" IN (5964032931, 5964032928, 5964032879, 5964032881, 5964032884, 5964032573)

  /* with Environment#latest_finished_jobs as input */
  /* AND "deployments"."deployable_id" IN (5964032931, 5964032928, 5964032879, 5964032881, 5964032884, 5964032573, 5964032926, 5964032873, 5964032874) */

Query 2-2 - preloaded associations

The preloaded associations are {:deployable=>{:pipeline=>{:manual_actions=>[]}}}

Out of all the Environment#latest_successful_jobs or Environment#latest_finished_jobs, only 1 has a deployment (meaning only 1 is a "deploy job"), so we are only preloading 1 record here. This is often the case since most pipelines would only have 1 deploy job.

deployable - Database Lab analysis
deployable -> pipeline - Database Lab analysis
deployable -> pipeline -> manual actions - Database Lab analysis

expand for query

/* deployable */

SELECT "p_ci_builds"."status", "p_ci_builds"."finished_at", "p_ci_builds"."created_at", "p_ci_builds"."updated_at", "p_ci_builds"."started_at", "p_ci_builds"."runner_id", "p_ci_builds"."coverage", "p_ci_builds"."commit_id", "p_ci_builds"."name", "p_ci_builds"."options", "p_ci_builds"."allow_failure", "p_ci_builds"."stage", "p_ci_builds"."trigger_request_id", "p_ci_builds"."stage_idx", "p_ci_builds"."tag", "p_ci_builds"."ref", "p_ci_builds"."user_id", "p_ci_builds"."type", "p_ci_builds"."target_url", "p_ci_builds"."description", "p_ci_builds"."project_id", "p_ci_builds"."erased_by_id", "p_ci_builds"."erased_at", "p_ci_builds"."artifacts_expire_at", "p_ci_builds"."environment", "p_ci_builds"."when", "p_ci_builds"."yaml_variables", "p_ci_builds"."queued_at", "p_ci_builds"."lock_version", "p_ci_builds"."coverage_regex", "p_ci_builds"."auto_canceled_by_id", "p_ci_builds"."retried", "p_ci_builds"."protected", "p_ci_builds"."failure_reason", "p_ci_builds"."scheduled_at", "p_ci_builds"."token_encrypted", "p_ci_builds"."upstream_pipeline_id", "p_ci_builds"."resource_group_id", "p_ci_builds"."waiting_for_resource_at", "p_ci_builds"."processed", "p_ci_builds"."scheduling_type", "p_ci_builds"."id", "p_ci_builds"."stage_id", "p_ci_builds"."partition_id", "p_ci_builds"."auto_canceled_by_partition_id"
FROM "p_ci_builds" 
WHERE "p_ci_builds"."id" = 5964032931

/* deployable -> pipeline */

SELECT "ci_pipelines"."id", "ci_pipelines"."ref", "ci_pipelines"."sha", "ci_pipelines"."before_sha", "ci_pipelines"."created_at", "ci_pipelines"."updated_at", "ci_pipelines"."tag", "ci_pipelines"."yaml_errors", "ci_pipelines"."committed_at", "ci_pipelines"."project_id", "ci_pipelines"."status", "ci_pipelines"."started_at", "ci_pipelines"."finished_at", "ci_pipelines"."duration", "ci_pipelines"."user_id", "ci_pipelines"."lock_version", "ci_pipelines"."pipeline_schedule_id", "ci_pipelines"."source", "ci_pipelines"."config_source", "ci_pipelines"."protected", "ci_pipelines"."failure_reason", "ci_pipelines"."iid", "ci_pipelines"."merge_request_id", "ci_pipelines"."source_sha", "ci_pipelines"."target_sha", "ci_pipelines"."external_pull_request_id", "ci_pipelines"."ci_ref_id", "ci_pipelines"."locked", "ci_pipelines"."partition_id", "ci_pipelines"."auto_canceled_by_id"
FROM "ci_pipelines"
WHERE "ci_pipelines"."partition_id" = 101 AND "ci_pipelines"."id" = 1143802721

/* deployable -> pipeline -> manual_actions */

SELECT "p_ci_builds"."status", "p_ci_builds"."finished_at", "p_ci_builds"."created_at", "p_ci_builds"."updated_at", "p_ci_builds"."started_at", "p_ci_builds"."runner_id", "p_ci_builds"."coverage", "p_ci_builds"."commit_id", "p_ci_builds"."name", "p_ci_builds"."options", "p_ci_builds"."allow_failure", "p_ci_builds"."stage", "p_ci_builds"."trigger_request_id", "p_ci_builds"."stage_idx", "p_ci_builds"."tag", "p_ci_builds"."ref", "p_ci_builds"."user_id", "p_ci_builds"."type", "p_ci_builds"."target_url", "p_ci_builds"."description", "p_ci_builds"."project_id", "p_ci_builds"."erased_by_id", "p_ci_builds"."erased_at", "p_ci_builds"."artifacts_expire_at", "p_ci_builds"."environment", "p_ci_builds"."when", "p_ci_builds"."yaml_variables", "p_ci_builds"."queued_at", "p_ci_builds"."lock_version", "p_ci_builds"."coverage_regex", "p_ci_builds"."auto_canceled_by_id", "p_ci_builds"."retried", "p_ci_builds"."protected", "p_ci_builds"."failure_reason", "p_ci_builds"."scheduled_at", "p_ci_builds"."token_encrypted", "p_ci_builds"."upstream_pipeline_id", "p_ci_builds"."resource_group_id", "p_ci_builds"."waiting_for_resource_at", "p_ci_builds"."processed", "p_ci_builds"."scheduling_type", "p_ci_builds"."id", "p_ci_builds"."stage_id", "p_ci_builds"."partition_id", "p_ci_builds"."auto_canceled_by_partition_id"
FROM "p_ci_builds"
WHERE "p_ci_builds"."type" IN ('Ci::Processable', 'Ci::Build')
  AND "p_ci_builds"."partition_id" = 100
  AND ("p_ci_builds"."retried" = FALSE OR "p_ci_builds"."retried" IS NULL)
  AND "p_ci_builds"."when" = 'manual'
  AND "p_ci_builds"."status" IN ('success', 'failed', 'canceled', 'skipped', 'manual')
  AND "p_ci_builds"."commit_id" = 1143802721

Review summary

Application Security review from @truegreg
backend review from @ahegyi
backend maintainer review from @jessieay
database review: from @jarka
database maintainer review (once Jarka has approved)
devopsverify review from @vshushlin
Technical Writing review from @phillipwells

Edited Apr 26, 2024 by Pam Artiaga

Include all finished deployments in Environments stop actions

What does this MR do and why?

MR acceptance checklist

Screenshots or screen recordings

How to set up and validate locally

Query Plan

Query 1-1 - Environment->deployment

Query 1-2 - Environment->deployment->deployable

Query 1-3 - Environment->deployment->deployable->pipeline

Query 1-4 - Environment->deployment->deployable->pipeline->jobs

Query 2-1 - Fetching deployment records given the latest_successful_jobs (before) or latest_finished_jobs (after)

Query 2-2 - preloaded associations

Review summary

Related issue

Merge request reports