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
When adding a new account to a list, if that list already has enough items, the new addition will fail due to the way that lists are implemented and indexed in the PostgreSQL database.
To replicate:
- On your Pleroma account, open a new list
- Proceed to add accounts to that group
- Eventually, adding a new account to the group will fail with a generic error message.
- Check the 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