Remove last_of_each_version scope and add unique index for npm packages
Context
We take measures to eliminate duplicate npm packages in the database.
- Firstly the root cause of duplicate packages was identified and removed: #390097 (closed)
- Secondary we removed existing duplicate packages using background migration: #390098 (closed)
Now is the last step - add database constraint: a unique index to ensure a better data coherence.
Note: the validation on the application level already exists (details)
Additionally this MR is doing a cleanup and removes last_of_each_version
and last_of_each_version_ids
scopes from Packages::Package
model, that produce a subquery like: SELECT MAX(id) AS id from packages_packages
and we don't need that anymore.
Why we have to combine the code removal and a new index in one MR?
We have several places in the different code areas were we ensure correct behavior with last_of_each_version
or last_of_each_version_ids
scopes using tests tests where we create duplicate packages. With a new unique database index we won't be able to do that anymore, therefore we agreed to merge changes in one MR.
What does this MR do and why?
- This MR adds a new unique index for npm packages.
- Removes
last_of_each_version
andlast_of_each_version_ids
scopes fromPackages#Package
Screenshots or screen recordings
No.
Migrations output
Migration Up
$ rails db:migrate:main
main: == [advisory_lock_connection] object_id: 228820, pg_backend_pid: 60831
main: == 20230616082958 AddUniqueIndexForNpmPackagesOnProjectIdNameVersion: migrating
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.1014s
main: -- index_exists?(:packages_packages, [:project_id, :name, :version], {:name=>"idx_packages_on_project_id_name_version_unique_when_npm", :unique=>true, :where=>"package_type = 2 AND status <> 4", :algorithm=>:concurrently})
main: -> 0.0079s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- add_index(:packages_packages, [:project_id, :name, :version], {:name=>"idx_packages_on_project_id_name_version_unique_when_npm", :unique=>true, :where=>"package_type = 2 AND status <> 4", :algorithm=>:concurrently})
main: -> 0.0017s
main: -- execute("RESET statement_timeout")
main: -> 0.0002s
main: == 20230616082958 AddUniqueIndexForNpmPackagesOnProjectIdNameVersion: migrated (0.1238s)
main: == [advisory_lock_connection] object_id: 228820, pg_backend_pid: 60831
Migration Down
$ rails db:rollback:main
main: == [advisory_lock_connection] object_id: 228540, pg_backend_pid: 60291
main: == 20230616082958 AddUniqueIndexForNpmPackagesOnProjectIdNameVersion: reverting
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.1232s
main: -- indexes(:packages_packages)
main: -> 0.0081s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- remove_index(:packages_packages, {:algorithm=>:concurrently, :name=>"idx_packages_on_project_id_name_version_unique_when_npm"})
main: -> 0.0010s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20230616082958 AddUniqueIndexForNpmPackagesOnProjectIdNameVersion: reverted (0.1461s)
main: == [advisory_lock_connection] object_id: 228540, pg_backend_pid: 60291
How to set up and validate locally
No.
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 #390099 (closed)