[bug] Lists are limited to around 180 accounts due to indexing limitations #572

New issue

Open

opened 2023-06-20 19:34:13 +00:00 by csolisr · 1 comment

csolisr commented

2023-06-20 19:34:13 +00:00

Your setup

OTP

Extra details

Debian 11 Bullseye

Version

2023.05

PostgreSQL version

What were you trying to do?

Trying to add a new account to an existing 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.

What did you expect to happen?

Being able to add the new account as usual

What actually happened?

The application failed with a generic error

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
```

Severity

I cannot use it as easily as I'd like

Have you searched for this issue?

I have double-checked and have not found this issue mentioned anywhere.

### Your setup OTP ### Extra details Debian 11 Bullseye ### Version 2023.05 ### PostgreSQL version 13 ### What were you trying to do? Trying to add a new account to an existing 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. ### What did you expect to happen? Being able to add the new account as usual ### What actually happened? The application failed with a generic error ### Logs ````shell ``` 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 ``` ```` ### Severity I cannot use it as easily as I'd like ### Have you searched for this issue? - [x] I have double-checked and have not found this issue mentioned anywhere.

csolisr added the

bug

label 2023-06-20 19:34:13 +00:00

csolisr changed title from ~~[bug]~~ to [bug] Lists are limited to around 180 accounts due to indexing limitations

2023-06-20 19:36:18 +00:00

csolisr commented

2023-07-31 14:35:30 +00:00

Author

Fixing this issue may involve converting the current way that lists are handled (as a single long string) to a form that can be better handled by the database (possibly as a set of arrays).

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference: AkkomaGang/akkoma#572

No description provided.