Page MenuHomePhorge
Files F85628367

No OneTemporary
Actions

Size
19 KB
Referenced Files
None
Subscribers
None

View Options

diff --git a/docs/administration/CLI_tasks/database.md b/docs/administration/CLI_tasks/database.md
index 6dca83167..c53c49921 100644
--- a/docs/administration/CLI_tasks/database.md
+++ b/docs/administration/CLI_tasks/database.md
@@ -1,143 +1,161 @@
# Database maintenance tasks
{! backend/administration/CLI_tasks/general_cli_task_info.include !}
!!! danger
These mix tasks can take a long time to complete. Many of them were written to address specific database issues that happened because of bugs in migrations or other specific scenarios. Do not run these tasks "just in case" if everything is fine your instance.
## Replace embedded objects with their references
Replaces embedded objects with references to them in the `objects` table. Only needs to be ran once if the instance was created before Pleroma 1.0.5. The reason why this is not a migration is because it could significantly increase the database size after being ran, however after this `VACUUM FULL` will be able to reclaim about 20% (really depends on what is in the database, your mileage may vary) of the db size before the migration.
=== "OTP"
```sh
./bin/pleroma_ctl database remove_embedded_objects [option ...]
```
=== "From Source"
```sh
mix pleroma.database remove_embedded_objects [option ...]
```
### Options
- `--vacuum` - run `VACUUM FULL` after the embedded objects are replaced with their references
## Prune old remote posts from the database
This will prune remote posts older than 90 days (configurable with [`config :pleroma, :instance, remote_post_retention_days`](../../configuration/cheatsheet.md#instance)) from the database, they will be refetched from source when accessed.
!!! danger
The disk space will only be reclaimed after `VACUUM FULL`. You may run out of disk space during the execution of the task or vacuuming if you don't have about 1/3rds of the database size free.
=== "OTP"
```sh
./bin/pleroma_ctl database prune_objects [option ...]
```
=== "From Source"
```sh
mix pleroma.database prune_objects [option ...]
```
### Options
- `--vacuum` - run `VACUUM FULL` after the objects are pruned
## Create a conversation for all existing DMs
Can be safely re-run
=== "OTP"
```sh
./bin/pleroma_ctl database bump_all_conversations
```
=== "From Source"
```sh
mix pleroma.database bump_all_conversations
```
## Remove duplicated items from following and update followers count for all users
=== "OTP"
```sh
./bin/pleroma_ctl database update_users_following_followers_counts
```
=== "From Source"
```sh
mix pleroma.database update_users_following_followers_counts
```
## Fix the pre-existing "likes" collections for all objects
=== "OTP"
```sh
./bin/pleroma_ctl database fix_likes_collections
```
=== "From Source"
```sh
mix pleroma.database fix_likes_collections
```
## Vacuum the database
### Analyze
Running an `analyze` vacuum job can improve performance by updating statistics used by the query planner. **It is safe to cancel this.**
=== "OTP"
```sh
./bin/pleroma_ctl database vacuum analyze
```
=== "From Source"
```sh
mix pleroma.database vacuum analyze
```
### Full
Running a `full` vacuum job rebuilds your entire database by reading all of the data and rewriting it into smaller
and more compact files with an optimized layout. This process will take a long time and use additional disk space as
it builds the files side-by-side the existing database files. It can make your database faster and use less disk space,
but should only be run if necessary. **It is safe to cancel this.**
=== "OTP"
```sh
./bin/pleroma_ctl database vacuum full
```
=== "From Source"
```sh
mix pleroma.database vacuum full
```
## Add expiration to all local statuses
=== "OTP"
```sh
./bin/pleroma_ctl database ensure_expiration
```
=== "From Source"
```sh
mix pleroma.database ensure_expiration
```
+
+## Change Text Search Configuration
+
+Change `default_text_search_config` for database and (if necessary) text_search_config used in index, then rebuild index (it may take time).
+
+=== "OTP"
+
+ ```sh
+ ./bin/pleroma_ctl database set_text_search_config english
+ ```
+
+=== "From Source"
+
+ ```sh
+ mix pleroma.database set_text_search_config english
+ ```
+
+See [PostgreSQL documentation](https://www.postgresql.org/docs/current/textsearch-configuration.html) and `docs/configuration/howto_search_cjk.md` for more detail.
diff --git a/docs/configuration/howto_search_cjk.md b/docs/configuration/howto_search_cjk.md
new file mode 100644
index 000000000..d3ce28077
--- /dev/null
+++ b/docs/configuration/howto_search_cjk.md
@@ -0,0 +1,42 @@
+# How to enable text search for Chinese, Japanese and Korean
+
+Pleroma's full text search feature is powered by PostgreSQL's native [text search](https://www.postgresql.org/docs/current/textsearch.html), it works well out of box for most of languages, but needs extra configurations for some asian languages like Chinese, Japanese and Korean (CJK).
+
+
+## Setup and test the new search config
+
+In most cases, you would need an extension installed to support parsing CJK text. Here are a few extension you may choose from, or you are more than welcome to share additional ones you found working for you with the rest of Pleroma community.
+
+ * [a generic n-gram parser](https://github.com/huangjimmy/pg_cjk_parser) supports Simplifed/Traditional Chinese, Japanese, and Korean
+ * [a Korean parser](https://github.com/i0seph/textsearch_ko) based on mecab
+ * [a Japanese parser](https://www.amris.co.jp/tsja/index.html) based on mecab
+ * [zhparser](https://github.com/amutu/zhparser/) is a PostgreSQL extension base on the Simple Chinese Word Segmentation(SCWS)
+ * [another Chinese parser](https://github.com/jaiminpan/pg_jieba) based on Jieba Chinese Word Segmentation
+
+Once you have the new search config , make sure you test it with the `pleroma` user in PostgreSQL (change `YOUR.CONFIG` to your real configuration name)
+```
+SELECT ts_debug('YOUR.CONFIG', '安装和配置Nginx, ElixirとErlangをインストールします');
+```
+Check output of the query, and see if it matches your expectation.
+
+
+## Update text search config and index in database
+
+=== "OTP"
+
+ ```sh
+ ./bin/pleroma_ctl database set_text_search_config YOUR.CONFIG
+ ```
+
+=== "From Source"
+
+ ```sh
+ mix pleroma.database set_text_search_config YOUR.CONFIG
+ ```
+
+Note: index update may take a while.
+
+## Restart database connection
+Since some changes above will only apply with a new database connection, you will have to restart either Pleroma or PostgreSQL process, or use `pg_terminate_backend` SQL command without restarting either.
+
+Now the search results of statuses should be much more friendly for your language of choice, the results for searching users and tags were not changed, as the default parsing/matching should work for most cases.
diff --git a/lib/mix/tasks/pleroma/database.ex b/lib/mix/tasks/pleroma/database.ex
index 6261910f0..2403ed581 100644
--- a/lib/mix/tasks/pleroma/database.ex
+++ b/lib/mix/tasks/pleroma/database.ex
@@ -1,170 +1,217 @@
# Pleroma: A lightweight social networking server
# Copyright © 2017-2021 Pleroma Authors <https://pleroma.social/>
# SPDX-License-Identifier: AGPL-3.0-only
defmodule Mix.Tasks.Pleroma.Database do
alias Pleroma.Conversation
alias Pleroma.Maintenance
alias Pleroma.Object
alias Pleroma.Repo
alias Pleroma.User
require Logger
require Pleroma.Constants
import Ecto.Query
import Mix.Pleroma
use Mix.Task
@shortdoc "A collection of database related tasks"
@moduledoc File.read!("docs/administration/CLI_tasks/database.md")
def run(["remove_embedded_objects" | args]) do
{options, [], []} =
OptionParser.parse(
args,
strict: [
vacuum: :boolean
]
)
start_pleroma()
Logger.info("Removing embedded objects")
Repo.query!(
"update activities set data = safe_jsonb_set(data, '{object}'::text[], data->'object'->'id') where data->'object'->>'id' is not null;",
[],
timeout: :infinity
)
if Keyword.get(options, :vacuum) do
Maintenance.vacuum("full")
end
end
def run(["bump_all_conversations"]) do
start_pleroma()
Conversation.bump_for_all_activities()
end
def run(["update_users_following_followers_counts"]) do
start_pleroma()
Repo.transaction(
fn ->
from(u in User, select: u)
|> Repo.stream()
|> Stream.each(&User.update_follower_count/1)
|> Stream.run()
end,
timeout: :infinity
)
end
def run(["prune_objects" | args]) do
{options, [], []} =
OptionParser.parse(
args,
strict: [
vacuum: :boolean
]
)
start_pleroma()
deadline = Pleroma.Config.get([:instance, :remote_post_retention_days])
Logger.info("Pruning objects older than #{deadline} days")
time_deadline =
NaiveDateTime.utc_now()
|> NaiveDateTime.add(-(deadline * 86_400))
from(o in Object,
where:
fragment(
"?->'to' \\? ? OR ?->'cc' \\? ?",
o.data,
^Pleroma.Constants.as_public(),
o.data,
^Pleroma.Constants.as_public()
),
where: o.inserted_at < ^time_deadline,
where:
fragment("split_part(?->>'actor', '/', 3) != ?", o.data, ^Pleroma.Web.Endpoint.host())
)
|> Repo.delete_all(timeout: :infinity)
if Keyword.get(options, :vacuum) do
Maintenance.vacuum("full")
end
end
def run(["fix_likes_collections"]) do
start_pleroma()
from(object in Object,
where: fragment("(?)->>'likes' is not null", object.data),
select: %{id: object.id, likes: fragment("(?)->>'likes'", object.data)}
)
|> Pleroma.Repo.chunk_stream(100, :batches)
|> Stream.each(fn objects ->
ids =
objects
|> Enum.filter(fn object -> object.likes |> Jason.decode!() |> is_map() end)
|> Enum.map(& &1.id)
Object
|> where([object], object.id in ^ids)
|> update([object],
set: [
data:
fragment(
"safe_jsonb_set(?, '{likes}', '[]'::jsonb, true)",
object.data
)
]
)
|> Repo.update_all([], timeout: :infinity)
end)
|> Stream.run()
end
def run(["vacuum", args]) do
start_pleroma()
Maintenance.vacuum(args)
end
def run(["ensure_expiration"]) do
start_pleroma()
days = Pleroma.Config.get([:mrf_activity_expiration, :days], 365)
Pleroma.Activity
|> join(:inner, [a], o in Object,
on:
fragment(
"(?->>'id') = COALESCE((?)->'object'->> 'id', (?)->>'object')",
o.data,
a.data,
a.data
)
)
|> where(local: true)
|> where([a], fragment("(? ->> 'type'::text) = 'Create'", a.data))
|> where([_a, o], fragment("?->>'type' = 'Note'", o.data))
|> Pleroma.Repo.chunk_stream(100, :batches)
|> Stream.each(fn activities ->
Enum.each(activities, fn activity ->
expires_at =
activity.inserted_at
|> DateTime.from_naive!("Etc/UTC")
|> Timex.shift(days: days)
Pleroma.Workers.PurgeExpiredActivity.enqueue(%{
activity_id: activity.id,
expires_at: expires_at
})
end)
end)
|> Stream.run()
end
+
+ def run(["set_text_search_config", tsconfig]) do
+ start_pleroma()
+ %{rows: [[tsc]]} = Ecto.Adapters.SQL.query!(Pleroma.Repo, "SHOW default_text_search_config;")
+ shell_info("Current default_text_search_config: #{tsc}")
+
+ %{rows: [[db]]} = Ecto.Adapters.SQL.query!(Pleroma.Repo, "SELECT current_database();")
+ shell_info("Update default_text_search_config: #{tsconfig}")
+
+ %{messages: msg} =
+ Ecto.Adapters.SQL.query!(
+ Pleroma.Repo,
+ "ALTER DATABASE #{db} SET default_text_search_config = '#{tsconfig}';"
+ )
+
+ # non-exist config will not raise excpetion but only give >0 messages
+ if length(msg) > 0 do
+ shell_info("Error: #{inspect(msg, pretty: true)}")
+ else
+ rum_enabled = Pleroma.Config.get([:database, :rum_enabled])
+ shell_info("Recreate index, RUM: #{rum_enabled}")
+
+ # Note SQL below needs to be kept up-to-date with latest GIN or RUM index definition in future
+ if rum_enabled do
+ Ecto.Adapters.SQL.query!(
+ Pleroma.Repo,
+ "CREATE OR REPLACE FUNCTION objects_fts_update() RETURNS trigger AS $$ BEGIN
+ new.fts_content := to_tsvector(new.data->>'content');
+ RETURN new;
+ END
+ $$ LANGUAGE plpgsql"
+ )
+
+ shell_info("Refresh RUM index")
+ Ecto.Adapters.SQL.query!(Pleroma.Repo, "UPDATE objects SET updated_at = NOW();")
+ else
+ Ecto.Adapters.SQL.query!(Pleroma.Repo, "DROP INDEX IF EXISTS objects_fts;")
+
+ Ecto.Adapters.SQL.query!(
+ Pleroma.Repo,
+ "CREATE INDEX objects_fts ON objects USING gin(to_tsvector('#{tsconfig}', data->>'content')); "
+ )
+ end
+
+ shell_info('Done.')
+ end
+ end
end
diff --git a/lib/pleroma/activity/search.ex b/lib/pleroma/activity/search.ex
index 52e7c048d..ed898ba4f 100644
--- a/lib/pleroma/activity/search.ex
+++ b/lib/pleroma/activity/search.ex
@@ -1,132 +1,132 @@
# Pleroma: A lightweight social networking server
# Copyright © 2017-2021 Pleroma Authors <https://pleroma.social/>
# SPDX-License-Identifier: AGPL-3.0-only
defmodule Pleroma.Activity.Search do
alias Pleroma.Activity
alias Pleroma.Object.Fetcher
alias Pleroma.Pagination
alias Pleroma.User
alias Pleroma.Web.ActivityPub.Visibility
require Pleroma.Constants
import Ecto.Query
def search(user, search_query, options \\ []) do
index_type = if Pleroma.Config.get([:database, :rum_enabled]), do: :rum, else: :gin
limit = Enum.min([Keyword.get(options, :limit), 40])
offset = Keyword.get(options, :offset, 0)
author = Keyword.get(options, :author)
search_function =
if :persistent_term.get({Pleroma.Repo, :postgres_version}) >= 11 do
:websearch
else
:plain
end
Activity
|> Activity.with_preloaded_object()
|> Activity.restrict_deactivated_users()
|> restrict_public()
|> query_with(index_type, search_query, search_function)
|> maybe_restrict_local(user)
|> maybe_restrict_author(author)
|> maybe_restrict_blocked(user)
|> Pagination.fetch_paginated(
%{"offset" => offset, "limit" => limit, "skip_order" => index_type == :rum},
:offset
)
|> maybe_fetch(user, search_query)
end
def maybe_restrict_author(query, %User{} = author) do
Activity.Queries.by_author(query, author)
end
def maybe_restrict_author(query, _), do: query
def maybe_restrict_blocked(query, %User{} = user) do
Activity.Queries.exclude_authors(query, User.blocked_users_ap_ids(user))
end
def maybe_restrict_blocked(query, _), do: query
defp restrict_public(q) do
from([a, o] in q,
where: fragment("?->>'type' = 'Create'", a.data),
where: ^Pleroma.Constants.as_public() in a.recipients
)
end
defp query_with(q, :gin, search_query, :plain) do
from([a, o] in q,
where:
fragment(
- "to_tsvector('english', ?->>'content') @@ plainto_tsquery('english', ?)",
+ "to_tsvector(?->>'content') @@ plainto_tsquery(?)",
o.data,
^search_query
)
)
end
defp query_with(q, :gin, search_query, :websearch) do
from([a, o] in q,
where:
fragment(
- "to_tsvector('english', ?->>'content') @@ websearch_to_tsquery('english', ?)",
+ "to_tsvector(?->>'content') @@ websearch_to_tsquery(?)",
o.data,
^search_query
)
)
end
defp query_with(q, :rum, search_query, :plain) do
from([a, o] in q,
where:
fragment(
- "? @@ plainto_tsquery('english', ?)",
+ "? @@ plainto_tsquery(?)",
o.fts_content,
^search_query
),
order_by: [fragment("? <=> now()::date", o.inserted_at)]
)
end
defp query_with(q, :rum, search_query, :websearch) do
from([a, o] in q,
where:
fragment(
- "? @@ websearch_to_tsquery('english', ?)",
+ "? @@ websearch_to_tsquery(?)",
o.fts_content,
^search_query
),
order_by: [fragment("? <=> now()::date", o.inserted_at)]
)
end
defp maybe_restrict_local(q, user) do
limit = Pleroma.Config.get([:instance, :limit_to_local_content], :unauthenticated)
case {limit, user} do
{:all, _} -> restrict_local(q)
{:unauthenticated, %User{}} -> q
{:unauthenticated, _} -> restrict_local(q)
{false, _} -> q
end
end
defp restrict_local(q), do: where(q, local: true)
defp maybe_fetch(activities, user, search_query) do
with true <- Regex.match?(~r/https?:/, search_query),
{:ok, object} <- Fetcher.fetch_object_from_id(search_query),
%Activity{} = activity <- Activity.get_create_by_object_ap_id(object.data["id"]),
true <- Visibility.visible_for_user?(activity, user) do
[activity | activities]
else
_ -> activities
end
end
end
diff --git a/priv/repo/migrations/20210121080964_add_default_text_search_config.exs b/priv/repo/migrations/20210121080964_add_default_text_search_config.exs
new file mode 100644
index 000000000..09b6cccc9
--- /dev/null
+++ b/priv/repo/migrations/20210121080964_add_default_text_search_config.exs
@@ -0,0 +1,11 @@
+defmodule Pleroma.Repo.Migrations.AddDefaultTextSearchConfig do
+ use Ecto.Migration
+
+ def change do
+ execute("DO $$
+ BEGIN
+ execute 'ALTER DATABASE '||current_database()||' SET default_text_search_config = ''english'' ';
+ END
+ $$;")
+ 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
index 82e02281d..88476fb57 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
@@ -1,51 +1,51 @@
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');
+ new.fts_content := to_tsvector(new.data->>'content');
return new;
end
$$ LANGUAGE plpgsql")
execute(
"create index if not exists 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 if exists objects_fts")
execute("drop trigger if exists tsvectorupdate on objects")
execute("drop function if exists objects_fts_update()")
alter table(:objects) do
remove(:fts_content, :tsvector)
end
create_if_not_exists(
index(:objects, ["(to_tsvector('english', data->>'content'))"],
using: :gin,
name: :objects_fts
)
)
end
end

File Metadata

Mime Type
text/x-diff
Expires
Sat, Aug 8, 12:24 PM (1 d, 11 h)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
1723242
Default Alt Text
(19 KB)

Event Timeline