Add geo database changes for pipeline artifact replication (!57506) · Merge requests · GitLab.org / GitLab

Max Orefice requested to merge mo-add-pipeline-artifact-geo-database into master Mar 25, 2021

Ref: #238464 (closed)

Step #1 to implement PipelineArtifact replication with geo.

What does this MR do?

This MR adds all the necessary database changes required to replicate pipeline artifacts with geo.

It includes the following items:

Add pipeline_artifact_registry to geo schema
Add necessary columns to ci_pipeline_artifacts in order to verify data replication
Add necessary indexes as explained on the geo development framework

Why are we doing this?

The grouptesting is currently replicating our PipelineArtifact with geo.

During the investigation of this new feature in our POC the team decided to implement it in 3 steps:

Adding database changes necessary - !57506 (merged)
Implementing replication + verification
Enabling the feature

Database Review

Click to expand for review

Table

$ bin/rails db:migrate
== 20210325150435 CreatePipelineArtifactRegistry: migrating ===================
-- create_table(:pipeline_artifact_registry, {:id=>:bigserial, :force=>:cascade})
   -> 0.0521s
== 20210325150435 CreatePipelineArtifactRegistry: migrated (0.0521s) ==========

$ bin/rails db:rollback
== 20210325150435 CreatePipelineArtifactRegistry: reverting ===================
-- drop_table(:pipeline_artifact_registry)
   -> 0.0040s
== 20210325150435 CreatePipelineArtifactRegistry: reverted (0.0041s) ==========

Columns

$ bin/rails db:rollback
== 20210325150837 AddVerificationStateToCiPipelineArtifact: migrating =========
-- change_table(:ci_pipeline_artifacts, {:bulk=>true})
   -> 0.0079s
== 20210325150837 AddVerificationStateToCiPipelineArtifact: migrated (0.0079s)

$ bin/rails db:rollback
== 20210325150837 AddVerificationStateToCiPipelineArtifact: reverting =========
== 20210325150837 AddVerificationStateToCiPipelineArtifact: reverted (0.0000s)

Text constraint

$ bin/rails db:migrate
== 20210325151758 AddVerificationFailureLimitToCiPipelineArtifact: migrating ==
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0002s
-- execute("ALTER TABLE ci_pipeline_artifacts\nADD CONSTRAINT ci_pipeline_artifacts_verification_failure_text_limit\nCHECK ( char_length(verification_failure) <= 255 )\nNOT VALID;\n")
   -> 0.0053s
-- current_schema()
   -> 0.0001s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- execute("ALTER TABLE ci_pipeline_artifacts VALIDATE CONSTRAINT ci_pipeline_artifacts_verification_failure_text_limit;")
   -> 0.0019s
-- execute("RESET ALL")
   -> 0.0005s
== 20210325151758 AddVerificationFailureLimitToCiPipelineArtifact: migrated (0.0217s)


$ bin/rails db:rollback
== 20210325151758 AddVerificationFailureLimitToCiPipelineArtifact: reverting ==
-- execute("ALTER TABLE ci_pipeline_artifacts\nDROP CONSTRAINT IF EXISTS ci_pipeline_artifacts_verification_failure_text_limit\n")

Indexes

$ bin/rails db:migrate
== 20210325152011 AddVerificationIndexesToCiPipelineArtifacts: migrating ======
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipeline_artifacts, :verification_state, {:name=>"index_ci_pipeline_artifacts_verification_state", :algorithm=>:concurrently})
   -> 0.0030s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index(:ci_pipeline_artifacts, :verification_state, {:name=>"index_ci_pipeline_artifacts_verification_state", :algorithm=>:concurrently})
   -> 0.0044s
-- execute("RESET ALL")
   -> 0.0005s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipeline_artifacts, :verified_at, {:where=>"(verification_state = 0)", :order=>{:verified_at=>"ASC NULLS FIRST"}, :name=>"index_ci_pipeline_artifacts_pending_verification", :algorithm=>:concurrently})
   -> 0.0018s
-- add_index(:ci_pipeline_artifacts, :verified_at, {:where=>"(verification_state = 0)", :order=>{:verified_at=>"ASC NULLS FIRST"}, :name=>"index_ci_pipeline_artifacts_pending_verification", :algorithm=>:concurrently})
   -> 0.0041s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipeline_artifacts, :verification_retry_at, {:where=>"(verification_state = 3)", :order=>{:verification_retry_at=>"ASC NULLS FIRST"}, :name=>"index_ci_pipeline_artifacts_failed_verification", :algorithm=>:concurrently})
   -> 0.0028s
-- add_index(:ci_pipeline_artifacts, :verification_retry_at, {:where=>"(verification_state = 3)", :order=>{:verification_retry_at=>"ASC NULLS FIRST"}, :name=>"index_ci_pipeline_artifacts_failed_verification", :algorithm=>:concurrently})
   -> 0.0028s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pipeline_artifacts, :verification_state, {:where=>"(verification_state = 0 OR verification_state = 3)", :name=>"index_ci_pipeline_artifacts_needs_verification", :algorithm=>:concurrently})
   -> 0.0023s
-- add_index(:ci_pipeline_artifacts, :verification_state, {:where=>"(verification_state = 0 OR verification_state = 3)", :name=>"index_ci_pipeline_artifacts_needs_verification", :algorithm=>:concurrently})
   -> 0.0025s
== 20210325152011 AddVerificationIndexesToCiPipelineArtifacts: migrated (0.0277s)

$ bin/rails db:rollback
== 20210325152011 AddVerificationIndexesToCiPipelineArtifacts: reverting ======
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_pipeline_artifacts)
   -> 0.0042s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- remove_index(:ci_pipeline_artifacts, {:algorithm=>:concurrently, :name=>"index_ci_pipeline_artifacts_verification_state"})
   -> 0.0038s
-- execute("RESET ALL")
   -> 0.0005s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_pipeline_artifacts)
   -> 0.0024s
-- remove_index(:ci_pipeline_artifacts, {:algorithm=>:concurrently, :name=>"index_ci_pipeline_artifacts_pending_verification"})
   -> 0.0017s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_pipeline_artifacts)
   -> 0.0019s
-- remove_index(:ci_pipeline_artifacts, {:algorithm=>:concurrently, :name=>"index_ci_pipeline_artifacts_failed_verification"})
   -> 0.0017s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_pipeline_artifacts)
   -> 0.0021s
-- remove_index(:ci_pipeline_artifacts, {:algorithm=>:concurrently, :name=>"index_ci_pipeline_artifacts_needs_verification"})
   -> 0.0017s
== 20210325152011 AddVerificationIndexesToCiPipelineArtifacts: reverted (0.0232s)

Does this MR meet the acceptance criteria?

Conformity

📋 Does this MR need a changelog?
- I have included a changelog entry.
[-] Documentation (if required)
Code review guidelines
[-] Merge request performance guidelines
Style guides
[-] Database guides
[-] Separation of EE specific content

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

Edited Mar 25, 2021 by Max Orefice

Add geo database changes for pipeline artifact replication