From 01c45ddc9ead715131b3c583caa14fcf20845354 Mon Sep 17 00:00:00 2001 From: lain Date: Sat, 11 May 2019 11:26:46 +0200 Subject: Search: Use RUM index. --- ...20190510135645_add_fts_index_to_objects_two.exs | 33 ++++++++++++++++++++++ 1 file changed, 33 insertions(+) create mode 100644 priv/repo/migrations/20190510135645_add_fts_index_to_objects_two.exs (limited to 'priv') diff --git a/priv/repo/migrations/20190510135645_add_fts_index_to_objects_two.exs b/priv/repo/migrations/20190510135645_add_fts_index_to_objects_two.exs new file mode 100644 index 000000000..14b964847 --- /dev/null +++ b/priv/repo/migrations/20190510135645_add_fts_index_to_objects_two.exs @@ -0,0 +1,33 @@ +defmodule Pleroma.Repo.Migrations.AddFtsIndexToObjectsTwo do + use Ecto.Migration + + def up do + drop_if_exists index(:objects, ["(to_tsvector('english', data->>'content'))"], using: :gin, name: :objects_fts) + alter table(:objects) do + add(:fts_content, :tsvector) + end + + execute("CREATE FUNCTION objects_fts_update() RETURNS trigger AS $$ + begin + new.fts_content := to_tsvector('english', new.data->>'content'); + return new; + end + $$ LANGUAGE plpgsql") + execute("create index objects_fts on objects using RUM (fts_content rum_tsvector_addon_ops, inserted_at) with (attach = 'inserted_at', to = 'fts_content');") + + execute("CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON objects + FOR EACH ROW EXECUTE PROCEDURE objects_fts_update()") + + execute("UPDATE objects SET updated_at = NOW()") + end + + def down do + execute "drop index objects_fts" + execute "drop trigger tsvectorupdate on objects" + execute "drop function objects_fts_update()" + alter table(:objects) do + remove(:fts_content, :tsvector) + end + create index(:objects, ["(to_tsvector('english', data->>'content'))"], using: :gin, name: :objects_fts) + end +end -- cgit v1.2.3 From f1e67bdc312ba16a37916024244d6cb9d4417c9e Mon Sep 17 00:00:00 2001 From: lain Date: Wed, 15 May 2019 15:28:01 +0200 Subject: Search: Add optional rum indexing / searching. --- ...20190510135645_add_fts_index_to_objects_two.exs | 33 -------------------- ...20190510135645_add_fts_index_to_objects_two.exs | 36 ++++++++++++++++++++++ 2 files changed, 36 insertions(+), 33 deletions(-) delete mode 100644 priv/repo/migrations/20190510135645_add_fts_index_to_objects_two.exs create mode 100644 priv/repo/optional_migrations/rum_indexing/20190510135645_add_fts_index_to_objects_two.exs (limited to 'priv') diff --git a/priv/repo/migrations/20190510135645_add_fts_index_to_objects_two.exs b/priv/repo/migrations/20190510135645_add_fts_index_to_objects_two.exs deleted file mode 100644 index 14b964847..000000000 --- a/priv/repo/migrations/20190510135645_add_fts_index_to_objects_two.exs +++ /dev/null @@ -1,33 +0,0 @@ -defmodule Pleroma.Repo.Migrations.AddFtsIndexToObjectsTwo do - use Ecto.Migration - - def up do - drop_if_exists index(:objects, ["(to_tsvector('english', data->>'content'))"], using: :gin, name: :objects_fts) - alter table(:objects) do - add(:fts_content, :tsvector) - end - - execute("CREATE FUNCTION objects_fts_update() RETURNS trigger AS $$ - begin - new.fts_content := to_tsvector('english', new.data->>'content'); - return new; - end - $$ LANGUAGE plpgsql") - execute("create index objects_fts on objects using RUM (fts_content rum_tsvector_addon_ops, inserted_at) with (attach = 'inserted_at', to = 'fts_content');") - - execute("CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON objects - FOR EACH ROW EXECUTE PROCEDURE objects_fts_update()") - - execute("UPDATE objects SET updated_at = NOW()") - end - - def down do - execute "drop index objects_fts" - execute "drop trigger tsvectorupdate on objects" - execute "drop function objects_fts_update()" - alter table(:objects) do - remove(:fts_content, :tsvector) - end - create index(:objects, ["(to_tsvector('english', data->>'content'))"], using: :gin, name: :objects_fts) - end -end diff --git a/priv/repo/optional_migrations/rum_indexing/20190510135645_add_fts_index_to_objects_two.exs b/priv/repo/optional_migrations/rum_indexing/20190510135645_add_fts_index_to_objects_two.exs new file mode 100644 index 000000000..09e6cbfb1 --- /dev/null +++ b/priv/repo/optional_migrations/rum_indexing/20190510135645_add_fts_index_to_objects_two.exs @@ -0,0 +1,36 @@ +defmodule Pleroma.Repo.Migrations.AddFtsIndexToObjectsTwo do + use Ecto.Migration + + def up do + execute("create extension if not exists rum") + drop_if_exists index(:objects, ["(to_tsvector('english', data->>'content'))"], using: :gin, name: :objects_fts) + alter table(:objects) do + add(:fts_content, :tsvector) + end + + execute("CREATE FUNCTION objects_fts_update() RETURNS trigger AS $$ + begin + new.fts_content := to_tsvector('english', new.data->>'content'); + return new; + end + $$ LANGUAGE plpgsql") + execute("create index objects_fts on objects using RUM (fts_content rum_tsvector_addon_ops, inserted_at) with (attach = 'inserted_at', to = 'fts_content');") + + execute("CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON objects + FOR EACH ROW EXECUTE PROCEDURE objects_fts_update()") + + execute("UPDATE objects SET updated_at = NOW()") + execute("vacuum analyze") + end + + def down do + execute "drop index objects_fts" + execute "drop trigger tsvectorupdate on objects" + execute "drop function objects_fts_update()" + alter table(:objects) do + remove(:fts_content, :tsvector) + end + create index(:objects, ["(to_tsvector('english', data->>'content'))"], using: :gin, name: :objects_fts) + execute("vacuum analyze") + end +end -- cgit v1.2.3 From 022e6e4b44229be70f0ec6720a66610a0e2a403a Mon Sep 17 00:00:00 2001 From: lain Date: Fri, 17 May 2019 14:59:01 +0200 Subject: RUM: Remove vacuum analyze from migration Can't be run in a trnasaction. --- .../rum_indexing/20190510135645_add_fts_index_to_objects_two.exs | 2 -- 1 file changed, 2 deletions(-) (limited to 'priv') diff --git a/priv/repo/optional_migrations/rum_indexing/20190510135645_add_fts_index_to_objects_two.exs b/priv/repo/optional_migrations/rum_indexing/20190510135645_add_fts_index_to_objects_two.exs index 09e6cbfb1..b6a24441a 100644 --- a/priv/repo/optional_migrations/rum_indexing/20190510135645_add_fts_index_to_objects_two.exs +++ b/priv/repo/optional_migrations/rum_indexing/20190510135645_add_fts_index_to_objects_two.exs @@ -20,7 +20,6 @@ defmodule Pleroma.Repo.Migrations.AddFtsIndexToObjectsTwo do FOR EACH ROW EXECUTE PROCEDURE objects_fts_update()") execute("UPDATE objects SET updated_at = NOW()") - execute("vacuum analyze") end def down do @@ -31,6 +30,5 @@ defmodule Pleroma.Repo.Migrations.AddFtsIndexToObjectsTwo do remove(:fts_content, :tsvector) end create index(:objects, ["(to_tsvector('english', data->>'content'))"], using: :gin, name: :objects_fts) - execute("vacuum analyze") end end -- cgit v1.2.3