Skip to content

Fix non-deterministic ContainerRepository query method

Rémy Coutable requested to merge fix-non-deterministic-pg11-implementation into master

What does this MR do and why?

This tries to make ContainerRegistry::Migration::EnqueuerWorker#next_repository and ContainerRegistry::Migration::EnqueuerWorker#next_aborted_repository deterministic, as it seems it's not when using PG11.

Related to gitlab-org/quality/engineering-productivity/master-broken-incidents#342 (closed).

Screenshots or screen recordings

ContainerRegistry::Migration::EnqueuerWorker#next_repository 

[5] pry(main)> ContainerRepository.ready_for_import.limit(25).explain
  ContainerRepository Load (1.5ms)  SELECT "container_repositories".* FROM "container_repositories" INNER JOIN "projects" ON "projects"."id" = "container_repositories"."project_id" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" WHERE "container_repositories"."migration_state" = 'default' AND "container_repositories"."created_at" < '2022-01-23 00:00:00' AND (NOT EXISTS (
        SELECT 1
        FROM feature_gates
        WHERE feature_gates.feature_key = 'container_registry_phase_2_deny_list'
        AND feature_gates.key = 'actors'
        AND feature_gates.value = concat('Group:', namespaces.traversal_ids[1])
      )) LIMIT 25 /*application:console,db_config_name:main,console_hostname:Remys-GitLab-MacBook-Pro.local,console_username:remy,line:(pry):5:in `__pry__'*/
=> EXPLAIN for: SELECT "container_repositories".* FROM "container_repositories" INNER JOIN "projects" ON "projects"."id" = "container_repositories"."project_id" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" WHERE "container_repositories"."migration_state" = 'default' AND "container_repositories"."created_at" < '2022-01-23 00:00:00' AND (NOT EXISTS (
        SELECT 1
        FROM feature_gates
        WHERE feature_gates.feature_key = 'container_registry_phase_2_deny_list'
        AND feature_gates.key = 'actors'
        AND feature_gates.value = concat('Group:', namespaces.traversal_ids[1])
      )) LIMIT 25 /*application:console,db_config_name:main,console_hostname:Remys-GitLab-MacBook-Pro.local,console_username:remy,line:(pry):5:in `__pry__'*/
                                                                                    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.58..5.23 rows=1 width=246)
   ->  Nested Loop Anti Join  (cost=0.58..5.23 rows=1 width=246)
         ->  Nested Loop  (cost=0.43..4.87 rows=1 width=271)
               ->  Nested Loop  (cost=0.29..4.47 rows=1 width=250)
                     ->  Index Scan using idx_container_repos_on_migration_state_migration_plan_created on container_repositories  (cost=0.15..2.17 rows=1 width=246)
                           Index Cond: ((migration_state = 'default'::text) AND (created_at < '2022-01-23 00:00:00'::timestamp without time zone))
                     ->  Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects  (cost=0.14..2.16 rows=1 width=8)
                           Index Cond: (id = container_repositories.project_id)
               ->  Index Scan using namespaces_pkey on namespaces  (cost=0.14..0.39 rows=1 width=29)
                     Index Cond: (id = projects.namespace_id)
         ->  Index Only Scan using index_feature_gates_on_feature_key_and_key_and_value on feature_gates  (cost=0.15..0.26 rows=1 width=32)
               Index Cond: ((feature_key = 'container_registry_phase_2_deny_list'::text) AND (key = 'actors'::text) AND (value = concat('Group:', (namespaces.traversal_ids)[1])))
(12 rows)

[6] pry(main)> ContainerRepository.ready_for_import.ordered.limit(25).explain
  ContainerRepository Load (1.2ms)  SELECT "container_repositories".* FROM "container_repositories" INNER JOIN "projects" ON "projects"."id" = "container_repositories"."project_id" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" WHERE "container_repositories"."migration_state" = 'default' AND "container_repositories"."created_at" < '2022-01-23 00:00:00' AND (NOT EXISTS (
        SELECT 1
        FROM feature_gates
        WHERE feature_gates.feature_key = 'container_registry_phase_2_deny_list'
        AND feature_gates.key = 'actors'
        AND feature_gates.value = concat('Group:', namespaces.traversal_ids[1])
      )) ORDER BY "container_repositories"."name" ASC LIMIT 25 /*application:console,db_config_name:main,console_hostname:Remys-GitLab-MacBook-Pro.local,console_username:remy,line:(pry):6:in `__pry__'*/
=> EXPLAIN for: SELECT "container_repositories".* FROM "container_repositories" INNER JOIN "projects" ON "projects"."id" = "container_repositories"."project_id" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" WHERE "container_repositories"."migration_state" = 'default' AND "container_repositories"."created_at" < '2022-01-23 00:00:00' AND (NOT EXISTS (
        SELECT 1
        FROM feature_gates
        WHERE feature_gates.feature_key = 'container_registry_phase_2_deny_list'
        AND feature_gates.key = 'actors'
        AND feature_gates.value = concat('Group:', namespaces.traversal_ids[1])
      )) ORDER BY "container_repositories"."name" ASC LIMIT 25 /*application:console,db_config_name:main,console_hostname:Remys-GitLab-MacBook-Pro.local,console_username:remy,line:(pry):6:in `__pry__'*/
                                                                                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=5.24..5.24 rows=1 width=246)
   ->  Sort  (cost=5.24..5.24 rows=1 width=246)
         Sort Key: container_repositories.name
         ->  Nested Loop Anti Join  (cost=0.58..5.23 rows=1 width=246)
               ->  Nested Loop  (cost=0.43..4.87 rows=1 width=271)
                     ->  Nested Loop  (cost=0.29..4.47 rows=1 width=250)
                           ->  Index Scan using idx_container_repos_on_migration_state_migration_plan_created on container_repositories  (cost=0.15..2.17 rows=1 width=246)
                                 Index Cond: ((migration_state = 'default'::text) AND (created_at < '2022-01-23 00:00:00'::timestamp without time zone))
                           ->  Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects  (cost=0.14..2.16 rows=1 width=8)
                                 Index Cond: (id = container_repositories.project_id)
                     ->  Index Scan using namespaces_pkey on namespaces  (cost=0.14..0.39 rows=1 width=29)
                           Index Cond: (id = projects.namespace_id)
               ->  Index Only Scan using index_feature_gates_on_feature_key_and_key_and_value on feature_gates  (cost=0.15..0.26 rows=1 width=32)
                     Index Cond: ((feature_key = 'container_registry_phase_2_deny_list'::text) AND (key = 'actors'::text) AND (value = concat('Group:', (namespaces.traversal_ids)[1])))
(14 rows)

ContainerRegistry::Migration::EnqueuerWorker#next_aborted_repository 

[8] pry(main)> ContainerRepository.with_migration_state('import_aborted').limit(25).explain
  ContainerRepository Load (0.5ms)  SELECT "container_repositories".* FROM "container_repositories" WHERE ("container_repositories"."migration_state" IN ('import_aborted')) LIMIT 25 /*application:console,db_config_name:main,console_hostname:Remys-GitLab-MacBook-Pro.local,console_username:remy,line:(pry):8:in `__pry__'*/
=> EXPLAIN for: SELECT "container_repositories".* FROM "container_repositories" WHERE ("container_repositories"."migration_state" IN ('import_aborted')) LIMIT 25 /*application:console,db_config_name:main,console_hostname:Remys-GitLab-MacBook-Pro.local,console_username:remy,line:(pry):8:in `__pry__'*/
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.15..2.17 rows=1 width=246)
   ->  Index Scan using idx_container_repos_on_migration_state_migration_plan_created on container_repositories  (cost=0.15..2.17 rows=1 width=246)
         Index Cond: (migration_state = 'import_aborted'::text)
(3 rows)

[9] pry(main)> ContainerRepository.with_migration_state('import_aborted').ordered.limit(25).explain
  ContainerRepository Load (0.5ms)  SELECT "container_repositories".* FROM "container_repositories" WHERE ("container_repositories"."migration_state" IN ('import_aborted')) ORDER BY "container_repositories"."name" ASC LIMIT 25 /*application:console,db_config_name:main,console_hostname:Remys-GitLab-MacBook-Pro.local,console_username:remy,line:(pry):9:in `__pry__'*/
=> EXPLAIN for: SELECT "container_repositories".* FROM "container_repositories" WHERE ("container_repositories"."migration_state" IN ('import_aborted')) ORDER BY "container_repositories"."name" ASC LIMIT 25 /*application:console,db_config_name:main,console_hostname:Remys-GitLab-MacBook-Pro.local,console_username:remy,line:(pry):9:in `__pry__'*/
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2.17..2.18 rows=1 width=246)
   ->  Sort  (cost=2.17..2.18 rows=1 width=246)
         Sort Key: name
         ->  Index Scan using idx_container_repos_on_migration_state_migration_plan_created on container_repositories  (cost=0.15..2.17 rows=1 width=246)
               Index Cond: (migration_state = 'import_aborted'::text)
(5 rows)

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.

Edited by Rémy Coutable

Merge request reports