ir_translation: Index rows size exceeds btree maximum

[José Pastor]

Sorry my English.

I add new product A with

A.name
A.description_en

description_en is text type then no problem with length.

I can save new product without problem when description_en is very long.

Now, I translate this very long field, description_en, to Spanish with A.description_es

When I entry description_es, system inserts src and valeu in ir_translation.

src set to A.description_en
value set to A.description_es

Because src is involved in some btree index, then A.description_en is length limited.

If A.description_es is long enough then system launchs this message:

ERROR: index row size 5364 exceeds btree maximum, 2713

[Borja López Soilán (NeoPolus) (borjals)]

Seems that currently the index is being created like this:
--
CREATE INDEX ir_translation_lts
  ON ir_translation
  USING btree
  (lang, type, src);
--

I think a workaround would be to use partial indexes that exclude long items (you still will be able to search for that long items, they will just took longer):

--
DROP INDEX ir_translation_lts;
CREATE INDEX ir_translation_lts
  ON ir_translation
  USING btree
  (lang, type, src)
  WHERE LENGTH(src) < 2500;
--

Can you test if that solves your problem?

[Albert Cervera i Areny - http://www.NaN-tic.com (albert-nan)]

The attached patch solves the problem by using two indexes:

- One of type btree for 'lang', 'type' and 'name' fields
- Another one of type hash for 'src' field

The reason is that hash indexes do not support multiple columns. Also note that AFAIK current indexes have no sense given that PostgreSQL can use an index composed of (lang, type, name, src) to search by (lang, type, name) only. So there was no reason for having both of them (indeed it's probably worse performance wise).

As stated by Borja, another option would be to use a partial index, but I think it complicates things a little bit and would affect performance negatively in databases with lots of large strings (ie. users with lots of large descriptions in products, for example).

[Anup(SerpentCS) (anup-serpent)]

Hello all,

   It has been fixed by revision 2127 <email address hidden>
@Albert : Thanks a lot for pointing towards such issue and helping to resolving it.

Thanks.