Delete many2many column in table and the table itself - odoo 13

While running a pre-migration script to delete a (wizard) transient model, ended up with below mentioned issue.

from openupgradelib import openupgrade


@openupgrade.migrate()
def migrate(env, version):    
    openupgrade.delete_records_safely_by_xml_id(
        env,
        ["moduel_name.view_id)"],
        delete_childs=True,
    )

    try:
        env.cr.execute("DROP TABLE IF EXISTS table_name CASCADE")
        env.cr.execute("DROP TABLE IF EXISTS dependent_table_names CASCADE")

    except Exception as e:
        raise ("Exception--------------", e)

Error:

psycopg2.errors.ForeignKeyViolation: update or delete on table "ir_model" violates foreign key constraint "ir_model_relation_model_fkey" on table "ir_model_relation"

According to the above issue, having Many2many in transient model might cause this issue. It is true in my case as well. I have many2many fields. No solution there.

I kind of tried deleting the problematic fields(Many2many) before deleting columns. But it is known that many2many fields can't be located in db. kind of stuck.

openupgrade.drop_columns(
    env.cr,
    [
        ("table_name", "any_other_column_name"), # ---> This works
        ("table_name", "many2many_column_name"), # ---> This doesn't
    ],
)

is there anyway to get rid of many2many fields from the model ? Any help is appreciated.

Solution

Could you try this :

Let's say your Transient is my_transient_model and the Many2many field is e.g. sale_line_ids = fields.Many2many('sale.order_line')

First thing to know : Did you specify the relation table ? like

sale_line_ids = fields.Many2many('sale.order_line', 'my_relation_table_name') ?

If so, 'my_relation_table_name' is the name you want to delete from ir_model_relation.

If not, the relation table name is my_transient_model_sale_order_line_rel (so model then _ then the model we point to with _ instead of . then _rel.

Second set: delete the data from ir_model_relation:

DELETE FROM ir_model_relation WHERE name='my_transient_model_sale_order_line_rel';

Then you should be able to delete the Many2many table :

DROP TABLE my_transient_model_sale_order_line_rel;

(for sure, change my_transient_model_sale_order_line_rel if you specified the relation table like my_relation_table_name in the example)

Hope it helped, keep me updated :)