diff options
| -rw-r--r-- | db/migrate/20191118084127_migrate_to_new_search_impl.rb | 28 | ||||
| -rw-r--r-- | dist/search.sql | 21 |
2 files changed, 30 insertions, 19 deletions
diff --git a/db/migrate/20191118084127_migrate_to_new_search_impl.rb b/db/migrate/20191118084127_migrate_to_new_search_impl.rb new file mode 100644 index 000000000..b068443ca --- /dev/null +++ b/db/migrate/20191118084127_migrate_to_new_search_impl.rb @@ -0,0 +1,28 @@ +class MigrateToNewSearchImpl < ActiveRecord::Migration[5.2] + disable_ddl_transaction! + + def up + safety_assured { + execute 'DROP TRIGGER IF EXISTS tsvectorinsert ON statuses' + execute 'DROP TRIGGER IF EXISTS tsvectorupdate ON statuses' + execute 'DROP FUNCTION IF EXISTS tsv_update_trigger' + execute 'DROP INDEX IF EXISTS tsv_idx' + execute 'ALTER TABLE statuses DROP COLUMN IF EXISTS tsv' + execute 'DROP INDEX IF EXISTS index_statuses_on_text_trgm' + execute 'DROP INDEX IF EXISTS index_statuses_on_spoiler_text_trgm' + execute <<-SQL.squish + CREATE OR REPLACE FUNCTION public.f_normalize(text) + RETURNS text LANGUAGE sql PARALLEL SAFE STRICT AS + $func$ + SELECT REGEXP_REPLACE(LOWER(unaccent($1)), '"(.*)"', '\\\\y\\1\\\\y') + $func$ + SQL + execute 'CREATE INDEX CONCURRENTLY IF NOT EXISTS index_statuses_on_normalized_text_trgm ON statuses USING GIN (normalized_text gin_trgm_ops)' + } + end + + def down + #raise ActiveRecord::IrreversibleMigration + true + end +end diff --git a/dist/search.sql b/dist/search.sql index e68b3c9b3..f80ca049c 100644 --- a/dist/search.sql +++ b/dist/search.sql @@ -1,22 +1,5 @@ --- Run this section on Mastodon DB as Postgres superuser. -- +-- Before running db:migrate, run this on Mastodon DB as Postgres superuser. -- -- sudo -sHu postgres -- psql mastodon_production CREATE EXTENSION pg_trgm; - --- Run this section on Mastodon DB as Mastodon user. -- --- sudo -sHu mastodon -- psql mastodon_production - --- Drop old FTS implementation -- -DROP TRIGGER IF EXISTS tsvectorinsert ON statuses; -DROP TRIGGER IF EXISTS tsvectorupdate ON statuses; -DROP FUNCTION IF EXISTS tsv_update_trigger; -DROP INDEX IF EXISTS tsv_idx; -ALTER TABLE statuses DROP COLUMN IF EXISTS tsv; -DROP INDEX IF EXISTS index_statuses_on_text_trgm; -DROP INDEX IF EXISTS index_statuses_on_spoiler_text_trgm; - --- Create new trigram indexes -- -CREATE INDEX CONCURRENTLY IF NOT EXISTS index_statuses_on_normalized_text_trgm ON statuses USING GIN (normalized_text gin_trgm_ops); - --- Compact tables --- -VACUUM ANALYZE; +CREATE EXTENSION unaccent; |