Change unique constraints to prepare ci_job_artifact_states for partitioning
What does this MR do and why?
Adds partition_id to the primary key, making it a composite key, to prepare ci_job_artifact_states for partitioning.
References
Related to #525449 (closed)
This is step 3 and 5 of this guide https://docs.gitlab.com/development/database/partitioning/list/#step-3---enforce-unique-constraint. Step 4 is already covered so it should be ok to merge both steps in this MR.
Screenshots or screen recordings
Running the migration
gitlab|525449-change-unique-constraints-ci_job_artifact_states⚡ ⇒ bin/rails db:migrate:main VERSION=20250428232147 RAILS_ENV=development
DEPRECATION WARNING: Support for `config.active_support.cache_format_version = 6.1` has been deprecated and will be removed in Rails 7.2.
Check the Rails upgrade guide at https://guides.rubyonrails.org/upgrading_ruby_on_rails.html#new-activesupport-cache-serialization-format
for more information on how to upgrade.
(called from initialize at /Users/natanaelsilva/dev/gdk/gitlab/lib/gitlab/patch/redis_cache_store.rb:16)
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 12080
main: == 20250428232147 ChangeUniqueContraintsCiJobArtifactStates: migrating ========
main: -- indexes(:ci_job_artifact_states)
main: -> 0.0191s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0003s
main: -- indexes(:ci_job_artifact_states)
main: -> 0.0015s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- remove_index(:ci_job_artifact_states, {:algorithm=>:concurrently, :name=>:index_ci_job_artifact_states_on_job_artifact_id_partition_id})
main: -> 0.0013s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0003s
main: -- index_exists?(:ci_job_artifact_states, [:job_artifact_id, :partition_id], {:unique=>true, :name=>:index_ci_job_artifact_states_on_job_artifact_id_partition_id, :algorithm=>:concurrently})
main: -> 0.0014s
main: -- add_index(:ci_job_artifact_states, [:job_artifact_id, :partition_id], {:unique=>true, :name=>:index_ci_job_artifact_states_on_job_artifact_id_partition_id, :algorithm=>:concurrently})
main: -> 0.0016s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- quote_table_name(:ci_job_artifact_states)
main: -> 0.0000s
main: -- quote_column_name(:ci_job_artifact_states_pkey)
main: -> 0.0000s
main: -- execute("ALTER TABLE \"ci_job_artifact_states\" DROP CONSTRAINT \"ci_job_artifact_states_pkey\" CASCADE\n")
main: -> 0.0021s
main: -- quote_table_name(:ci_job_artifact_states)
main: -> 0.0000s
main: -- quote_table_name(:ci_job_artifact_states_pkey)
main: -> 0.0000s
main: -- quote_table_name(:index_ci_job_artifact_states_on_job_artifact_id_partition_id)
main: -> 0.0000s
main: -- execute("ALTER TABLE \"ci_job_artifact_states\" ADD CONSTRAINT \"ci_job_artifact_states_pkey\" PRIMARY KEY USING INDEX \"index_ci_job_artifact_states_on_job_artifact_id_partition_id\"\n")
main: -> 0.0005s
main: == 20250428232147 ChangeUniqueContraintsCiJobArtifactStates: migrated (0.0689s)
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 12080Table state:
gitlabhq_development=# \d ci_job_artifact_states
Table "public.ci_job_artifact_states"
Column | Type | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
verification_started_at | timestamp with time zone | | |
verification_retry_at | timestamp with time zone | | |
verified_at | timestamp with time zone | | |
job_artifact_id | bigint | | not null |
verification_state | smallint | | not null | 0
verification_retry_count | smallint | | |
verification_checksum | bytea | | |
verification_failure | text | | |
partition_id | bigint | | not null |
project_id | bigint | | |
Indexes:
"ci_job_artifact_states_pkey" PRIMARY KEY, btree (job_artifact_id, partition_id)
"index_job_artifact_states_failed_verification" btree (verification_retry_at NULLS FIRST) WHERE verification_state = 3
"index_job_artifact_states_needs_verification" btree (verification_state) WHERE verification_state = 0 OR verification_state = 3
"index_job_artifact_states_on_verification_state" btree (verification_state)
"index_job_artifact_states_pending_verification" btree (verified_at NULLS FIRST) WHERE verification_state = 0
"index_on_job_artifact_id_partition_id_verification_state" btree (verification_state, job_artifact_id, partition_id)
Check constraints:
"check_df832b66ea" CHECK (char_length(verification_failure) <= 255)
Foreign-key constraints:
"fk_rails_80a9cba3b2_p" FOREIGN KEY (partition_id, job_artifact_id) REFERENCES p_ci_job_artifacts(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
gitlab_schema_write_trigger_for_ci_job_artifact_states BEFORE INSERT OR DELETE OR UPDATE OR TRUNCATE ON ci_job_artifact_states FOR EACH STATEMENT EXECUTE FUNCTION gitlab_schema_prevent_write()
trigger_a465de38164e BEFORE INSERT OR UPDATE ON ci_job_artifact_states FOR EACH ROW EXECUTE FUNCTION trigger_a465de38164e()Reverting changes:
gitlab|525449-change-unique-constraints-ci_job_artifact_states⚡ ⇒ bin/rails db:migrate:down:main VERSION=20250428232147 RAILS_ENV=development
DEPRECATION WARNING: Support for `config.active_support.cache_format_version = 6.1` has been deprecated and will be removed in Rails 7.2.
Check the Rails upgrade guide at https://guides.rubyonrails.org/upgrading_ruby_on_rails.html#new-activesupport-cache-serialization-format
for more information on how to upgrade.
(called from initialize at /Users/natanaelsilva/dev/gdk/gitlab/lib/gitlab/patch/redis_cache_store.rb:16)
main: == [advisory_lock_connection] object_id: 129280, pg_backend_pid: 12008
main: == 20250428232147 ChangeUniqueContraintsCiJobArtifactStates: reverting ========
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0182s
main: -- index_exists?(:ci_job_artifact_states, :job_artifact_id, {:unique=>true, :name=>:temp_ci_job_artifact_states_job_artifact_id_idx, :algorithm=>:concurrently})
main: -> 0.0021s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- add_index(:ci_job_artifact_states, :job_artifact_id, {:unique=>true, :name=>:temp_ci_job_artifact_states_job_artifact_id_idx, :algorithm=>:concurrently})
main: -> 0.0021s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- quote_table_name(:ci_job_artifact_states)
main: -> 0.0000s
main: -- quote_column_name(:ci_job_artifact_states_pkey)
main: -> 0.0000s
main: -- execute("ALTER TABLE \"ci_job_artifact_states\" DROP CONSTRAINT \"ci_job_artifact_states_pkey\" CASCADE\n")
main: -> 0.0024s
main: -- quote_table_name(:ci_job_artifact_states)
main: -> 0.0000s
main: -- quote_table_name(:ci_job_artifact_states_pkey)
main: -> 0.0000s
main: -- quote_table_name(:temp_ci_job_artifact_states_job_artifact_id_idx)
main: -> 0.0000s
main: -- execute("ALTER TABLE \"ci_job_artifact_states\" ADD CONSTRAINT \"ci_job_artifact_states_pkey\" PRIMARY KEY USING INDEX \"temp_ci_job_artifact_states_job_artifact_id_idx\"\n")
main: -> 0.0005s
main: -- indexes(:ci_job_artifact_states)
main: -> 0.0015s
main: -- current_schema(nil)
main: -> 0.0001s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0002s
main: -- index_exists?(:ci_job_artifact_states, [:job_artifact_id, :partition_id], {:unique=>false, :name=>:index_ci_job_artifact_states_on_job_artifact_id_partition_id, :algorithm=>:concurrently})
main: -> 0.0012s
main: -- add_index(:ci_job_artifact_states, [:job_artifact_id, :partition_id], {:unique=>false, :name=>:index_ci_job_artifact_states_on_job_artifact_id_partition_id, :algorithm=>:concurrently})
main: -> 0.0012s
main: == 20250428232147 ChangeUniqueContraintsCiJobArtifactStates: reverted (0.0601s)
main: == [advisory_lock_connection] object_id: 129280, pg_backend_pid: 12008Table restored:
gitlabhq_development=# \d ci_job_artifact_states
Table "public.ci_job_artifact_states"
Column | Type | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
verification_started_at | timestamp with time zone | | |
verification_retry_at | timestamp with time zone | | |
verified_at | timestamp with time zone | | |
job_artifact_id | bigint | | not null |
verification_state | smallint | | not null | 0
verification_retry_count | smallint | | |
verification_checksum | bytea | | |
verification_failure | text | | |
partition_id | bigint | | not null |
project_id | bigint | | |
Indexes:
"ci_job_artifact_states_pkey" PRIMARY KEY, btree (job_artifact_id)
"index_ci_job_artifact_states_on_job_artifact_id_partition_id" btree (job_artifact_id, partition_id)
"index_job_artifact_states_failed_verification" btree (verification_retry_at NULLS FIRST) WHERE verification_state = 3
"index_job_artifact_states_needs_verification" btree (verification_state) WHERE verification_state = 0 OR verification_state = 3
"index_job_artifact_states_on_verification_state" btree (verification_state)
"index_job_artifact_states_pending_verification" btree (verified_at NULLS FIRST) WHERE verification_state = 0
"index_on_job_artifact_id_partition_id_verification_state" btree (verification_state, job_artifact_id, partition_id)
Check constraints:
"check_df832b66ea" CHECK (char_length(verification_failure) <= 255)
Foreign-key constraints:
"fk_rails_80a9cba3b2_p" FOREIGN KEY (partition_id, job_artifact_id) REFERENCES p_ci_job_artifacts(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
gitlab_schema_write_trigger_for_ci_job_artifact_states BEFORE INSERT OR DELETE OR UPDATE OR TRUNCATE ON ci_job_artifact_states FOR EACH STATEMENT EXECUTE FUNCTION gitlab_schema_prevent_write()
trigger_a465de38164e BEFORE INSERT OR UPDATE ON ci_job_artifact_states FOR EACH ROW EXECUTE FUNCTION trigger_a465de38164e()How to set up and validate locally
The primary key should be (job_artifact_id, partition_id), no longer just job_artifact_id. Just check on with \d ci_job_artifact_states .
Edited by Natanael Silva