This blog post is based on my StackOverflow question, my own StackOverflow answer and my subsequent bug report.
Sometimes after adding a Many2many field to a model in Odoo, it fails with an error message saying ProgrammingError: relation "<some long name>" already exists. This seems pretty random and it’s hard to discover the real cause behind the problem.
When creating database index identifiers Odoo doesn’t take PostgreSQL length limits into account. This results in truncation of those identifiers and collisions between their truncated instances. As a consequence it is currently unsafe to use model names longer than 16 characters. As far as I know this is undocumented and can result in problems that are really hard to debug and understand.
The issue exists for all database identifiers, but it seems to have worse consequences when it comes to indexes for Many2many relation tables (as they are the most likely to exceed the length limit).
When you create a Many2many relation, odoo setts up a new database table for this relation and then creates two database indexes for the table. Their name are basically as follows:
<model1>_<model2>_rel_<model1>_id_index<model1>_<model2>_rel_<model2>_id_indexWhere <model1> and <model2> are the _name property of an appropriate model. You can observe this in _m2m_raise_or_create_relation method of odoo’s BaseModel.
There is however one catch. By default indetifiers in PostgreSQL (inluding index identifiers) can not be longer than 63 characters:
The system uses no more than NAMEDATALEN-1 bytes of an identifier; longer names can be written in commands, but they will be truncated. By default, NAMEDATALEN is 64 so the maximum identifier length is 63 bytes.
Odoo doesn’t take this into account. It happily generates much longer identifiers, which are then truncated by PostgreSQL. If both identifiers share the same first 63 characters (which for longer identifiers is quite likely) they will be treated by PostgreSQL as the same. That means the first index will be created, but creating the second one will result in an error, since it shares an identifier that was already used (at least according to PostgreSQL).
As a result odoo fails to start, displaying a cryptic relation already exists error.
So what is the maximum length the _name property can have while avoiding the problem? It depends on how many characters are shred between the names of two models in m2m relation, but to fully avoid identifier truncation you should never use names longer than 16 characters.
Why 16? PostgreSQL identifiers can not be longer than 63 characters. In index identifiers generated by odoo there are 15 fixed characters. That leaves us with 48 characters, which have to accommodate three repetitions of model names. This in turn leaves as with 16 characters per single model name.
Another way to work around the issue would be to manually set a short relation name via relation attribute on the Many2many field.
Comments
© Ludwik Trammer, 2014 — built with Jekyll using Lagom theme