Admin runner filtering by version

What does this MR do and why?

This MR lets admins search by runner version on the admin runners page. When searching by a specific version, all runners that do not start with the searched runner version will be filtered out of the runner list. For example, searching for 14. could result 14.11.1 or 14.2.3 but not 143.2.1. If searching for 14 in the previous example, we would also get 143.2.1.

This MR does not implement search suggestions and instead defaults to two set suggestions of 15.11.0 and 15.12.0. Search suggestions would be good to add onto this MR but it might make it too big.

Part of #422046 (closed)

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

How to set up and validate locally

Make sure you have at least one GitLab runner registered and running.

1. Go to http://gdk.test:3000/-/graphql-explorer

2. Use the following query:

   {
     runners(versionPrefix: "16") {
       nodes {
         id
         managers {
           nodes {
             systemId
             version
           }
         }
       }
     }
   }

3. The query will return any runners that have runner managers of version 16 attached.

Database query plans

Setup:

exec CREATE INDEX index_ci_runner_machines_on_major_version_trigram ON ci_runner_machines USING btree ("substring"(version, '\d+\.'::text), version, runner_id);

exec CREATE INDEX index_ci_runner_machines_on_minor_version_trigram ON ci_runner_machines USING btree ("substring"(version, '\d+\.\d+\.'::text), version, runner_id);

exec CREATE INDEX index_ci_runner_machines_on_patch_version_trigram ON ci_runner_machines USING btree ("substring"(version, '\d+\.\d+\.\d+'::text), version, runner_id);

Simple version query

Retrieve uncapped project runner count

This branch, with version filter

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/23071/commands/74264

SELECT COUNT(*)
FROM "ci_runners"
  INNER JOIN "ci_runner_machines" ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
  INNER JOIN "ci_runner_versions" runner_version ON runner_version.version = "ci_runner_machines".version
WHERE "ci_runners"."runner_type" = 3
  AND substring("ci_runner_machines"."version", '\d+\.\d+\.'::text) = '15.11.'

 Aggregate  (cost=2627.10..2627.11 rows=1 width=8) (actual time=50.992..50.996 rows=1 loops=1)
   Buffers: shared hit=43916
   I/O Timings: read=0.000 write=0.000
   ->  Merge Join  (cost=12.59..2624.87 rows=891 width=0) (actual time=0.622..50.140 rows=6176 loops=1)
         Merge Cond: (ci_runner_machines.version = runner_version.version)
         Buffers: shared hit=43916
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=0.85..2927.17 rows=1056 width=7) (actual time=0.098..48.141 rows=6176 loops=1)
               Buffers: shared hit=43861
               I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using index_ci_runner_machines_on_minor_version_trigram on public.ci_runner_machines  (cost=0.42..105.37 rows=1454 width=15) (actual time=0.015..5.987 rows=10819 loops=1)
                     Index Cond: (("substring"(ci_runner_machines.version, '\d+\.\d+\.'::text)) = '15.11.'::text)
                     Heap Fetches: 1815
                     Buffers: shared hit=5691
                     I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners  (cost=0.43..1.94 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=10819)
                     Index Cond: ((ci_runners.runner_type = 3) AND (ci_runners.id = ci_runner_machines.runner_id))
                     Heap Fetches: 2192
                     Buffers: shared hit=38170
                     I/O Timings: read=0.000 write=0.000
         ->  Index Only Scan using ci_runner_versions_pkey on public.ci_runner_versions runner_version  (cost=0.28..136.38 rows=2970 width=21) (actual time=0.016..0.278 rows=1213 loops=1)
               Heap Fetches: 62
               Buffers: shared hit=55
               I/O Timings: read=0.000 write=0.000

Retrieve page of 21 runners

This branch, with version filter

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/23071/commands/74265

SELECT "ci_runners".*
FROM "ci_runners"
  INNER JOIN "ci_runner_machines" ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
WHERE substring("ci_runner_machines"."version", '\d+\.\d+\.'::text) = '15.11.'
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 21

 Limit  (cost=5130.23..5130.29 rows=21 width=273) (actual time=30312.677..30312.684 rows=21 loops=1)
   Buffers: shared hit=35675 read=13301 dirtied=2479
   I/O Timings: read=29349.677 write=0.000
   ->  Sort  (cost=5130.23..5133.87 rows=1454 width=273) (actual time=30312.674..30312.678 rows=21 loops=1)
         Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
         Sort Method: top-N heapsort  Memory: 45kB
         Buffers: shared hit=35675 read=13301 dirtied=2479
         I/O Timings: read=29349.677 write=0.000
         ->  Nested Loop  (cost=0.85..5091.03 rows=1454 width=273) (actual time=3.377..30209.857 rows=10819 loops=1)
               Buffers: shared hit=35669 read=13301 dirtied=2479
               I/O Timings: read=29349.677 write=0.000
               ->  Index Only Scan using index_ci_runner_machines_on_minor_version_trigram on public.ci_runner_machines  (cost=0.42..105.37 rows=1454 width=8) (actual time=0.026..79.577 rows=10819 loops=1)
                     Index Cond: (("substring"(ci_runner_machines.version, '\d+\.\d+\.'::text)) = '15.11.'::text)
                     Heap Fetches: 1815
                     Buffers: shared hit=5691
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_runners_pkey on public.ci_runners  (cost=0.43..3.43 rows=1 width=273) (actual time=2.777..2.777 rows=1 loops=10819)
                     Index Cond: (ci_runners.id = ci_runner_machines.runner_id)
                     Buffers: shared hit=29978 read=13301 dirtied=2479
                     I/O Timings: read=29349.677 write=0.000

Complex query with various filters

Retrieve uncapped project runner count

master branch without version filter

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/21364/commands/69698

SELECT COUNT(*)
FROM "ci_runners"
  INNER JOIN "ci_runner_machines" ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
  INNER JOIN "ci_runner_versions" runner_version ON runner_version.version = "ci_runner_machines".version
WHERE "ci_runners"."active" = TRUE
  AND "ci_runners"."created_at" <= '2023-05-15 09:32:40.914414'
  AND ("ci_runners"."contacted_at" IS NULL
    OR "ci_runners"."contacted_at" <= '2023-05-15 09:32:40.914414')
  AND "runner_version"."status" = 3
  AND "ci_runners"."runner_type" = 3

 Aggregate  (cost=291021.20..291021.21 rows=1 width=8) (actual time=2060.723..2114.579 rows=1 loops=1)
   Buffers: shared hit=923946 read=104418 dirtied=64463
   I/O Timings: read=3774.733 write=0.000
   ->  Gather  (cost=291020.98..291021.19 rows=2 width=8) (actual time=2059.662..2114.566 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=923946 read=104418 dirtied=64463
         I/O Timings: read=3774.733 write=0.000
         ->  Aggregate  (cost=290020.98..290020.99 rows=1 width=8) (actual time=2053.416..2053.420 rows=1 loops=3)
               Buffers: shared hit=923946 read=104418 dirtied=64463
               I/O Timings: read=3774.733 write=0.000
               ->  Nested Loop  (cost=13.27..289855.82 rows=66065 width=0) (actual time=2053.409..2053.413 rows=0 loops=3)
                     Buffers: shared hit=923946 read=104418 dirtied=64463
                     I/O Timings: read=3774.733 write=0.000
                     ->  Merge Join  (cost=12.84..102117.88 rows=162985 width=8) (actual time=0.372..623.712 rows=77089 loops=3)
                           Merge Cond: (ci_runner_machines.version = runner_version.version)
                           Buffers: shared hit=157460 read=32387 dirtied=23430
                           I/O Timings: read=1104.953 write=0.000
                           ->  Parallel Index Scan using index_ci_runner_machines_on_version on public.ci_runner_machines  (cost=0.42..104529.27 rows=173062 width=15) (actual time=0.216..599.467 rows=79760 loops=3)
                                 Buffers: shared hit=157261 read=32329 dirtied=23423
                                 I/O Timings: read=1102.269 write=0.000
                           ->  Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version  (cost=0.28..140.87 rows=2453 width=21) (actual time=0.104..2.229 rows=1584 loops=3)
                                 Index Cond: (runner_version.status = 3)
                                 Heap Fetches: 623
                                 Buffers: shared hit=199 read=58 dirtied=7
                                 I/O Timings: read=2.683 write=0.000
                     ->  Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners  (cost=0.43..1.15 rows=1 width=4) (actual time=0.018..0.018 rows=0 loops=231267)
                           Index Cond: ((ci_runners.runner_type = 3) AND (ci_runners.id = ci_runner_machines.runner_id))
                           Filter: (ci_runners.active AND (ci_runners.created_at <= '2023-05-15 09:32:40.914414'::timestamp without time zone) AND ((ci_runners.contacted_at IS NULL) OR (ci_runners.contacted_at <= '2023-05-15 09:32:40.914414'::timestamp without time zone)))
                           Rows Removed by Filter: 1
                           Buffers: shared hit=766486 read=72031 dirtied=41033
                           I/O Timings: read=2669.781 write=0.000

This branch, with version filter

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/23071/commands/74266

SELECT COUNT(*)
FROM "ci_runners"
  INNER JOIN "ci_runner_machines" ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
  INNER JOIN "ci_runner_versions" runner_version ON runner_version.version = "ci_runner_machines".version
WHERE "ci_runners"."active" = TRUE
  AND "ci_runners"."created_at" <= '2023-05-15 09:40:23.437431'
  AND ("ci_runners"."contacted_at" IS NULL
    OR "ci_runners"."contacted_at" <= '2023-05-15 09:40:23.437431')
  AND "runner_version"."status" = 3
  AND "ci_runners"."runner_type" = 3
  AND substring("ci_runner_machines"."version", '\d+\.\d+\.\d+'::text) = '15.11.1'

 Aggregate  (cost=4312.40..4312.41 rows=1 width=8) (actual time=26.545..26.548 rows=1 loops=1)
   Buffers: shared hit=1664 read=15 dirtied=3
   I/O Timings: read=21.835 write=0.000
   ->  Nested Loop  (cost=8.88..4311.29 rows=445 width=0) (actual time=26.539..26.541 rows=0 loops=1)
         Buffers: shared hit=1664 read=15 dirtied=3
         I/O Timings: read=21.835 write=0.000
         ->  Merge Join  (cost=8.45..201.48 rows=1201 width=8) (actual time=22.591..23.333 rows=432 loops=1)
               Merge Cond: (ci_runner_machines.version = runner_version.version)
               Buffers: shared hit=229 read=15
               I/O Timings: read=21.835 write=0.000
               ->  Index Only Scan using index_ci_runner_machines_on_patch_version_trigram on public.ci_runner_machines  (cost=0.42..105.37 rows=1454 width=15) (actual time=9.362..10.008 rows=432 loops=1)
                     Index Cond: (("substring"(ci_runner_machines.version, '\d+\.\d+\.\d+'::text)) = '15.11.1'::text)
                     Heap Fetches: 90
                     Buffers: shared hit=202 read=5
                     I/O Timings: read=9.403 write=0.000
               ->  Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version  (cost=0.28..90.22 rows=2910 width=21) (actual time=3.882..12.932 rows=1208 loops=1)
                     Index Cond: (runner_version.status = 3)
                     Heap Fetches: 63
                     Buffers: shared hit=27 read=10
                     I/O Timings: read=12.432 write=0.000
         ->  Index Scan using index_ci_runners_on_runner_type_and_id on public.ci_runners  (cost=0.43..3.42 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=432)
               Index Cond: ((ci_runners.runner_type = 3) AND (ci_runners.id = ci_runner_machines.runner_id))
               Filter: (ci_runners.active AND (ci_runners.created_at <= '2023-05-15 09:40:23.437431'::timestamp without time zone) AND ((ci_runners.contacted_at IS NULL) OR (ci_runners.contacted_at <= '2023-05-15 09:40:23.437431'::timestamp without time zone)))
               Rows Removed by Filter: 0
               Buffers: shared hit=1435 dirtied=3
               I/O Timings: read=0.000 write=0.000

Retrieve page of 21 runners

master branch without version filter

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/21364/commands/69705

SELECT "ci_runners".*
FROM "ci_runners"
  INNER JOIN "ci_runner_machines" ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
  INNER JOIN "ci_runner_versions" runner_version ON runner_version.version = "ci_runner_machines".version
WHERE "ci_runners"."active" = TRUE
  AND "ci_runners"."created_at" <= '2023-05-15 10:02:55.055500'
  AND ("ci_runners"."contacted_at" IS NULL
    OR "ci_runners"."contacted_at" <= '2023-05-15 10:02:55.055500')
  AND "runner_version"."status" = 3
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 21

 Limit  (cost=1.13..111.91 rows=21 width=276) (actual time=25806.407..25806.418 rows=0 loops=1)
   Buffers: shared hit=6084364 read=208308 dirtied=55673 written=20494
   I/O Timings: read=18392.120 write=609.862
   ->  Nested Loop  (cost=1.13..1146393.47 rows=217328 width=276) (actual time=25806.404..25806.415 rows=0 loops=1)
         Buffers: shared hit=6084364 read=208308 dirtied=55673 written=20494
         I/O Timings: read=18392.120 write=609.862
         ->  Nested Loop  (cost=0.85..1077114.10 rows=230765 width=283) (actual time=25806.394..25806.395 rows=0 loops=1)
               Buffers: shared hit=6084364 read=208308 dirtied=55673 written=20494
               I/O Timings: read=18392.120 write=609.862
               ->  Index Scan using index_ci_runners_on_created_at_desc_and_id_desc on public.ci_runners  (cost=0.43..308312.35 rows=1440305 width=276) (actual time=21.397..22191.689 rows=1686430 loops=1)
                     Index Cond: (ci_runners.created_at <= '2023-05-15 10:02:55.0555'::timestamp without time zone)
                     Filter: (ci_runners.active AND ((ci_runners.contacted_at IS NULL) OR (ci_runners.contacted_at <= '2023-05-15 10:02:55.0555'::timestamp without time zone)))
                     Rows Removed by Filter: 240876
                     Buffers: shared hit=1045167 read=188210 dirtied=55673 written=19025
                     I/O Timings: read=17658.094 write=564.974
               ->  Index Scan using index_ci_runner_machines_on_runner_id_and_system_xid on public.ci_runner_machines  (cost=0.42..0.52 rows=1 width=15) (actual time=0.002..0.002 rows=0 loops=1686430)
                     Index Cond: (ci_runner_machines.runner_id = ci_runners.id)
                     Buffers: shared hit=5039197 read=20098 written=1469
                     I/O Timings: read=734.026 write=44.888
         ->  Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version  (cost=0.28..0.30 rows=1 width=21) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: ((runner_version.status = 3) AND (runner_version.version = ci_runner_machines.version))
               Heap Fetches: 0
               I/O Timings: read=0.000 write=0.000

This branch, with version filter

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/23071/commands/74269

SELECT "ci_runners".*
FROM "ci_runners"
  INNER JOIN "ci_runner_machines" ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
  INNER JOIN "ci_runner_versions" runner_version ON runner_version.version = "ci_runner_machines".version
WHERE "ci_runners"."active" = TRUE
  AND "ci_runners"."created_at" <= '2023-05-15 10:06:01.772944'
  AND ("ci_runners"."contacted_at" IS NULL
    OR "ci_runners"."contacted_at" <= '2023-05-15 10:06:01.772944')
  AND "runner_version"."status" = 3
  AND substring("ci_runner_machines"."version", '\d+\.\d+\.'::text) = '15.11.'
ORDER BY "ci_runners"."created_at" DESC, "ci_runners"."id" DESC
LIMIT 21

 Limit  (cost=4341.44..4341.50 rows=21 width=273) (actual time=10637.304..10637.308 rows=0 loops=1)
   Buffers: shared hit=42849 read=6218 dirtied=767
   I/O Timings: read=10304.432 write=0.000
   ->  Sort  (cost=4341.44..4342.98 rows=613 width=273) (actual time=10637.301..10637.304 rows=0 loops=1)
         Sort Key: ci_runners.created_at DESC, ci_runners.id DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=42849 read=6218 dirtied=767
         I/O Timings: read=10304.432 write=0.000
         ->  Nested Loop  (cost=8.88..4324.92 rows=613 width=273) (actual time=10637.251..10637.254 rows=0 loops=1)
               Buffers: shared hit=42843 read=6218 dirtied=767
               I/O Timings: read=10304.432 write=0.000
               ->  Merge Join  (cost=8.45..201.48 rows=1201 width=8) (actual time=0.523..58.476 rows=10819 loops=1)
                     Merge Cond: (ci_runner_machines.version = runner_version.version)
                     Buffers: shared hit=5724
                     I/O Timings: read=0.000 write=0.000
                     ->  Index Only Scan using index_ci_runner_machines_on_minor_version_trigram on public.ci_runner_machines  (cost=0.42..105.37 rows=1454 width=15) (actual time=0.018..43.688 rows=10819 loops=1)
                           Index Cond: (("substring"(ci_runner_machines.version, '\d+\.\d+\.'::text)) = '15.11.'::text)
                           Heap Fetches: 1815
                           Buffers: shared hit=5691
                           I/O Timings: read=0.000 write=0.000
                     ->  Index Only Scan using index_ci_runner_versions_on_unique_status_and_version on public.ci_runner_versions runner_version  (cost=0.28..90.22 rows=2910 width=21) (actual time=0.053..0.309 rows=1208 loops=1)
                           Index Cond: (runner_version.status = 3)
                           Heap Fetches: 60
                           Buffers: shared hit=33
                           I/O Timings: read=0.000 write=0.000
               ->  Index Scan using index_ci_runners_on_active on public.ci_runners  (cost=0.43..3.43 rows=1 width=273) (actual time=0.976..0.976 rows=0 loops=10819)
                     Index Cond: ((ci_runners.active = true) AND (ci_runners.id = ci_runner_machines.runner_id))
                     Filter: ((ci_runners.created_at <= '2023-05-15 10:06:01.772944'::timestamp without time zone) AND ((ci_runners.contacted_at IS NULL) OR (ci_runners.contacted_at <= '2023-05-15 10:06:01.772944'::timestamp without time zone)))
                     Rows Removed by Filter: 1
                     Buffers: shared hit=37119 read=6218 dirtied=767
                     I/O Timings: read=10304.432 write=0.000

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.

Hey @markus.ferrell!

Thank you for your contribution to GitLab. Please refer to the contribution flow documentation for a quick overview of the process, and the merge request (MR) guidelines for the detailed process.

Did you know about our community forks? Working from there will make your contribution process easier. Please check it out!

When you're ready for a first review, post @gitlab-bot ready. If you know a relevant reviewer(s) (for example, someone that was involved in a related issue), you can also assign them directly with @gitlab-bot ready @user1 @user2.

At any time, if you need help, feel free to post @gitlab-bot help or initiate a mentor session on Discord. Read more on how to get help.

You can comment @gitlab-bot label <label1> <label2> to add labels to your MR. Please see the list of allowed labels in the label command documentation.

This message was generated automatically. You're welcome to improve it.

added Community contribution workflowin dev labels

assigned to @markus.ferrell

This merge request will be counted as part of the running Hackathon!

Check out the Hackathon page for more information!

This message was generated automatically. You're welcome to improve it.

added Hackathon label

mentioned in issue gitlab-org/quality/triage-reports#12175 (closed)

changed milestone to %16.0

added Category:Runner GraphQL backend frontend labels

added featureaddition label

added typefeature label

removed typefeature label

removed featureaddition label

added featureaddition label

added typefeature label

mentioned in issue gitlab-org/quality/triage-reports#12219 (closed)

mentioned in issue gitlab-org/quality/triage-reports#12276 (closed)

added grouppipeline execution label

added devopsverify sectionops labels

added 2 commits

• 56f6d5c9 - Sort admin runners page by runner version
• f302e807 - Robocop touch ups

Compare with previous version

added 1 commit

• 920a5d3e - Sort admin runners page by runner version

Compare with previous version

added 2813 commits

• 920a5d3e...f9eff1b5 - 2810 commits from branch gitlab-org:master
• 1b179cd7 - Sort admin runners page by runner version
• c8fc3380 - WIP changes
• 6af471bc - yarn fixes

Compare with previous version

changed the description

changed the description

marked this merge request as ready

@gitlab-bot ready

added workflowready for review label and removed workflowin dev label

Hi @ashrafkhamis! Please review this documentation merge request. This message was generated automatically. You're welcome to improve it.

added documentation twtriaged labels

requested review from @ashrafkhamis

@ashrafkhamis, this Community contribution is ready for review.

• Do you have capacity and domain expertise to review this? We are mindful of your time, so if you are not able to take this on, please re-assign to one or more other reviewers.
• Add the workflowin dev label if the merge request needs action from the author.

This message was generated automatically. You're welcome to improve it.

added 1 commit

• 1563022a - Backend specs

Compare with previous version

added Technical Writing docsfeature labels

added workflowin review label and removed workflowready for review label

removed review request for @ashrafkhamis

added workflowin dev label and removed workflowin review label

added 2 commits

• 749609e4 - WIP frontend specsc
• c358f7bc - Cleanup docs strings

Compare with previous version

@gitlab-bot ready @ashrafkhamis

added workflowready for review label and removed workflowin dev label

@vyaklushin, this Community contribution is ready for review.

• Do you have capacity and domain expertise to review this? We are mindful of your time, so if you are not able to take this on, please re-assign to one or more other reviewers.
• Add the workflowin dev label if the merge request needs action from the author.

This message was generated automatically. You're welcome to improve it.

requested review from @vyaklushin

requested review from @ashrafkhamis

added workflowin review label and removed workflowready for review label

approved this merge request

@ashrafkhamis, thanks for approving this merge request.

This is the first time the merge request is approved. To ensure full test coverage, please start a new pipeline before merging.

For more info, please refer to the following links:

• rspec predictive jobs
• jest predictive jobs
• merging a merge request.

added pipeline:mr-approved label

removed review request for @ashrafkhamis

requested review from @pedropombeiro

Hi @djadmin! Can you please review frontend changes?

requested review from @djadmin

added UX label

Thanks for helping us improve the UX of GitLab. Your contribution is appreciated! We have pinged our UX team, so stay tuned for their feedback.

This message was generated automatically. You're welcome to improve it.

requested review from @gdoyle

changed title from Admin runner sorting by version to Admin runner filtering by version

@markus.ferrell I updated the MR title to filtering instead of sorting.

changed the description

added database label

requested review from @joseph

added 3 commits

• 029ab2f9 - Review changes
• 6cf29bc6 - Sql narrowing
• 86c68471 - Remove unused import

Compare with previous version

changed the description

added 1 commit

• 81437202 - Review changes

Compare with previous version

removed review request for @djadmin

@DarrenEastman Pinging you because this feature overlaps a bit with our runner_upgrade_management feature: Users that are familiar with the GitLab versions may find this filtering does something similar to the "Upgrade Status" filter already, so they may find it a duplicate.

cc @gdoyle @nicolewilliams

added GitLab Free label

added 3 commits

• f5ebd9d6 - Frontend reveiw changes
• 492efdce - Update scope test to reflect ILIKE call
• 6717704c - Update graphql docs

Compare with previous version

added 1 commit

• ecb247d2 - Update frontend label

Compare with previous version

Value menu

When the user selects version as the key, we should not show the menu at all, since we are not allowing them to choose from any value options. This is similar to how we can search by tag in groups (see screenshot below). @markus.ferrell We should remove the menu from appearing here. cc: @mrincon in case you need to add additional details

Tags filter in groups:

changed the description

I think we'll need to do something regarding the database performance. If you search for a non-existing version, the database query will time out because Postgres is sequentially scanning the table by created_at and id and then dropping rows that don't match the version (which will be all rows).

Time: 2.537 min  
  - planning: 0.870 ms  
  - execution: 2.537 min  
    - I/O read: 4.655 min  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 1184127 (~9.00 GiB) from the buffer pool  
  - reads: 258759 (~2.00 GiB) from the OS file cache, including disk I/O  
  - dirtied: 42385 (~331.10 MiB)  
  - writes: 0  

 Limit  (cost=1000.58..447112.35 rows=1 width=282) (actual time=152080.476..152219.986 rows=0 loops=1)
   Buffers: shared hit=1184127 read=258759 dirtied=42385
   I/O Timings: read=279281.711 write=0.000
   ->  Gather Merge  (cost=1000.58..447112.35 rows=1 width=282) (actual time=152080.471..152219.979 rows=0 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=1184127 read=258759 dirtied=42385
         I/O Timings: read=279281.711 write=0.000
         ->  Parallel Index Scan using index_ci_runners_on_created_at_desc_and_id_desc on public.ci_runners  (cost=0.56..446112.21 rows=1 width=282) (actual time=152050.486..152050.487 rows=0 loops=3)
               Filter: ((ci_runners.version)::text ~~* '156.11.%'::text)
               Rows Removed by Filter: 670822
               Buffers: shared hit=1184127 read=258759 dirtied=42385
               I/O Timings: read=279281.711 write=0.000

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/18503/commands/61386

approved this merge request

removed review request for @gdoyle

mentioned in merge request !120889 (closed)

@gdoyle @mrincon so this MR introduces the ability to search runners by their version. However, given the recent introduction of the concept of runner managers (who are the ones who do have a version), I'm wondering if this implementation will match what users are expecting. A runner's version will contain the version of the latest runner that pinged the GitLab instance for a job. If you have multiple runner managers under a runner, each runner manager with a different version, your search might come out empty when in fact there is a runner manager running the version that you're searching for.

So it sounds like what we really want is to instead perform this search on the ci_runner_machines table, and then return all the unique runners that own those runner managers/runner machines. WDYT?

added blocked label

removed milestone %16.0

removed review request for @vyaklushin, @pedropombeiro, and @joseph

Hi @vyaklushin

We noticed this MR is marked as workflowready for review but no reviewer is assigned. workflowin dev has automatically been applied to this MR based on the likelihood the review is finished. If additional reviews are still required, please assign a reviewer and reapply workflowready for review.

@markus.ferrell you may also request a review by commenting @gitlab-bot ready. You can also assign reviewers directly using @gitlab-bot ready @user1 @user2 if you know the relevant reviewer(s), such as those who were involved in a related issue.

This message was generated automatically. You're welcome to improve it.

added workflowin dev label and removed workflowin review label

@markus.ferrell, it seems we're waiting on an action from you for approximately two weeks.

• Do you still have capacity to work on this? If not, you might want to close this MR and/or ask someone to take over.
• Do you need help in getting it ready? At any time, you can:
  • ask for help with @gitlab-bot help.
  • initiate a mentor session on Discord.
• If you're actually ready for a review, you can post @gitlab-bot ready.

This message was generated automatically. You're welcome to improve it.

added automation:author-reminded label

added idle label

removed idle label

mentioned in issue gitlab-org/quality/triage-reports#13157 (closed)

mentioned in merge request !123131 (merged)

mentioned in issue gitlab-org/quality/triage-reports#13251 (closed)

mentioned in issue gitlab-org/quality/triage-reports#13321 (closed)

added 12316 commits

• ecb247d2...a18e1370 - 12302 commits from branch gitlab-org:master
• a18e1370...626a9f0c - 4 earlier commits
• cebd70d2 - WIP frontend specsc
• baf6b1b3 - Cleanup docs strings
• 9833752d - Review changes
• 9bdf531a - Frontend reveiw changes
• cab95cc9 - Update scope test to reflect ILIKE call
• 49361aee - Update graphql docs
• 0d11b2c2 - Update frontend label
• fb31438e - New search backend
• d8263d57 - Touch up
• f1d86275 - Push checks

Compare with previous version

added sectionci label and removed sectionops label

@gitlab-bot ready @pedropombeiro

added workflowready for review label and removed workflowin dev label

requested review from @pedropombeiro

@pedropombeiro, this Community contribution is ready for review.

• Do you have capacity and domain expertise to review this? We are mindful of your time, so if you are not able to take this on, please re-assign to one or more other reviewers.
• Add the workflowin dev label if the merge request needs action from the author.

This message was generated automatically. You're welcome to improve it.

removed automation:author-reminded label

mentioned in issue gitlab-org/quality/triage-reports#13416 (closed)

mentioned in issue gitlab-org/quality/triage-reports#13480 (closed)

changed the description

mentioned in issue gitlab-org/quality/triage-reports#13598 (closed)

mentioned in issue gitlab-runner#29289 (closed)

added 3237 commits

• f1d86275...576ffe7f - 3222 commits from branch gitlab-org:master
• 576ffe7f...2066bb8d - 5 earlier commits
• 887e75b1 - Cleanup docs strings
• a0a0d501 - Review changes
• 6af963c5 - Frontend reveiw changes
• b315b0b2 - Update scope test to reflect ILIKE call
• 2247f79d - Update graphql docs
• b312b70a - Update frontend label
• 4cafbeab - New search backend
• 8e2ccd5f - Touch up
• a6b831ec - Push checks
• 4d15463a - Cleanup tests

Compare with previous version

added 170 commits

• 4d15463a...ef56fc21 - 155 commits from branch gitlab-org:master
• ef56fc21...33623889 - 5 earlier commits
• f428cebb - Cleanup docs strings
• e2ef9157 - Review changes
• b9842cc6 - Frontend reveiw changes
• e1e8f48b - Update scope test to reflect ILIKE call
• 6ef26597 - Update graphql docs
• 1c3b409e - Update frontend label
• 17ac232c - New search backend
• d3712e4d - Touch up
• da416475 - Push checks
• 29176e40 - Cleanup tests

Compare with previous version

mentioned in merge request !128809 (merged)

mentioned in issue gitlab-org/quality/triage-reports#13694 (closed)

removed blocked label

added databasereview pending label

	1 Message
	This merge request adds or changes documentation files. A review from the Technical Writing team before you merge is recommended. Reviews can happen after you merge.

Documentation review

The following files require a review from a technical writer:

• doc/api/graphql/reference/index.md (Link to current live version)

The review does not need to block merging this merge request. See the:

• Metadata for the *.md files that you've changed. The first few lines of each *.md file identify the stage and group most closely associated with your docs change.
• The Technical Writer assigned for that stage and group.
• Documentation workflows for information on when to assign a merge request for review.

Reviewer roulette

Changes that require review have been detected!

Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:

Category	Reviewer	Maintainer
backend	@aakriti.gupta (UTC+2)	@engwan (UTC+8)
database	@jarka (UTC+2)	@ahegyi (UTC+2)
UX	@pedroms (UTC+2)	Maintainer review is optional for UX
~"Verify"	Reviewer review is optional for ~"Verify"	@vshushlin (UTC+2)

Please check reviewer's status!

• Reviewer is available!
• Reviewer is unavailable!

Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.

To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.

Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.

If needed, you can retry the danger-review job that generated this comment.

Generated by Danger

Bundle size analysis [beta]

This compares changes in bundle size for entry points between the commits 01feeb59 and 64ff860b

Special assets

Entrypoint / Name	Size before	Size after	Diff	Diff in percent
average	4.17 MB	4.17 MB	-	0.0 %
mainChunk	3.02 MB	3.02 MB	-	0.0 %

---

Note: We do not have exact data for 01feeb59. So we have used data from: 623c4fb7.
The intended commit has no webpack pipeline, so we chose the last commit with one before it.

Please look at the full report for more details

---

Read more about how this report works.

Generated by Danger

@markus.ferrell Some end-to-end (E2E) tests should run based on the stage label.

Please start the trigger-omnibus-and-follow-up-e2e job in the qa stage and ensure tests in the follow-up-e2e:package-and-test-ee pipeline pass before this MR is merged. (E2E tests are computationally intensive and don't run automatically for every push/rebase, so we ask you to run this job manually at least once.)

To run all E2E tests, apply the pipeline:run-all-e2e label and run a new pipeline.

E2E test jobs are allowed to fail due to flakiness. See current failures at the latest pipeline triage issue.

Once done, apply the emoji on this comment.

For any questions or help, reach out on the internal #quality Slack channel.

Allure report

allure-report-publisher generated test report!

e2e-test-on-gdk: test report for 6e31228e

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Plan        | 51     | 0      | 0       | 0     | 51    | ✅     |
| Manage      | 0      | 0      | 1       | 0     | 1     | ➖     |
| Create      | 32     | 0      | 5       | 0     | 37    | ✅     |
| Data Stores | 20     | 0      | 0       | 0     | 20    | ✅     |
| Govern      | 45     | 0      | 0       | 0     | 45    | ✅     |
| Verify      | 20     | 0      | 0       | 0     | 20    | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 168    | 0      | 6       | 0     | 174   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

e2e-review-qa: test report for 64ff860b

expand test summary

+-----------------------------------------------------------------------+
|                            suites summary                             |
+------------------+--------+--------+---------+-------+-------+--------+
|                  | passed | failed | skipped | flaky | total | result |
+------------------+--------+--------+---------+-------+-------+--------+
| Create           | 8      | 0      | 1       | 0     | 9     | ✅     |
| Manage           | 1      | 0      | 0       | 0     | 1     | ✅     |
| Verify           | 8      | 0      | 0       | 0     | 8     | ✅     |
| Plan             | 3      | 0      | 1       | 0     | 4     | ✅     |
| Package          | 0      | 0      | 1       | 0     | 1     | ➖     |
| Data Stores      | 2      | 0      | 0       | 0     | 2     | ✅     |
| Framework sanity | 0      | 0      | 1       | 0     | 1     | ➖     |
| Govern           | 2      | 0      | 0       | 0     | 2     | ✅     |
| Monitor          | 4      | 0      | 0       | 1     | 4     | ❗     |
+------------------+--------+--------+---------+-------+-------+--------+
| Total            | 28     | 0      | 4       | 1     | 32    | ❗     |
+------------------+--------+--------+---------+-------+-------+--------+

added 504 commits

• 29176e40...6200fee2 - 502 commits from branch gitlab-org:master
• af2aa156 - Sort admin runners page by runner version
• da2168b3 - Review changes

Compare with previous version

removed workflowready for review label

added blocked label

mentioned in merge request !129264 (closed)

@markus.ferrell I've found another (kind of unrelated) issue that I'll need to fix first, in order for the database review to be simpler. The problem is that since Ci::Runner.with_version is being introduced, joining ci_runners with ci_runner_managers, this will make the complex query scenario in the MR description very inefficient, given that it will query on ci_runner_managers for version values, but also on ci_runners for other values such as contacted_at. So we need to move online/offline, and stale scopes to operate similarly on ci_runner_machines values. Something like this:

diff --git a/app/models/ci/runner.rb b/app/models/ci/runner.rb
index 0413bb480d40..9ddc6d7185b4 100644
--- a/app/models/ci/runner.rb
+++ b/app/models/ci/runner.rb
@@ -87,7 +87,14 @@ class Runner < Ci::ApplicationRecord
 
     scope :active, -> (value = true) { where(active: value) }
     scope :paused, -> { active(false) }
-    scope :online, -> { where(arel_table[:contacted_at].gt(online_contact_time_deadline)) }
+    scope :online, -> do
+      where('EXISTS(?)',
+        RunnerManager.select(1)
+          .where(RunnerManager.arel_table[:runner_id].eq(arel_table[:id]))
+          .where(RunnerManager.arel_table[:contacted_at].gt(online_contact_time_deadline))
+          .limit(1)
+      )
+    end
     scope :recent, -> do
       timestamp = stale_deadline
 
diff --git a/spec/models/ci/runner_spec.rb b/spec/models/ci/runner_spec.rb
index 56e69cc2b9c9..13307fe91a9c 100644
--- a/spec/models/ci/runner_spec.rb
+++ b/spec/models/ci/runner_spec.rb
@@ -612,12 +612,24 @@ def stub_redis_runner_contacted_at(value)
   end
 
   describe '.online', :freeze_time do
-    subject { described_class.online }
+    subject(:scope) { described_class.online }
 
-    let!(:runner1) { create(:ci_runner, :instance, contacted_at: 2.hours.ago) }
-    let!(:runner2) { create(:ci_runner, :instance, contacted_at: 1.second.ago) }
+    let!(:runner1) { create(:ci_runner, :instance) }
 
-    it { is_expected.to match_array([runner2]) }
+    context 'when runners have no managers attached' do
+      it { is_expected.to be_empty }
+    end
+
+    context 'when runners have managers attached' do
+      let!(:runner2) { create(:ci_runner, :instance) }
+
+      before do
+        create(:ci_runner_machine, runner: runner1, contacted_at: 2.hours.ago)
+        create(:ci_runner_machine, runner: runner2, contacted_at: 1.second.ago)
+      end
+
+      it { is_expected.to contain_exactly runner2 }
+    end
   end
 
   describe '#online?', :clean_gitlab_redis_cache, :freeze_time do

This should be done before we introduce with_version, ideally on a separate MR blocking this one.

@markus.ferrell I noticed that there's no issue associated with these MRs. Would you mind creating one, so that we have a single issue linked to the different MRs that are related to this work?

note: @markus.ferrell you'll also want to update the following file, so that the counts on the top of the page reflect the runners with the matched version:

diff --git a/ee/app/assets/javascripts/ci/runner/graphql/list/all_runners_count.query.graphql b/ee/app/assets/javascripts/ci/runner/graphql/list/all_runners_count.query.graphql
index b1e537f2f064..6889a71d056f 100644
--- a/ee/app/assets/javascripts/ci/runner/graphql/list/all_runners_count.query.graphql
+++ b/ee/app/assets/javascripts/ci/runner/graphql/list/all_runners_count.query.graphql
@@ -5,6 +5,7 @@ query getAllRunnersCountEE(
   $type: CiRunnerType
   $tagList: [String!]
   $search: String
+  $version: String
 ) {
   runners(
     paused: $paused
@@ -13,6 +14,7 @@ query getAllRunnersCountEE(
     type: $type
     tagList: $tagList
     search: $search
+    version: $version
   ) {
     count
   }

changed the description

added workflowin dev label

mentioned in issue #422046 (closed)

added grouprunner label and removed grouppipeline execution label

added [Deprecated] Category:Runner Fleet label

removed Category:Runner label

changed the description

changed milestone to %16.3

added linked-issue label

added 1 commit

• 568f89e2 - change version to version_prefix

Compare with previous version

added 1 commit

• 64ff860b - bugfixes

Compare with previous version

@markus.ferrell Due to a long standing issue (#352455), to prevent potential outages we should split this MR into frontend and API/backend changes.

The idea is to first merge the API/backend changes and wait until they hit production, and then merge the frontend changes.

These are the frontend changes that would have to go to a separate MR:

• assets/javascr‎ipts/ci/runner‎
• ee/app/assets/javascript‎s/ci/runner/graphql/list
• spec/frontend/ci/runner/admin_runners/admin_runners_app_spec.js
• locale/gitlab.pot

I am sorry this creates extra churn.

added 1 commit

• b1427ee5 - Revert frontend changes

Compare with previous version

added 1 commit

• ad4ced59 - More name changes and an extra test

Compare with previous version

added 1 commit

• aef892c9 - wip case sensitive filter

Compare with previous version

This is my last day at Kitware so somebody else will be picking up this issue in the future. @joe-snyder for awareness

changed the description

assigned to @joe-snyder and unassigned @markus.ferrell

added 1317 commits

• aef892c9...0620a7e1 - 1309 commits from branch gitlab-org:master
• d465bf0e - Sort admin runners page by runner version
• 12d2fade - Review changes
• 3395d15e - change version to version_prefix
• 3712697f - bugfixes
• d3fa7b10 - Revert frontend changes
• 6065a639 - More name changes and an extra test
• 55803e3e - wip case sensitive filter
• c2244d98 - Add SQL sanitization to query

Compare with previous version

added 1006 commits

• c2244d98...bbee4d39 - 998 commits from branch gitlab-org:master
• 1d9ef62c - Sort admin runners page by runner version
• c710798e - Review changes
• 31faa986 - change version to version_prefix
• 12cd0843 - bugfixes
• 30a408fd - Revert frontend changes
• 1b93bd9f - More name changes and an extra test
• 20178dea - wip case sensitive filter
• 7e799b70 - Add SQL sanitization to query

Compare with previous version

Hey @jarka , mind doing the database initial review?

changed milestone to %16.4

added workflowready for review label and removed blocked workflowin dev labels

requested review from @jarka

@jarka @pedropombeiro, this Community contribution is ready for review.

• Do you have capacity and domain expertise to review this? We are mindful of your time, so if you are not able to take this on, please re-assign to one or more other reviewers.
• Add the workflowin dev label if the merge request needs action from the author.

This message was generated automatically. You're welcome to improve it.

changed the description

requested review from @fneill

added 1 commit

• d83545cb - Update documentation string for version_prefix

Compare with previous version

approved this merge request

removed review request for @fneill

added 1313 commits

• d83545cb...ba731998 - 1304 commits from branch gitlab-org:master
• dc4f700c - Sort admin runners page by runner version
• 92e9cfc6 - Review changes
• b88c0a99 - change version to version_prefix
• 7ae90647 - bugfixes
• 38d218d5 - Revert frontend changes
• 7d2b324a - More name changes and an extra test
• 4120de9f - wip case sensitive filter
• 5c56c59a - Add SQL sanitization to query
• dcdb3565 - Update documentation string for version_prefix

Compare with previous version

added 826 commits

• dcdb3565...a88afb44 - 816 commits from branch gitlab-org:master
• aea25a35 - Sort admin runners page by runner version
• feb5622d - Review changes
• 8d54a6d9 - change version to version_prefix
• 8d10db48 - bugfixes
• cd0cee8d - Revert frontend changes
• 3d5523b0 - More name changes and an extra test
• 9a9fcec3 - wip case sensitive filter
• 5b0c476f - Add SQL sanitization to query
• 6be6e1da - Update documentation string for version_prefix
• 5e32054d - Properly increment runner number

Compare with previous version

added 1 commit

• 61fac49e - Add comment explaining edge case in test

Compare with previous version

@jarka, @pedropombeiro, this Community contribution was recently assigned to you for review.

• Do you still have capacity to review this? We are mindful of your time, so if you are not able to take this on, please re-assign to one or more other reviewers.
• Add the workflowin dev label if the merge request needs action from the author.

This message was generated automatically. You're welcome to improve it.

added automation:reviewers-reminded label

mentioned in commit gitlab-community/gitlab@8e3cc358

added 3060 commits

• 61fac49e...95894f6e - 3048 commits from branch gitlab-org:master
• 95894f6e...8dbff2c8 - 2 earlier commits
• d87e2db5 - Review changes
• 037740c3 - change version to version_prefix
• a7b47ccd - bugfixes
• 0926603a - Revert frontend changes
• ce3130f4 - More name changes and an extra test
• 8eb51ce5 - wip case sensitive filter
• 354240f4 - Add SQL sanitization to query
• f73a9504 - Update documentation string for version_prefix
• a87af05a - Add comment explaining edge case in test
• 7e95f092 - Add major version migration index

Compare with previous version

@pedropombeiro, thanks for the comments I've addressed them in this latest push. PTAL again!

added 229 commits

• 7e95f092...d37a9e88 - 218 commits from branch gitlab-org:master
• c57a6b13 - 1 earlier commit
• 957fece6 - Review changes
• 6e5e2c4b - change version to version_prefix
• ee3bc952 - bugfixes
• 5d77ff02 - Revert frontend changes
• b24825fb - More name changes and an extra test
• aaa79b1a - wip case sensitive filter
• ae61d2b0 - Add SQL sanitization to query
• 867a4ed4 - Update documentation string for version_prefix
• 1671eddb - Add comment explaining edge case in test
• 6f961a5e - Add major version migration index

Compare with previous version

added 498 commits

• 6f961a5e...e284e202 - 487 commits from branch gitlab-org:master
• 9b2c2c56 - 1 earlier commit
• 9e72fec2 - Review changes
• 76a3a7bf - change version to version_prefix
• d5d7e412 - bugfixes
• e896e383 - Revert frontend changes
• ea1f7f21 - More name changes and an extra test
• 2281ae3a - wip case sensitive filter
• 8094c0a9 - Add SQL sanitization to query
• c66db352 - Update documentation string for version_prefix
• a60dad40 - Add comment explaining edge case in test
• 61856644 - Add Version migration indexes

Compare with previous version

removed review request for @jarka

changed the description

added 625 commits

• 61856644...fc179736 - 613 commits from branch gitlab-org:master
• fc179736...87bd0728 - 2 earlier commits
• a385c2ea - change version to version_prefix
• 0c61be66 - bugfixes
• 29785db2 - Revert frontend changes
• 790bef65 - More name changes and an extra test
• aa9644c8 - wip case sensitive filter
• d7a9d09c - Add SQL sanitization to query
• 39342477 - Update documentation string for version_prefix
• 7e3edafc - Add comment explaining edge case in test
• bd5e9e6a - Add Version migration indexes
• b547883a - Update query to use substrings

Compare with previous version

changed the description

changed milestone to %16.6

All findings based on the AppSec custom Semgrep rules have been resolved!

added Data WarehouseImpact Check label

added Data WarehouseNot Impacted label and removed Data WarehouseImpact Check label

changed the description

added Data WarehouseImpact Check label and removed Data WarehouseNot Impacted label

requested review from @minac

added workflowin dev label and removed workflowready for review label

removed automation:reviewers-reminded label

requested review from @vshushlin

added databasereviewed label and removed databasereview pending label

requested review from @mayra-cabrera and removed review request for @minac

Database migrations (on the main database)

Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

Migration	Type	Total runtime	Result	DB size change
20231002144140 - AddSemverIndexCiRunnerMachines	Regular	4.4 s		+24.00 KiB

Runtime Histogram for all migrations

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	6
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20231002144140 - AddSemverIndexCiRunnerMachines

• Type: Regular
• Duration: 4.4 s
• Database size change: +24.00 KiB

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	20.4 ms	20.4 ms	20.4 ms	0	CREATE INDEX CONCURRENTLY "index_ci_runner_machines_on_major_version_trigram" ON "ci_runner_machines" (((substring(version from '^\d+.'))), version, runner_id)
1	9.4 ms	9.4 ms	9.4 ms	0	CREATE INDEX CONCURRENTLY "index_ci_runner_machines_on_minor_version_trigram" ON "ci_runner_machines" (((substring(version from '^\d+.\d+.'))), version, runner_id)
1	5.4 ms	5.4 ms	5.4 ms	1	SELECT "feature_gates"."key", "feature_gates"."value" FROM "feature_gates" WHERE "feature_gates"."feature_key" = $1
1	1.9 ms	1.9 ms	1.9 ms	0	CREATE INDEX CONCURRENTLY "index_ci_runner_machines_on_patch_version_trigram" ON "ci_runner_machines" (((substring(version from '^\d+.\d+.\d+'))), version, runner_id)
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for AddSemverIndexCiRunnerMachines

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	6
0.1 seconds - 1 second	0
1 second - 5 seconds	0
5 seconds - 15 seconds	0
15 seconds - 5 minutes	0
5 minutes +	0

Other information

Other migrations pending on GitLab.com

Migration	Type	Total runtime	Result	DB size change
20230927045103 - AsyncIdxVulnerabilityOccurencesOnPrimIdenId	Post deploy	3.2 s		+8.00 KiB [note]
20231004080224 - SwapColumnsForCiStagesPipelineIdBigint	Post deploy	3.0 s		+0.00 B
20231009115743 - AddDuplicateIndexRuleTypeFourAndApplicableColumn	Post deploy	350.5 s		+1.18 GiB
20231013031159 - SwapColumnsForCiPipelinesPipelineIdBigint	Post deploy	2.5 s		+0.00 B
20231013181758 - RemoveTempIndexForProjectStatisticsPipelineArtifactsSizeMigration	Post deploy	2.4 s		-6.53 MiB
20231016100238 - RemoveUsersProjectsCreatorIdFk	Post deploy	2.6 s		+0.00 B
20231016101611 - RemoveUsersProjectsMarkedForDeletionByUserIdFk	Post deploy	2.0 s		+0.00 B
20231017064317 - SwapColumnsForCiPipelineVariablesPipelineIdBigint	Post deploy	2.7 s		+0.00 B
20231017172156 - AddIndexOnProjectsForAdjournedDeletion	Post deploy	384.3 s		+2.65 MiB

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2425819-11564240-main	2023-10-18T21:50:44Z	2023-10-18T14:39:11Z	2023-10-19 10:07:42 +0000
database-testing-2425819-11564240-ci	2023-10-18T21:50:44Z	2023-10-18T20:46:54Z	2023-10-19 10:07:42 +0000

Job artifacts

Database migrations (on the ci database)

Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

Migration	Type	Total runtime	Result	DB size change
20231002144140 - AddSemverIndexCiRunnerMachines	Regular	16.8 s		+29.49 MiB

Runtime Histogram for all migrations

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	2
0.1 seconds - 1 second	0
1 second - 5 seconds	2
5 seconds - 15 seconds	1
15 seconds - 5 minutes	0
5 minutes +	0

Migration: 20231002144140 - AddSemverIndexCiRunnerMachines

• Type: Regular
• Duration: 16.8 s
• Database size change: +29.49 MiB

Calls	Total Time	Max Time	Mean Time	Rows	Query
1	7942.4 ms	7942.4 ms	7942.4 ms	0	CREATE INDEX CONCURRENTLY "index_ci_runner_machines_on_major_version_trigram" ON "ci_runner_machines" (((substring(version from '^\d+.'))), version, runner_id)
1	2132.5 ms	2132.5 ms	2132.5 ms	0	CREATE INDEX CONCURRENTLY "index_ci_runner_machines_on_patch_version_trigram" ON "ci_runner_machines" (((substring(version from '^\d+.\d+.\d+'))), version, runner_id)
1	2026.4 ms	2026.4 ms	2026.4 ms	0	CREATE INDEX CONCURRENTLY "index_ci_runner_machines_on_minor_version_trigram" ON "ci_runner_machines" (((substring(version from '^\d+.\d+.'))), version, runner_id)
2	0.0 ms	0.0 ms	0.0 ms	2	SELECT pg_backend_pid()

Histogram for AddSemverIndexCiRunnerMachines

Query Runtime	Count
0 seconds - 0.01 seconds	0
0.01 seconds - 0.1 seconds	2
0.1 seconds - 1 second	0
1 second - 5 seconds	2
5 seconds - 15 seconds	1
15 seconds - 5 minutes	0
5 minutes +	0

Other information

Other migrations pending on GitLab.com

Migration	Type	Total runtime	Result	DB size change
20231009115743 - AddDuplicateIndexRuleTypeFourAndApplicableColumn	Post deploy	3.3 s		+8.00 KiB [note]
20231013181758 - RemoveTempIndexForProjectStatisticsPipelineArtifactsSizeMigration	Post deploy	3.3 s		-8.00 KiB
20231017064317 - SwapColumnsForCiPipelineVariablesPipelineIdBigint	Post deploy	3.5 s		+0.00 B
20231017172156 - AddIndexOnProjectsForAdjournedDeletion	Post deploy	5.2 s		+8.00 KiB [note]

Clone details

Clone ID	Clone Created At	Clone Data Timestamp	Expected Removal Time
database-testing-2425819-11564240-main	2023-10-18T21:50:44Z	2023-10-18T14:39:11Z	2023-10-19 10:07:42 +0000
database-testing-2425819-11564240-ci	2023-10-18T21:50:44Z	2023-10-18T20:46:54Z	2023-10-19 10:07:42 +0000

Job artifacts

---

Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic

added database-testing-automation label

Thanks @joe-snyder and @pedropombeiro I've left some thoughts from database.

removed review request for @mayra-cabrera

added 527 commits

• b547883a...d8745e77 - 514 commits from branch gitlab-org:master
• d8745e77...adf04e9e - 3 earlier commits
• d53c5222 - bugfixes
• 11cedbd0 - Revert frontend changes
• 592bc748 - More name changes and an extra test
• 2d7f3cfc - wip case sensitive filter
• 4d64d52d - Add SQL sanitization to query
• 5d6dd607 - Update documentation string for version_prefix
• c7b7ef4e - Add comment explaining edge case in test
• 3610ef58 - Add Version migration indexes
• f2615cd2 - Update query to use substrings
• 90e2bec8 - Additional review fixes

Compare with previous version

added Data WarehouseNot Impacted label and removed Data WarehouseImpact Check label

@pedropombeiro @mayra-cabrera, I think I've gotten to the relevant comments. PTAL again when you have the time!

Thank you for all the hard work, @joe-snyder! And thanks to @pedropombeiro and @mayra-cabrera for an excellent review! I love this solution

@joe-snyder I left a bunch of small comments, otherwise, it looks great!

@joe-snyder I also noticed that CI fails for you

For future MRs we can add you to our community fork: https://gitlab.com/gitlab-community#how-to and you won't be limited by the free CI minutes

For now, I just triggered the pipeline on this MR.

@joe-snyder I think you've forgot to push the latest changes

added 534 commits

• 90e2bec8...3ec068d6 - 521 commits from branch gitlab-org:master
• 3ec068d6...28a4640a - 3 earlier commits
• 3497a6b9 - bugfixes
• 7c82fd25 - Revert frontend changes
• d2d62dd2 - More name changes and an extra test
• 0a2704ff - wip case sensitive filter
• 4875ab37 - Add SQL sanitization to query
• cb45962b - Update documentation string for version_prefix
• c5017bcf - Add comment explaining edge case in test
• 2dc21bb9 - Add Version migration indexes
• ef36409f - Update query to use substrings
• 33797a2c - Additional review fixes

Compare with previous version

added 163 commits

• 33797a2c...f459b29f - 150 commits from branch gitlab-org:master
• f459b29f...1aac73aa - 3 earlier commits
• 8f303ff7 - bugfixes
• cdc1be5d - Revert frontend changes
• ba067e3c - More name changes and an extra test
• 472c9c92 - wip case sensitive filter
• da6c0471 - Add SQL sanitization to query
• c804d333 - Update documentation string for version_prefix
• 8e579a5d - Add comment explaining edge case in test
• f09124a4 - Add Version migration indexes
• 483ad4ff - Update query to use substrings
• cc1268f5 - Additional review fixes

Compare with previous version

added 176 commits

• cc1268f5...b6a59a37 - 163 commits from branch gitlab-org:master
• b6a59a37...a299e1db - 3 earlier commits
• 8600595d - bugfixes
• 945408e7 - Revert frontend changes
• 4d535d93 - More name changes and an extra test
• 42351383 - wip case sensitive filter
• f3a6078e - Add SQL sanitization to query
• 5a95ce9b - Update documentation string for version_prefix
• bd65d26e - Add comment explaining edge case in test
• 956f0f43 - Add Version migration indexes
• 18702c72 - Update query to use substrings
• 474bfcf1 - Additional review fixes

Compare with previous version

mentioned in issue #428771 (closed)

resolved all threads

added feature flagskipped label

Alright, @joe-snyder! Looks good

Can you please rebase on the current master? Filter Admin Runners by Creator (!132997 - merged) introduced some conflicts. And let's squash the commits to make the danger happy: !118829 (comment 1512120307)

Then pass it to me and Mayra, and I think we can merge it

added 99 commits

• 474bfcf1...8e609d13 - 98 commits from branch gitlab-org:master
• 6e31228e - Sort admin runners page by runner version

Compare with previous version

approved this merge request

added databaseapproved label and removed databasereviewed label

resolved all threads

approved this merge request

Thank you so much for all the hard work, @joe-snyder and all reviewers! I'm really happy with the final solution and the state of this MR

I kicked off another pipeline and set MWPS!

enabled an automatic merge when the pipeline for 537c07c6 succeeds

merged

@markus.ferrell, how was your code review experience with this merge request? Please tell us how we can continue to iterate and improve:

1. React with a or a on this comment to describe your experience.
2. Create a new comment starting with @gitlab-bot feedback below, and leave any additional feedback you have for us in the comment.

Interested in learning more tips and tricks to solve your next challenge faster? Subscribe to the GitLab Community Newsletter for contributor-focused content and opportunities to level up.

Thanks for your help!

This message was generated automatically. You're welcome to improve it.

mentioned in commit fd5d1014

Hello @markus.ferrell

The database team is looking for ways to improve the database review process and we would love your help!

If you'd be open to someone on the database team reaching out to you for a chat, or if you'd like to leave some feedback asynchronously, just post a reply to this comment mentioning:

@gitlab-org/database-team

And someone will be by shortly!

Thanks for your help!

This message was generated automatically. You're welcome to improve it.

added workflowstaging-canary label and removed workflowin dev label

added workflowcanary label and removed workflowstaging-canary label

added workflowstaging label and removed workflowcanary label

added workflowproduction label and removed workflowstaging label

added workflowpost-deploy-db-staging label and removed workflowproduction label

added workflowpost-deploy-db-production label and removed workflowpost-deploy-db-staging label

mentioned in merge request !135025 (merged)

added releasedcandidate label

mentioned in issue gitlab-com/www-gitlab-com#34730 (closed)