Add unique index for Container Repository and Terraform State registries (!54841) · Merge requests · GitLab.org / GitLab

Valery Sizov requested to merge 296928-fix-unique-foreign-keys-for-geo-registries into master Feb 22, 2021

What does this MR do?

There are a number of registry tables which do not have a uniqueness constraint on their foreign keys:

job_artifact_registry
merge_request_diff_registry
package_file_registry
container_repository_registry
terraform_state_version_registry

This MR tackles the last two ones and it was split off from !54464 (closed)

Console output

UP:

== 20210217020154 AddUniqueIndexOnContainerRepositoryRegistry: migrating ======
-- execute("      DELETE FROM container_repository_registry\n      USING (\n        SELECT container_repository_id, MIN(id) as min_id\n        FROM container_repository_registry\n        GROUP BY container_repository_id\n        HAVING COUNT(id) > 1\n      ) as container_repository_registry_duplicates\n      WHERE container_repository_registry_duplicates.container_repository_id = container_repository_registry.container_repository_id\n      AND container_repository_registry_duplicates.min_id <> container_repository_registry.id\n")
   -> 0.0009s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:container_repository_registry, :container_repository_id, {:unique=>true, :name=>"unique_index_container_repository_registry_on_repository_id", :algorithm=>:concurrently})
   -> 0.0016s
-- add_index(:container_repository_registry, :container_repository_id, {:unique=>true, :name=>"unique_index_container_repository_registry_on_repository_id", :algorithm=>:concurrently})
   -> 0.0029s
-- transaction_open?()
   -> 0.0000s
-- indexes(:container_repository_registry)
   -> 0.0019s
-- remove_index(:container_repository_registry, {:algorithm=>:concurrently, :name=>"index_container_repository_registry_on_repository_id"})
   -> 0.0011s
== 20210217020154 AddUniqueIndexOnContainerRepositoryRegistry: migrated (0.0097s)

== 20210217020156 AddUniqueIndexOnTerraformStateVersionRegistry: migrating ====
-- execute("      DELETE FROM terraform_state_version_registry\n      USING (\n        SELECT terraform_state_version_id, MIN(id) as min_id\n        FROM terraform_state_version_registry\n        GROUP BY terraform_state_version_id\n        HAVING COUNT(id) > 1\n      ) as terraform_state_version_registry_duplicates\n      WHERE terraform_state_version_registry_duplicates.terraform_state_version_id = terraform_state_version_registry.terraform_state_version_id\n      AND terraform_state_version_registry_duplicates.min_id <> terraform_state_version_registry.id\n")
   -> 0.0009s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:terraform_state_version_registry, :terraform_state_version_id, {:unique=>true, :name=>"unique_index_tf_state_versions_registry_on_tf_state_versions_id", :algorithm=>:concurrently})
   -> 0.0017s
-- add_index(:terraform_state_version_registry, :terraform_state_version_id, {:unique=>true, :name=>"unique_index_tf_state_versions_registry_on_tf_state_versions_id", :algorithm=>:concurrently})
   -> 0.0022s
-- transaction_open?()
   -> 0.0000s
-- indexes(:terraform_state_version_registry)
   -> 0.0020s
-- remove_index(:terraform_state_version_registry, {:algorithm=>:concurrently, :name=>"index_tf_state_versions_registry_on_tf_state_versions_id"})
   -> 0.0016s
== 20210217020156 AddUniqueIndexOnTerraformStateVersionRegistry: migrated (0.0097s)

DOWN:

== 20210217020154 AddUniqueIndexOnContainerRepositoryRegistry: reverting ======
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:container_repository_registry, :container_repository_id, {:name=>"index_container_repository_registry_on_repository_id", :algorithm=>:concurrently})
   -> 0.0034s
-- add_index(:container_repository_registry, :container_repository_id, {:name=>"index_container_repository_registry_on_repository_id", :algorithm=>:concurrently})
   -> 0.0036s
-- transaction_open?()
   -> 0.0000s
-- indexes(:container_repository_registry)
   -> 0.0018s
-- remove_index(:container_repository_registry, {:algorithm=>:concurrently, :name=>"unique_index_container_repository_registry_on_repository_id"})
   -> 0.0016s
== 20210217020154 AddUniqueIndexOnContainerRepositoryRegistry: reverted (0.0119s)

== 20210217020156 AddUniqueIndexOnTerraformStateVersionRegistry: reverting ====
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:terraform_state_version_registry, :terraform_state_version_id, {:name=>"index_tf_state_versions_registry_on_tf_state_versions_id", :algorithm=>:concurrently})
   -> 0.0037s
-- add_index(:terraform_state_version_registry, :terraform_state_version_id, {:name=>"index_tf_state_versions_registry_on_tf_state_versions_id", :algorithm=>:concurrently})
   -> 0.0031s
-- transaction_open?()
   -> 0.0000s
-- indexes(:terraform_state_version_registry)
   -> 0.0024s
-- remove_index(:terraform_state_version_registry, {:algorithm=>:concurrently, :name=>"unique_index_tf_state_versions_registry_on_tf_state_versions_id"})
   -> 0.0015s
== 20210217020156 AddUniqueIndexOnTerraformStateVersionRegistry: reverted (0.0121s)

Queries:

Container Repository Registry Deletes (Depesz)[https://explain.depesz.com/s/6VfR]:

SQL:

      DELETE FROM container_repository_registry
      USING (
        SELECT container_repository_id, MIN(id) as min_id
        FROM container_repository_registry
        GROUP BY container_repository_id
        HAVING COUNT(id) > 1
      ) as container_repository_registry_duplicates
      WHERE container_repository_registry_duplicates.container_repository_id = container_repository_registry.container_repository_id
      AND container_repository_registry_duplicates.min_id <> container_repository_registry.id

Plan:

 Delete on container_repository_registry  (cost=2009.03..4624.11 rows=80801 width=38) (actual time=130.041..130.042 rows=0 loops=1)
   Buffers: shared hit=81980 dirtied=595
   ->  Nested Loop  (cost=2009.03..4624.11 rows=80801 width=38) (actual time=24.593..44.528 rows=80790 loops=1)
         Join Filter: ((container_repository_registry_duplicates.min_id <> container_repository_registry.id) AND (container_repository_registry.container_repository_id = container_repository_registry_duplicates.container_repository_id))
         Rows Removed by Join Filter: 1
         Buffers: shared hit=1190 dirtied=35
         ->  Subquery Scan on container_repository_registry_duplicates  (cost=2009.03..2009.06 rows=1 width=40) (actual time=24.583..24.585 rows=1 loops=1)
               Buffers: shared hit=595 dirtied=35
               ->  HashAggregate  (cost=2009.03..2009.05 rows=1 width=8) (actual time=24.575..24.576 rows=1 loops=1)
                     Group Key: container_repository_registry_1.container_repository_id
                     Filter: (count(container_repository_registry_1.id) > 1)
                     Buffers: shared hit=595 dirtied=35
                     ->  Seq Scan on container_repository_registry container_repository_registry_1  (cost=0.00..1403.02 rows=80802 width=8) (actual time=0.007..10.225 rows=80791 loops=1)
                           Buffers: shared hit=595 dirtied=35
         ->  Seq Scan on container_repository_registry  (cost=0.00..1403.02 rows=80802 width=14) (actual time=0.008..9.091 rows=80791 loops=1)
               Buffers: shared hit=595
 Planning Time: 0.519 ms

Terraform State Version Registry Deletes (Depesz)[https://explain.depesz.com/s/4vndW]:

SQL:

      DELETE FROM terraform_state_version_registry
      USING (
        SELECT terraform_state_version_id, MIN(id) as min_id
        FROM terraform_state_version_registry
        GROUP BY terraform_state_version_id
        HAVING COUNT(id) > 1
      ) as terraform_state_version_registry_duplicates
      WHERE terraform_state_version_registry_duplicates.terraform_state_version_id = terraform_state_version_registry.terraform_state_version_id
      AND terraform_state_version_registry_duplicates.min_id <> terraform_state_version_registry.id

Plan:

 Delete on terraform_state_version_registry  (cost=2115.88..4870.30 rows=85135 width=46) (actual time=134.908..134.909 rows=0 loops=1)
   Buffers: shared hit=86252
   ->  Nested Loop  (cost=2115.88..4870.30 rows=85135 width=46) (actual time=25.865..47.237 rows=85000 loops=1)
         Join Filter: ((terraform_state_version_registry_duplicates.min_id <> terraform_state_version_registry.id) AND (terraform_state_version_registry.terraform_state_version_id = terraform_state_version_registry_duplicates.terraform_state_version_id))
         Rows Removed by Join Filter: 1
         Buffers: shared hit=1252
         ->  Subquery Scan on terraform_state_version_registry_duplicates  (cost=2115.88..2115.90 rows=1 width=56) (actual time=25.855..25.857 rows=1 loops=1)
               Buffers: shared hit=626
               ->  HashAggregate  (cost=2115.88..2115.89 rows=1 width=16) (actual time=25.851..25.852 rows=1 loops=1)
                     Group Key: terraform_state_version_registry_1.terraform_state_version_id
                     Filter: (count(terraform_state_version_registry_1.id) > 1)
                     Buffers: shared hit=626
                     ->  Seq Scan on terraform_state_version_registry terraform_state_version_registry_1  (cost=0.00..1477.36 rows=85136 width=16) (actual time=0.019..9.925 rows=85001 loops=1)
                           Buffers: shared hit=626
         ->  Seq Scan on terraform_state_version_registry  (cost=0.00..1477.36 rows=85136 width=22) (actual time=0.008..9.726 rows=85001 loops=1)
               Buffers: shared hit=626
 Planning Time: 0.539 ms
 Execution Time: 134.975 ms
(18 rows)

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Related to #296928 (closed)

Edited Feb 23, 2021 by Valery Sizov

Add unique index for Container Repository and Terraform State registries