Fix null values being included in some indexes (mastodon#17711)

* Fix null values being included in some indexes * Update lib/mastodon/migration_helpers.rb Co-authored-by: Claire <[email protected]> * Add documentation link to corruption error message Co-authored-by: Claire <[email protected]>
hachyderm · Mar 12, 2022 · 75e33fd · 75e33fd
1 parent bc320d6
commit 75e33fd
Show file tree

Hide file tree

Showing 23 changed files with 354 additions and 37 deletions.
diff --git a/db/migrate/20200620164023_add_fixed_lowercase_index_to_accounts.rb b/db/migrate/20200620164023_add_fixed_lowercase_index_to_accounts.rb
@@ -16,7 +16,7 @@ def up
       add_index :accounts, "lower (username), COALESCE(lower(domain), '')", name: 'index_accounts_on_username_and_domain_lower', unique: true, algorithm: :concurrently
     rescue ActiveRecord::RecordNotUnique
       remove_index :accounts, name: 'index_accounts_on_username_and_domain_lower'
-      raise CorruptionError
+      raise CorruptionError.new('index_accounts_on_username_and_domain_lower')
     end
 
     remove_index :accounts, name: 'old_index_accounts_on_username_and_domain_lower' if index_name_exists?(:accounts, 'old_index_accounts_on_username_and_domain_lower')

diff --git a/db/migrate/20210421121431_add_case_insensitive_btree_index_to_tags.rb b/db/migrate/20210421121431_add_case_insensitive_btree_index_to_tags.rb
@@ -10,7 +10,7 @@ def up
       safety_assured { execute 'CREATE UNIQUE INDEX CONCURRENTLY index_tags_on_name_lower_btree ON tags (lower(name) text_pattern_ops)' }
     rescue ActiveRecord::StatementInvalid => e
       remove_index :tags, name: 'index_tags_on_name_lower_btree'
-      raise CorruptionError if e.is_a?(ActiveRecord::RecordNotUnique)
+      raise CorruptionError.new('index_tags_on_name_lower_btree') if e.is_a?(ActiveRecord::RecordNotUnique)
       raise e
     end
 

diff --git a/db/post_migrate/20220307083603_optimize_null_index_conversations_uri.rb b/db/post_migrate/20220307083603_optimize_null_index_conversations_uri.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexConversationsUri < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :conversations, 'index_conversations_on_uri', :uri, unique: true, where: 'uri IS NOT NULL', opclass: :text_pattern_ops
+  end
+
+  def down
+    update_index :conversations, 'index_conversations_on_uri', :uri, unique: true
+  end
+end
diff --git a/db/post_migrate/20220310060545_optimize_null_index_statuses_in_reply_to_account_id.rb b/db/post_migrate/20220310060545_optimize_null_index_statuses_in_reply_to_account_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexStatusesInReplyToAccountId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :statuses, 'index_statuses_on_in_reply_to_account_id', :in_reply_to_account_id, where: 'in_reply_to_account_id IS NOT NULL'
+  end
+
+  def down
+    update_index :statuses, 'index_statuses_on_in_reply_to_account_id', :in_reply_to_account_id
+  end
+end
diff --git a/db/post_migrate/20220310060556_optimize_null_index_statuses_in_reply_to_id.rb b/db/post_migrate/20220310060556_optimize_null_index_statuses_in_reply_to_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexStatusesInReplyToId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :statuses, 'index_statuses_on_in_reply_to_id', :in_reply_to_id, where: 'in_reply_to_id IS NOT NULL'
+  end
+
+  def down
+    update_index :statuses, 'index_statuses_on_in_reply_to_id', :in_reply_to_id
+  end
+end
diff --git a/db/post_migrate/20220310060614_optimize_null_index_media_attachments_scheduled_status_id.rb b/db/post_migrate/20220310060614_optimize_null_index_media_attachments_scheduled_status_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexMediaAttachmentsScheduledStatusId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :media_attachments, 'index_media_attachments_on_scheduled_status_id', :scheduled_status_id, where: 'scheduled_status_id IS NOT NULL'
+  end
+
+  def down
+    update_index :media_attachments, 'index_media_attachments_on_scheduled_status_id', :scheduled_status_id
+  end
+end
diff --git a/db/post_migrate/20220310060626_optimize_null_index_media_attachments_shortcode.rb b/db/post_migrate/20220310060626_optimize_null_index_media_attachments_shortcode.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexMediaAttachmentsShortcode < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :media_attachments, 'index_media_attachments_on_shortcode', :shortcode, unique: true, where: 'shortcode IS NOT NULL', opclass: :text_pattern_ops
+  end
+
+  def down
+    update_index :media_attachments, 'index_media_attachments_on_shortcode', :shortcode, unique: true
+  end
+end
diff --git a/db/post_migrate/20220310060641_optimize_null_index_users_reset_password_token.rb b/db/post_migrate/20220310060641_optimize_null_index_users_reset_password_token.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexUsersResetPasswordToken < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :users, 'index_users_on_reset_password_token', :reset_password_token, unique: true, where: 'reset_password_token IS NOT NULL', opclass: :text_pattern_ops
+  end
+
+  def down
+    update_index :users, 'index_users_on_reset_password_token', :reset_password_token, unique: true
+  end
+end
diff --git a/db/post_migrate/20220310060653_optimize_null_index_users_created_by_application_id.rb b/db/post_migrate/20220310060653_optimize_null_index_users_created_by_application_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexUsersCreatedByApplicationId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :users, 'index_users_on_created_by_application_id', :created_by_application_id, where: 'created_by_application_id IS NOT NULL'
+  end
+
+  def down
+    update_index :users, 'index_users_on_created_by_application_id', :created_by_application_id
+  end
+end
diff --git a/db/post_migrate/20220310060706_optimize_null_index_statuses_uri.rb b/db/post_migrate/20220310060706_optimize_null_index_statuses_uri.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexStatusesUri < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :statuses, 'index_statuses_on_uri', :uri, unique: true, where: 'uri IS NOT NULL', opclass: :text_pattern_ops
+  end
+
+  def down
+    update_index :statuses, 'index_statuses_on_uri', :uri, unique: true
+  end
+end
diff --git a/db/post_migrate/20220310060722_optimize_null_index_accounts_moved_to_account_id.rb b/db/post_migrate/20220310060722_optimize_null_index_accounts_moved_to_account_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexAccountsMovedToAccountId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :accounts, 'index_accounts_on_moved_to_account_id', :moved_to_account_id, where: 'moved_to_account_id IS NOT NULL'
+  end
+
+  def down
+    update_index :accounts, 'index_accounts_on_moved_to_account_id', :moved_to_account_id
+  end
+end
diff --git a/db/post_migrate/20220310060740_optimize_null_index_oauth_access_tokens_refresh_token.rb b/db/post_migrate/20220310060740_optimize_null_index_oauth_access_tokens_refresh_token.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexOauthAccessTokensRefreshToken < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :oauth_access_tokens, 'index_oauth_access_tokens_on_refresh_token', :refresh_token, unique: true, where: 'refresh_token IS NOT NULL', opclass: :text_pattern_ops
+  end
+
+  def down
+    update_index :oauth_access_tokens, 'index_oauth_access_tokens_on_refresh_token', :refresh_token, unique: true
+  end
+end
diff --git a/db/post_migrate/20220310060750_optimize_null_index_accounts_url.rb b/db/post_migrate/20220310060750_optimize_null_index_accounts_url.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexAccountsURL < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :accounts, 'index_accounts_on_url', :url, where: 'url IS NOT NULL', opclass: :text_pattern_ops
+  end
+
+  def down
+    update_index :accounts, 'index_accounts_on_url', :url
+  end
+end
diff --git a/db/post_migrate/20220310060809_optimize_null_index_oauth_access_tokens_resource_owner_id.rb b/db/post_migrate/20220310060809_optimize_null_index_oauth_access_tokens_resource_owner_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexOauthAccessTokensResourceOwnerId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :oauth_access_tokens, 'index_oauth_access_tokens_on_resource_owner_id', :resource_owner_id, where: 'resource_owner_id IS NOT NULL'
+  end
+
+  def down
+    update_index :oauth_access_tokens, 'index_oauth_access_tokens_on_resource_owner_id', :resource_owner_id
+  end
+end
diff --git a/db/post_migrate/20220310060833_optimize_null_index_announcement_reactions_custom_emoji_id.rb b/db/post_migrate/20220310060833_optimize_null_index_announcement_reactions_custom_emoji_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexAnnouncementReactionsCustomEmojiId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :announcement_reactions, 'index_announcement_reactions_on_custom_emoji_id', :custom_emoji_id, where: 'custom_emoji_id IS NOT NULL'
+  end
+
+  def down
+    update_index :announcement_reactions, 'index_announcement_reactions_on_custom_emoji_id', :custom_emoji_id
+  end
+end
diff --git a/db/post_migrate/20220310060854_optimize_null_index_appeals_approved_by_account_id.rb b/db/post_migrate/20220310060854_optimize_null_index_appeals_approved_by_account_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexAppealsApprovedByAccountId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :appeals, 'index_appeals_on_approved_by_account_id', :approved_by_account_id, where: 'approved_by_account_id IS NOT NULL'
+  end
+
+  def down
+    update_index :appeals, 'index_appeals_on_approved_by_account_id', :approved_by_account_id
+  end
+end
diff --git a/db/post_migrate/20220310060913_optimize_null_index_account_migrations_target_account_id.rb b/db/post_migrate/20220310060913_optimize_null_index_account_migrations_target_account_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexAccountMigrationsTargetAccountId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :account_migrations, 'index_account_migrations_on_target_account_id', :target_account_id, where: 'target_account_id IS NOT NULL'
+  end
+
+  def down
+    update_index :account_migrations, 'index_account_migrations_on_target_account_id', :target_account_id
+  end
+end
diff --git a/db/post_migrate/20220310060926_optimize_null_index_appeals_rejected_by_account_id.rb b/db/post_migrate/20220310060926_optimize_null_index_appeals_rejected_by_account_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexAppealsRejectedByAccountId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :appeals, 'index_appeals_on_rejected_by_account_id', :rejected_by_account_id, where: 'rejected_by_account_id IS NOT NULL'
+  end
+
+  def down
+    update_index :appeals, 'index_appeals_on_rejected_by_account_id', :rejected_by_account_id
+  end
+end
diff --git a/db/post_migrate/20220310060939_optimize_null_index_list_accounts_follow_id.rb b/db/post_migrate/20220310060939_optimize_null_index_list_accounts_follow_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexListAccountsFollowId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :list_accounts, 'index_list_accounts_on_follow_id', :follow_id, where: 'follow_id IS NOT NULL'
+  end
+
+  def down
+    update_index :list_accounts, 'index_list_accounts_on_follow_id', :follow_id
+  end
+end
diff --git a/db/post_migrate/20220310060959_optimize_null_index_web_push_subscriptions_access_token_id.rb b/db/post_migrate/20220310060959_optimize_null_index_web_push_subscriptions_access_token_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+require Rails.root.join('lib', 'mastodon', 'migration_helpers')
+
+class OptimizeNullIndexWebPushSubscriptionsAccessTokenId < ActiveRecord::Migration[5.2]
+  include Mastodon::MigrationHelpers
+
+  disable_ddl_transaction!
+
+  def up
+    update_index :web_push_subscriptions, 'index_web_push_subscriptions_on_access_token_id', :access_token_id, where: 'access_token_id IS NOT NULL'
+  end
+
+  def down
+    update_index :web_push_subscriptions, 'index_web_push_subscriptions_on_access_token_id', :access_token_id
+  end
+end