Lists are limited to around 180 accounts due to indexing limitations
Environment
- Installation type (OTP or From Source): OTP
- Pleroma version (could be found in the "Version" tab of settings in Pleroma-FE): 2.5.2
- Elixir version (
elixir -v
for from source installations, N/A for OTP): N/A - Operating system: Debian 11 Bullseye
- PostgreSQL version (
psql -V
): 13.11
Bug description
Trying to add a new account to an existing list can fail if that list already has enough items (around 180 in my case, this can vary depending on the length of the account names in the list). The new addition will fail due to the way that lists are implemented and indexed in the PostgreSQL database, i.e. as a single long string instead of an array.
Logs:
pleroma[3589497]: 13:23:23.889 request_id=F2p0aMyDYOMTLgwAAvJi [error] Internal server error: %Postgrex.Error{message: nil, postgres: %{code: :program_limit_exceeded, constraint: "lists_following_index", detail: "Index row references tuple (0,9) in relation \"lists\".", file: "nbtutils.c", hint: "Values larger than 1/3 of a buffer page cannot be indexed.\nConsider a function index of an MD5 hash of the value, or use full text indexing.", line: "2662", message: "index row size 2712 exceeds btree version 4 maximum 2704 for index \"lists_following_index\"", pg_code: "54000", routine: "_bt_check_third_page", schema: "public", severity: "ERROR", table: "lists", unknown: "ERROR"}, connection_id: 3662753, query: nil}
pleroma[3589497]: 13:23:23.891 [error] #PID<0.12250.1> running Pleroma.Web.Endpoint (connection #PID<0.12249.1>, stream id 1) terminated
pleroma[3589497]: Server: social.example.net:80 (http)
pleroma[3589497]: Request: POST /api/v1/lists/13/accounts
pleroma[3589497]: ** (exit) an exception was raised:
pleroma[3589497]: ** (Postgrex.Error) ERROR 54000 (program_limit_exceeded) index row size 2712 exceeds btree version 4 maximum 2704 for index "lists_following_index"
pleroma[3589497]: table: lists
pleroma[3589497]: constraint: lists_following_index
pleroma[3589497]: hint: Values larger than 1/3 of a buffer page cannot be indexed.
pleroma[3589497]: Consider a function index of an MD5 hash of the value, or use full text indexing.
pleroma[3589497]: Index row references tuple (0,9) in relation "lists".
pleroma[3589497]: (ecto_sql 3.9.2) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1
pleroma[3589497]: (ecto 3.9.5) lib/ecto/repo/schema.ex:756: Ecto.Repo.Schema.apply/4
pleroma[3589497]: (ecto 3.9.5) lib/ecto/repo/schema.ex:459: anonymous fn/15 in Ecto.Repo.Schema.do_update/4
pleroma[3589497]: (elixir 1.14.3) lib/enum.ex:975: Enum."-each/2-lists^foreach/1-0-"/2
pleroma[3589497]: (pleroma 3.9.3-0-g39b3d92-develop) lib/pleroma/web/mastodon_api/controllers/list_controller.ex:66: Pleroma.Web.MastodonAPI.ListController.add_to_list/2
pleroma[3589497]: (pleroma 3.9.3-0-g39b3d92-develop) lib/pleroma/web/mastodon_api/controllers/list_controller.ex:5: Pleroma.Web.MastodonAPI.ListController.action/2
pleroma[3589497]: (pleroma 3.9.3-0-g39b3d92-develop) lib/pleroma/web/mastodon_api/controllers/list_controller.ex:5: Pleroma.Web.MastodonAPI.ListController.phoenix_controller_pipeline/2
pleroma[3589497]: (phoenix 1.6.16) lib/phoenix/router.ex:354: Phoenix.Router.__call__/2