"relation already exists" after adding a Many2many field in odoo

Question

I've defined the following two odoo ORM models:

class Weekday(models.Model):
    _name = 'ludwik.offers.weekday'
    name = fields.Char()

class Duration(models.Model):
    _name = 'ludwik.offers.duration'
    weekday = fields.Many2many('ludwik.offers.weekday')

When I try to start odoo I get the following message:

ProgrammingError: relation "ludwik_offers_duration_ludwik_offers_weekday_rel_ludwik_offers_" already exists

Also, when I change the _name properties in models, the problem persists (of course the relation name in the error message changes to reflect the rename), so it's not like it's just a conflict with some old relations already existing in the database.

Answer 1

I figured this out. I have to say, I think this technically qualifies as a bug in Odoo.

Summary

The names of my models were too long. Every time you set a _name property longer than 16 characters you are setting yourself up to potentially experience this problem.

Details

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 as follows:

• <model1>_<model2>_rel_<model1>_id_index
• <model1>_<model2>_rel_<model2>_id_index

Where <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).

So what is the maximum length the _name property can have while avoiding the problem? It depends on how many characters are shared 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 us 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.

Answer 2

I don't see nay problem with your code. Maybe you arrived at a corner case the ORM can't handle well, as a result or experimenting several variations of the relation.

Probably trying with a freshly initialized database would work.

If you really want to keep working with the current database, you can try uninstalling you module and then reinstalling it. This should have it's database objects be dropped and then recreated.

Finally, it that doesn't work, try to manually drop the ludwik_offers tables in the database and the upgrade your module so that hey are re-created.