BG migration to fix incorrect job artifacts expire_at on self-managed
Compare changes
Files
2- Albert authored
@@ -13,14 +13,14 @@ class AddTmpIndexJobArtifactsIdAndExpireAt < Gitlab::Database::Migration[2.0]
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).
These are strongly recommended to assist reviewers and reduce the time to merge your change.
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:
Numbered steps to set up and validate the change are strongly suggested.
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)