From 7640dc4e0c5787d7e37fa8b8d86c7f1b34fa99c9 Mon Sep 17 00:00:00 2001 From: Marius Bobin Date: Tue, 7 Feb 2023 11:51:57 +0200 Subject: [PATCH] Add async foreign key validation model Changelog: added --- ...postgres_async_foreign_key_validations.yml | 11 ++++++ ..._postgres_async_foreign_key_validations.rb | 17 ++++++++++ db/schema_migrations/20230206143851 | 1 + db/structure.sql | 29 ++++++++++++++++ .../postgres_async_foreign_key_validation.rb | 20 +++++++++++ .../postgres_async_foreign_key_validation.rb | 9 +++++ ...tgres_async_foreign_key_validation_spec.rb | 34 +++++++++++++++++++ 7 files changed, 121 insertions(+) create mode 100644 db/docs/postgres_async_foreign_key_validations.yml create mode 100644 db/migrate/20230206143851_create_postgres_async_foreign_key_validations.rb create mode 100644 db/schema_migrations/20230206143851 create mode 100644 lib/gitlab/database/async_foreign_keys/postgres_async_foreign_key_validation.rb create mode 100644 spec/factories/gitlab/database/async_foreign_keys/postgres_async_foreign_key_validation.rb create mode 100644 spec/lib/gitlab/database/async_foreign_keys/postgres_async_foreign_key_validation_spec.rb diff --git a/db/docs/postgres_async_foreign_key_validations.yml b/db/docs/postgres_async_foreign_key_validations.yml new file mode 100644 index 00000000000000..0b9608a3acec35 --- /dev/null +++ b/db/docs/postgres_async_foreign_key_validations.yml @@ -0,0 +1,11 @@ +--- +table_name: postgres_async_foreign_key_validations +classes: +- Gitlab::Database::AsyncForeignKeys::PostgresAsyncForeignKeyValidation +feature_categories: +- database +description: >- + Contains a queue-like structure for database foreign key validations scheduled to be executed asynchronously. +introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/111293 +milestone: '15.9' +gitlab_schema: gitlab_shared diff --git a/db/migrate/20230206143851_create_postgres_async_foreign_key_validations.rb b/db/migrate/20230206143851_create_postgres_async_foreign_key_validations.rb new file mode 100644 index 00000000000000..b9c32f83411e65 --- /dev/null +++ b/db/migrate/20230206143851_create_postgres_async_foreign_key_validations.rb @@ -0,0 +1,17 @@ +# frozen_string_literal: true + +class CreatePostgresAsyncForeignKeyValidations < Gitlab::Database::Migration[2.1] + def change + create_table :postgres_async_foreign_key_validations, if_not_exists: true do |t| + t.timestamps_with_timezone null: false + + t.text :name, null: false, limit: 63 + t.text :table_name, null: false, limit: 63 + t.text :last_error, limit: 10_000 + + t.integer :attempts, null: false, default: 0 + + t.index :name, unique: true + end + end +end diff --git a/db/schema_migrations/20230206143851 b/db/schema_migrations/20230206143851 new file mode 100644 index 00000000000000..52c7753a7ac08a --- /dev/null +++ b/db/schema_migrations/20230206143851 @@ -0,0 +1 @@ +08ba891bbf1342addac1197cbbb13c539ba865928082c72b469c8fae26154728 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index c3524f7c754693..7bdd94442afd5f 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -19811,6 +19811,28 @@ CREATE SEQUENCE pool_repositories_id_seq ALTER SEQUENCE pool_repositories_id_seq OWNED BY pool_repositories.id; +CREATE TABLE postgres_async_foreign_key_validations ( + id bigint NOT NULL, + created_at timestamp with time zone NOT NULL, + updated_at timestamp with time zone NOT NULL, + name text NOT NULL, + table_name text NOT NULL, + last_error text, + attempts integer DEFAULT 0 NOT NULL, + CONSTRAINT check_536a40afbf CHECK ((char_length(last_error) <= 10000)), + CONSTRAINT check_74fb7c8e57 CHECK ((char_length(name) <= 63)), + CONSTRAINT check_cd435d6301 CHECK ((char_length(table_name) <= 63)) +); + +CREATE SEQUENCE postgres_async_foreign_key_validations_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + +ALTER SEQUENCE postgres_async_foreign_key_validations_id_seq OWNED BY postgres_async_foreign_key_validations.id; + CREATE TABLE postgres_async_indexes ( id bigint NOT NULL, created_at timestamp with time zone NOT NULL, @@ -24723,6 +24745,8 @@ ALTER TABLE ONLY pm_packages ALTER COLUMN id SET DEFAULT nextval('pm_packages_id ALTER TABLE ONLY pool_repositories ALTER COLUMN id SET DEFAULT nextval('pool_repositories_id_seq'::regclass); +ALTER TABLE ONLY postgres_async_foreign_key_validations ALTER COLUMN id SET DEFAULT nextval('postgres_async_foreign_key_validations_id_seq'::regclass); + ALTER TABLE ONLY postgres_async_indexes ALTER COLUMN id SET DEFAULT nextval('postgres_async_indexes_id_seq'::regclass); ALTER TABLE ONLY postgres_reindex_actions ALTER COLUMN id SET DEFAULT nextval('postgres_reindex_actions_id_seq'::regclass); @@ -26955,6 +26979,9 @@ ALTER TABLE ONLY pm_packages ALTER TABLE ONLY pool_repositories ADD CONSTRAINT pool_repositories_pkey PRIMARY KEY (id); +ALTER TABLE ONLY postgres_async_foreign_key_validations + ADD CONSTRAINT postgres_async_foreign_key_validations_pkey PRIMARY KEY (id); + ALTER TABLE ONLY postgres_async_indexes ADD CONSTRAINT postgres_async_indexes_pkey PRIMARY KEY (id); @@ -30907,6 +30934,8 @@ CREATE INDEX index_pool_repositories_on_shard_id ON pool_repositories USING btre CREATE UNIQUE INDEX index_pool_repositories_on_source_project_id_and_shard_id ON pool_repositories USING btree (source_project_id, shard_id); +CREATE UNIQUE INDEX index_postgres_async_foreign_key_validations_on_name ON postgres_async_foreign_key_validations USING btree (name); + CREATE UNIQUE INDEX index_postgres_async_indexes_on_name ON postgres_async_indexes USING btree (name); CREATE INDEX index_postgres_reindex_actions_on_index_identifier ON postgres_reindex_actions USING btree (index_identifier); diff --git a/lib/gitlab/database/async_foreign_keys/postgres_async_foreign_key_validation.rb b/lib/gitlab/database/async_foreign_keys/postgres_async_foreign_key_validation.rb new file mode 100644 index 00000000000000..76403f9a643b6a --- /dev/null +++ b/lib/gitlab/database/async_foreign_keys/postgres_async_foreign_key_validation.rb @@ -0,0 +1,20 @@ +# frozen_string_literal: true + +module Gitlab + module Database + module AsyncForeignKeys + class PostgresAsyncForeignKeyValidation < SharedModel + self.table_name = 'postgres_async_foreign_key_validations' + + MAX_IDENTIFIER_LENGTH = Gitlab::Database::MigrationHelpers::MAX_IDENTIFIER_NAME_LENGTH + MAX_LAST_ERROR_LENGTH = 10_000 + + validates :name, presence: true, uniqueness: true, length: { maximum: MAX_IDENTIFIER_LENGTH } + validates :table_name, presence: true, length: { maximum: MAX_IDENTIFIER_LENGTH } + validates :last_error, length: { maximum: MAX_LAST_ERROR_LENGTH } + + scope :ordered, -> { order(attempts: :asc, id: :asc) } + end + end + end +end diff --git a/spec/factories/gitlab/database/async_foreign_keys/postgres_async_foreign_key_validation.rb b/spec/factories/gitlab/database/async_foreign_keys/postgres_async_foreign_key_validation.rb new file mode 100644 index 00000000000000..a61b5cde7a0336 --- /dev/null +++ b/spec/factories/gitlab/database/async_foreign_keys/postgres_async_foreign_key_validation.rb @@ -0,0 +1,9 @@ +# frozen_string_literal: true + +FactoryBot.define do + factory :postgres_async_foreign_key_validation, + class: 'Gitlab::Database::AsyncForeignKeys::PostgresAsyncForeignKeyValidation' do + sequence(:name) { |n| "fk_users_id_#{n}" } + table_name { "users" } + end +end diff --git a/spec/lib/gitlab/database/async_foreign_keys/postgres_async_foreign_key_validation_spec.rb b/spec/lib/gitlab/database/async_foreign_keys/postgres_async_foreign_key_validation_spec.rb new file mode 100644 index 00000000000000..23313387400dfc --- /dev/null +++ b/spec/lib/gitlab/database/async_foreign_keys/postgres_async_foreign_key_validation_spec.rb @@ -0,0 +1,34 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Database::AsyncForeignKeys::PostgresAsyncForeignKeyValidation, type: :model, + feature_category: :database do + it { is_expected.to be_a Gitlab::Database::SharedModel } + + describe 'validations' do + let_it_be(:fk_validation) { create(:postgres_async_foreign_key_validation) } + let(:identifier_limit) { described_class::MAX_IDENTIFIER_LENGTH } + let(:last_error_limit) { described_class::MAX_LAST_ERROR_LENGTH } + + subject { fk_validation } + + it { is_expected.to validate_presence_of(:name) } + it { is_expected.to validate_uniqueness_of(:name) } + it { is_expected.to validate_length_of(:name).is_at_most(identifier_limit) } + it { is_expected.to validate_presence_of(:table_name) } + it { is_expected.to validate_length_of(:table_name).is_at_most(identifier_limit) } + it { is_expected.to validate_length_of(:last_error).is_at_most(last_error_limit) } + end + + describe 'scopes' do + let!(:failed_validation) { create(:postgres_async_foreign_key_validation, attempts: 1) } + let!(:new_validation) { create(:postgres_async_foreign_key_validation) } + + describe '.ordered' do + subject { described_class.ordered } + + it { is_expected.to eq([new_validation, failed_validation]) } + end + end +end -- GitLab