Skip to content

BG migration to fix incorrect job artifacts expire_at on self-managed

Albert requested to merge 355833-migration-to-fix-incorrect-expire-at into master

What does this MR do and why?

Fix remaining incorrect expire_at on self-managed instances that was set by !47723 (merged). This gives instances that may not have fully executed the fix in DestroyBatchService to still have a way to remove the incorrect values.

This MR adds a temporary index, which will be removed later on, tracked in #368979 (closed).

Screenshots or screen recordings

These are strongly recommended to assist reviewers and reduce the time to merge your change.

DB Queries

Create index:

CREATE INDEX tmp_index_ci_job_artifacts_on_id_expire_at_file_type_trace ON ci_job_artifacts USING btree (id, expire_at, file_type) WHERE (((date_part('day'::text, timezone('UTC'::text, expire_at)) = ANY (ARRAY[(21)::double precision, (22)::double precision, (23)::double precision])) AND (date_part('minute'::text, timezone('UTC'::text, expire_at)) = ANY (ARRAY[(0)::double precision, (30)::double precision, (45)::double precision])) AND (date_part('second'::text, timezone('UTC'::text, expire_at)) = (0)::double precision)) OR (file_type = 3));

Batching and update:

SELECT
    "ci_job_artifacts"."id"
FROM
    "ci_job_artifacts"
WHERE
    "ci_job_artifacts"."id" BETWEEN 1 AND 100
ORDER BY
    "ci_job_artifacts"."id" ASC
LIMIT 1

--

SELECT
    "ci_job_artifacts"."id"
FROM
    "ci_job_artifacts"
WHERE
    "ci_job_artifacts"."id" BETWEEN 1 AND 100
    AND "ci_job_artifacts"."id" >= 1
ORDER BY
    "ci_job_artifacts"."id" ASC
LIMIT 1 OFFSET 10

--

UPDATE
    "ci_job_artifacts"
SET
    "expire_at" = NULL
WHERE
    "ci_job_artifacts"."id" BETWEEN 1 AND 100
    AND "ci_job_artifacts"."id" >= 1
    AND "ci_job_artifacts"."id" < 11
    AND (EXTRACT(day FROM timezone('UTC', expire_at)) IN (21, 22, 23)
        AND EXTRACT(minute FROM timezone('UTC', expire_at)) IN (0, 30, 45)
        AND EXTRACT(second FROM timezone('UTC', expire_at)) = 0
        OR "ci_job_artifacts"."file_type" = 3)

--

SELECT
    "ci_job_artifacts"."id"
FROM
    "ci_job_artifacts"
WHERE
    "ci_job_artifacts"."id" BETWEEN 1 AND 100
    AND "ci_job_artifacts"."id" >= 11
ORDER BY
    "ci_job_artifacts"."id" ASC
LIMIT 1 OFFSET 10

--

UPDATE
    "ci_job_artifacts"
SET
    "expire_at" = NULL
WHERE
    "ci_job_artifacts"."id" BETWEEN 1 AND 100
    AND "ci_job_artifacts"."id" >= 11
    AND (EXTRACT(day FROM timezone('UTC', expire_at)) IN (21, 22, 23)
        AND EXTRACT(minute FROM timezone('UTC', expire_at)) IN (0, 30, 45)
        AND EXTRACT(second FROM timezone('UTC', expire_at)) = 0
        OR "ci_job_artifacts"."file_type" = 3)

query plans:

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

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

Related to #355833 (closed)

Edited by Adam Hegyi

Merge request reports