[IMP] core: add/update foreign keys in batch

This greatly reduces the number of queries to add foreign keys (notably
for many2one fields).

This saves 6.5% of the total installation time.

X-original-commit: 13a2666b4da09fc0ec7608a974dadde45f76fd7a

2 files changed, 83 insertions, 38 deletions

diff --git a/odoo/fields.py b/odoo/fields.py
index ec384c09429..b96c9fe64f8 100644
--- a/odoo/fields.py
+++ b/odoo/fields.py
@@ -2437,11 +2437,10 @@ class Many2one(_Relational):
         if not comodel._auto or comodel._table == 'ir_actions':
             return
         # create/update the foreign key, and reflect it in 'ir.model.constraint'
-        process = sql.fix_foreign_key if column else sql.add_foreign_key
-        new = process(model._cr, model._table, self.name, comodel._table, 'id', self.ondelete or 'set null')
-        if new:
-            conname = '%s_%s_fkey' % (model._table, self.name)
-            model.env['ir.model.constraint']._reflect_constraint(model, conname, 'f', None, self._module)
+        model.pool.add_foreign_key(
+            model._table, self.name, comodel._table, 'id', self.ondelete or 'set null',
+            model, self._module
+        )
 
     def _update(self, records, value):
         """ Update the cached value of ``self`` for ``records`` with ``value``. """
@@ -3268,28 +3267,22 @@ class Many2many(_RelationalMulti):
             """.format(rel=self.relation, id1=self.column1, id2=self.column2)
             cr.execute(query, ['RELATION BETWEEN %s AND %s' % (model._table, comodel._table)])
             _schema.debug("Create table %r: m2m relation between %r and %r", self.relation, model._table, comodel._table)
-            model.pool.post_init(self.update_db_foreign_keys, model)
-            return True
-        elif sql.table_kind(cr, comodel._table) != 'v' and self.ondelete != 'cascade':
-            # Fix foreign key references with ondelete, unless the targets are
-            # SQL views.
-            # This is needed because by default the ondelete of the column1
-            # fkey is set to 'cascade', but the relation on the opposite model
-            # can override it by defining ondelete for its column2 fkey.
-            sql.fix_foreign_key(cr, self.relation, self.column2, comodel._table, 'id', self.ondelete)
+
+        model.pool.post_init(self.update_db_foreign_keys, model)
 
     def update_db_foreign_keys(self, model):
         """ Add the foreign keys corresponding to the field's relation table. """
-        cr = model._cr
         comodel = model.env[self.comodel_name]
-        reflect = model.env['ir.model.constraint']._reflect_constraint
-        # create foreign key references with ondelete, unless the targets are SQL views
-        if sql.table_kind(cr, model._table) != 'v':
-            sql.add_foreign_key(cr, self.relation, self.column1, model._table, 'id', 'cascade')
-            reflect(model, '%s_%s_fkey' % (self.relation, self.column1), 'f', None, self._module)
-        if sql.table_kind(cr, comodel._table) != 'v':
-            sql.add_foreign_key(cr, self.relation, self.column2, comodel._table, 'id', self.ondelete)
-            reflect(model, '%s_%s_fkey' % (self.relation, self.column2), 'f', None, self._module)
+        if model._is_an_ordinary_table():
+            model.pool.add_foreign_key(
+                self.relation, self.column1, model._table, 'id', 'cascade',
+                model, self._module, force=False,
+            )
+        if comodel._is_an_ordinary_table():
+            model.pool.add_foreign_key(
+                self.relation, self.column2, comodel._table, 'id', self.ondelete,
+                model, self._module,
+            )
 
     def read(self, records):
         context = {'active_test': False}
diff --git a/odoo/modules/registry.py b/odoo/modules/registry.py
index 9d9833308e4..4e1fe137eb5 100644
--- a/odoo/modules/registry.py
+++ b/odoo/modules/registry.py
@@ -113,7 +113,6 @@ class Registry(Mapping):
         self._assertion_report = assertion_report.assertion_report()
         self._fields_by_model = None
         self._ordinary_tables = None
-        self._post_init_queue = deque()
         self._constraint_queue = deque()
 
         # modules fully loaded (maintained during init phase by `loading` module)
@@ -367,26 +366,33 @@ class Registry(Mapping):
         env = odoo.api.Environment(cr, SUPERUSER_ID, context)
         models = [env[model_name] for model_name in model_names]
 
-        # make sure the queue does not contain some leftover from a former call
-        self._post_init_queue.clear()
-        self._is_install = install
+        try:
+            self._post_init_queue = deque()
+            self._foreign_keys = {}
+            self._is_install = install
 
-        for model in models:
-            model._auto_init()
-            model.init()
+            for model in models:
+                model._auto_init()
+                model.init()
 
-        self._ordinary_tables = None
+            self._ordinary_tables = None
+
+            while self._post_init_queue:
+                func = self._post_init_queue.popleft()
+                func()
 
-        self.check_indexes(cr, model_names)
+            self.check_indexes(cr, model_names)
+            self.check_foreign_keys(cr)
 
-        while self._post_init_queue:
-            func = self._post_init_queue.popleft()
-            func()
+            env['base'].flush()
 
-        env['base'].flush()
+            # make sure all tables are present
+            self.check_tables_exist(cr)
 
-        # make sure all tables are present
-        self.check_tables_exist(cr)
+        finally:
+            del self._post_init_queue
+            del self._foreign_keys
+            del self._is_install
 
     def check_indexes(self, cr, model_names):
         """ Create or drop column indexes for the given models. """
@@ -415,6 +421,52 @@ class Registry(Mapping):
             elif not index and indexname in existing:
                 sql.drop_index(cr, indexname, tablename)
 
+    def add_foreign_key(self, table1, column1, table2, column2, ondelete,
+                        model, module, force=True):
+        """ Specify an expected foreign key. """
+        key = (table1, column1)
+        val = (table2, column2, ondelete, model, module)
+        if force:
+            self._foreign_keys[key] = val
+        else:
+            self._foreign_keys.setdefault(key, val)
+
+    def check_foreign_keys(self, cr):
+        """ Create or update the expected foreign keys. """
+        if not self._foreign_keys:
+            return
+
+        # determine existing foreign keys on the tables
+        query = """
+            SELECT fk.conname, c1.relname, a1.attname, c2.relname, a2.attname, fk.confdeltype
+            FROM pg_constraint AS fk
+            JOIN pg_class AS c1 ON fk.conrelid = c1.oid
+            JOIN pg_class AS c2 ON fk.confrelid = c2.oid
+            JOIN pg_attribute AS a1 ON a1.attrelid = c1.oid AND fk.conkey[1] = a1.attnum
+            JOIN pg_attribute AS a2 ON a2.attrelid = c2.oid AND fk.confkey[1] = a2.attnum
+            WHERE fk.contype = 'f' AND c1.relname IN %s
+        """
+        cr.execute(query, [tuple({table for table, column in self._foreign_keys})])
+        existing = {
+            (table1, column1): (name, table2, column2, deltype)
+            for name, table1, column1, table2, column2, deltype in cr.fetchall()
+        }
+
+        # create or update foreign keys
+        for key, val in self._foreign_keys.items():
+            table1, column1 = key
+            table2, column2, ondelete, model, module = val
+            conname = '%s_%s_fkey' % key
+            deltype = sql._CONFDELTYPES[ondelete.upper()]
+            spec = existing.get(key)
+            if spec is None:
+                sql.add_foreign_key(cr, table1, column1, table2, column2, ondelete)
+                model.env['ir.model.constraint']._reflect_constraint(model, conname, 'f', None, module)
+            elif spec != (conname, table2, column2, deltype):
+                sql.drop_constraint(cr, table1, spec[0])
+                sql.add_foreign_key(cr, table1, column1, table2, column2, ondelete)
+                model.env['ir.model.constraint']._reflect_constraint(model, conname, 'f', None, module)
+
     def check_tables_exist(self, cr):
         """
         Verify that all tables are present and try to initialize those that are missing.