Fix unlocking of job artifacts when pipelines succeed, failed, canceled, or blocked

What does this MR do and why?

This MR fixes the process to unlock job artifacts from earlier pipelines after each pipeline transition to the following states:

• success
• failed
• canceled
• manual (blocked)

The unlock process will unlock previous job artifacts, except for the following:

1. The latest pipeline family on the ref. This could be a pipeline in any of the above states. The non-successful pipeline is also kept locked because they could eventually be a successful pipeline. For example, a failed pipeline could pass upon job retry, a blocked pipeline could finally complete and succeeds.
2. The latest successful pipeline on the ref.

Fixes: #387087 (closed), #266958 (closed)

Screenshots or screen recordings

Recording showing the fix:

Screen_Recording_2023-03-17_at_1.56.15_PM

Database queries

These queries are tested on gitlab-org/gitlab master branch (ci_ref_id 5688).

1. Selecting latest successful pipeline in a ref:

SQL query

SELECT "ci_pipelines".* FROM "ci_pipelines"
                        WHERE "ci_pipelines"."ci_ref_id" = 5688
                          AND ("ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11) OR "ci_pipelines"."source" IS NULL)
                          AND ("ci_pipelines"."status" IN ('success')) ORDER BY "ci_pipelines"."id" DESC LIMIT 1

✅ 55.871 ms - postgres.ai

2. Unlocking previous pipelines excluding last successful pipeline:

SQL query

UPDATE
    "ci_pipelines"
SET
    "locked" = 0
WHERE
        "ci_pipelines"."id" IN
        (SELECT
             "ci_pipelines"."id"
         FROM
             "ci_pipelines"
         WHERE
                 "ci_pipelines"."ci_ref_id" = 5688
           AND "ci_pipelines"."locked" = 1
           AND "ci_pipelines"."id" < 812565959
           AND "ci_pipelines"."id" NOT IN
               (WITH RECURSIVE
                    "base_and_descendants"
                        AS
                        ((SELECT
                              "ci_pipelines".*
                          FROM
                              "ci_pipelines"
                          WHERE
                                  "ci_pipelines"."id" = 812565959)
                         UNION
                         (SELECT
                              "ci_pipelines".*
                          FROM
                              "ci_pipelines",
                              "base_and_descendants",
                              "ci_sources_pipelines"
                          WHERE
                                  "ci_sources_pipelines"."pipeline_id" = "ci_pipelines"."id"
                            AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id"
                            AND "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id"))
                SELECT
                    "id"
                FROM
                    "base_and_descendants"
                        AS
                        "ci_pipelines")
           AND "ci_pipelines"."id" NOT IN
               (WITH RECURSIVE
                    "base_and_descendants"
                        AS
                        ((SELECT
                              "ci_pipelines".*
                          FROM
                              "ci_pipelines"
                          WHERE
                                  "ci_pipelines"."id" = 812514428)
                         UNION
                         (SELECT
                              "ci_pipelines".*
                          FROM
                              "ci_pipelines",
                              "base_and_descendants",
                              "ci_sources_pipelines"
                          WHERE
                                  "ci_sources_pipelines"."pipeline_id" = "ci_pipelines"."id"
                            AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id"
                            AND "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id"))
                SELECT
                    "id"
                FROM
                    "base_and_descendants"
                        AS
                        "ci_pipelines")
    LIMIT 100
    FOR UPDATE
            SKIP LOCKED)
            RETURNING ("ci_pipelines"."id")

✅ 247.310 ms postgres.ai

How to set up and validate locally

1. Create a project with CI config that creates a successful pipeline (Pipeline 1) and a job artifact. Example CI config provided below.
2. Check that the job artifact from this pipeline is locked. It appears as "They will not be deleted (even if expired)".
3. Update the CI config to fail the job and wait for the pipeline to fail (Pipeline 2). Example CI config provided below.
4. Check that the job artifact from this pipeline is locked. It appear as "They will not be deleted (even if expired)".
5. Check that the job artifact from the successful pipeline is still locked (Pipeline 1). It appears as "They will not be deleted (even if expired)".
6. Start a new pipeline on the branch (Pipeline 3). This will fail given the existing failing script.
7. Check that the job artifact from this pipeline is locked. It appears as "They will not be deleted (even if expired)".
8. Check that the job artifact from the previous failed pipeline is unlocked (Pipeline 2). It appears as "The artifacts will be removed".
9. Check that the job artifact from the successful pipeline is still locked (Pipeline 1). It appears as "They will not be deleted (even if expired)".
10. Update the CI config to make the job pass and include a manual job and wait for the pipeline to be blocked (Pipeline 4). Example CI config provided below.
11. Check that the job artifact from this pipeline is locked. It appears as "They will not be deleted (even if expired)".
12. Check that the job artifact from the previous failed pipeline is unlocked (Pipeline 3). It appears as "The artifacts will be removed".
13. Check that the job artifact from the successful pipeline is still locked (Pipeline 1). It appears as "They will not be deleted (even if expired)".
14. Update the CI config to remove the manual job and wait for the pipeline (Pipeline 5) to succeed.
15. Check that the job artifact from this pipeline is locked. It appears as "They will not be deleted (even if expired)".
16. Check that the job artifact from all other pipelines are unlocked. It appears as "They will not be deleted (even if expired)".

# successful CI config
test:
  stage: test
  script: echo 'hello'
  artifacts:
    paths: ['.gitlab-ci.yml']
    when: always

# failed CI config
test:
  stage: test
  script: echo 'hello' && exit 1
  artifacts:
    paths: ['.gitlab-ci.yml']
    when: always

# blocked CI config
test:
  stage: test
  script: echo 'hello'
  artifacts:
    paths: ['.gitlab-ci.yml']
    when: always
manual-job:
  stage: test
  script: echo 'manual'
  rules:
  - when: manual

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

Related to #387087 (closed)