Indexing crash on migration 6.8

acaubet · September 20, 2023, 9:31am

Moving from 6.4 to 6.8, the update had crashed with:

68109 139803431196480 [2023-09-20 09:20:46,433] INFO trytond.modules index:create ir.translation
Traceback (most recent call last):
  File "/home/vant/Documentos/GitLab/customer/.venv/bin/trytond-admin", line 31, in <module>
    admin.run(options)
  File "/home/vant/Documentos/GitLab/customer/.venv/lib/python3.8/site-packages/trytond/admin.py", line 57, in run
    pool.init(update=options.update, lang=list(lang),
  File "/home/vant/Documentos/GitLab/customer/.venv/lib/python3.8/site-packages/trytond/pool.py", line 167, in init
    restart = not load_modules(
  File "/home/vant/Documentos/GitLab/customer/.venv/lib/python3.8/site-packages/trytond/modules/__init__.py", line 456, in load_modules
    _load_modules(update)
  File "/home/vant/Documentos/GitLab/customer/.venv/lib/python3.8/site-packages/trytond/modules/__init__.py", line 424, in _load_modules
    load_module_graph(graph, pool, update, lang)
  File "/home/vant/Documentos/GitLab/customer/.venv/lib/python3.8/site-packages/trytond/modules/__init__.py", line 305, in load_module_graph
    model._update_sql_indexes()
  File "/home/vant/Documentos/GitLab/customer/.venv/lib/python3.8/site-packages/trytond/model/modelsql.py", line 489, in _update_sql_indexes
    table_h.set_indexes(cls._sql_indexes)
  File "/home/vant/Documentos/GitLab/customer/.venv/lib/python3.8/site-packages/trytond/backend/postgresql/table.py", line 501, in set_indexes
    cursor.execute(
  File "/home/vant/Documentos/GitLab/customer/.venv/lib/python3.8/site-packages/trytond/backend/postgresql/database.py", line 68, in execute
    cursor.execute(self, sql, args)
psycopg2.errors.ProgramLimitExceeded: index row size 2976 exceeds btree version 4 maximum 2704 for index "idx_ir_translation_17959a6671e3a3c93dfdf2fc3640d645"
DETAIL:  Index row references tuple (392,11) in relation "ir_translation".
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

I don’t know what caused or how it can be solved, and/or if it’s something related to some custom code.

ced · September 20, 2023, 10:56am

I do not think it is really usable to have large Text field translatable.
With Support btree_gin extension (#12480) · Issues · Tryton / Tryton · GitLab GIN index could be used for such case which has no limit.

acaubet · September 20, 2023, 11:03am

Sorry, I don’t understand what you mean.

Is this only affecting my module or it’s core? Should be reported?
Is there any workaround I can do to update while it’s being fixed?

ced · September 20, 2023, 4:20pm

It depends on the data. But it can not be fixed as it is a limitation of PostgreSQL index.

It depends on the source of the problem.
If it is a custom Text field, probably it should not be translatable.
If it is a standard Text field, user should be questioned about why they put such large amount of text in a translatable field.

acaubet · September 20, 2023, 4:38pm

I have a better understanding now, thanks.

Should we make size required on Text translatable fields to prevent this? What is the max size allowed that prevents this?

ced · September 21, 2023, 8:37am

It depends on the backend and even on the version of the backend.

ced · September 29, 2023, 3:09pm

I think we could add a size constraint automatically on Char and Text fields that are translated if they have any size. The value could be defined as a configuration value (so system admin can modify it according to the backend configuration).