BG migration to fix incorrect job artifacts expire_at on self-managed
-
Review changes -
-
Download -
Patches
-
Plain diff
6 unresolved threads
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:
- batching: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/11075/commands/40091
- update: https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/11075/commands/40095
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.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #355833 (closed)
Edited by Adam Hegyi
Merge request reports
Compare and
Show latest version
- version 28e2b5900a
- version 27efa45fc4
- version 26b047d31c
- version 25ab43ee03
- version 24834359dd
- version 23b2c71cbb
- version 22ba7dfc39
- version 211083e13d
- version 20d97559c0
- version 19b95115b2
- version 1817162c66
- version 171415292c
- version 166baa402f
- version 15107d84e2
- version 149fb06dee
- version 134eff1088
- version 12cd67385b
- version 115f7be734
- version 10a93a9b17
- version 91863cc25
- version 8a88efbf4
- version 73c491e69
- version 633753ab6
- version 5b13738c5
- version 4d2d882a4
- version 33bdc36ca
- version 2b1c7b6c6
- version 17483f353
- master (base)
- latest versionee16a84b8 commits,
- version 28e2b5900a7 commits,
- version 27efa45fc47 commits,
- version 26b047d31c6 commits,
- version 25ab43ee035 commits,
- version 24834359dd6 commits,
- version 23b2c71cbb4 commits,
- version 22ba7dfc394 commits,
- version 211083e13d4 commits,
- version 20d97559c04 commits,
- version 19b95115b24 commits,
- version 1817162c664 commits,
- version 171415292c3 commits,
- version 166baa402f2 commits,
- version 15107d84e22 commits,
- version 149fb06dee7 commits,
- version 134eff10887 commits,
- version 12cd67385b5 commits,
- version 115f7be7344 commits,
- version 10a93a9b174 commits,
- version 91863cc252 commits,
- version 8a88efbf42 commits,
- version 73c491e692 commits,
- version 633753ab62 commits,
- version 5b13738c52 commits,
- version 4d2d882a41 commit,
- version 33bdc36ca1 commit,
- version 2b1c7b6c61 commit,
- version 17483f3531 commit,
Compare changes
- Side-by-side
- Inline
Files
1414Loading