OD122 sql-write-without-invalidation¶

An SQL write runs without invalidating the cache, so the ORM keeps stale values.

What it reports¶

A query a method of a class runs with cr.execute() or env.execute_query() whose text starts, after comments, with INSERT INTO, UPDATE or DELETE FROM and a table name, when the method never invalidates the cache. A query that opens with WITH is reported for each write of its statement and of its CTEs, when its text can be read to its end; FOR UPDATE and the DO UPDATE of ON CONFLICT are no writes. The text is read from a literal, SQL("..."), "..." % values, "...".format() or the literal start of an f-string, given directly or through a name the method binds once, with a plain =; a table spliced in, or a name bound twice or by +=, is not checked. Functions the method defines count as part of it, and a class it defines is checked as a class of its own.

A call to invalidate_model(), invalidate_recordset(), invalidate_all(), invalidate_cache(), _invalidate_cache() or cache.invalidate() anywhere in the method keeps it silent, as does a call on self of another method of the class that makes one. It is also silent on a write:

• in init(), _auto_init(), _init_column(), a method named _reflect_*(), or from 20.0 a method a field names as its init_storage: Odoo runs them when it sets up the tables, before any record is cached;
• in an @api.autovacuum method, which Odoo calls from its cleanup job and commits after;
• in the method a field names as its inverse, when the query is an UPDATE of the model's own table that sets only the columns of the fields it is the inverse of, values the cache already holds;
• that is an UPDATE in a method that adds, with create_column(), the column the query sets, or a column of its table when the query does not name the columns it sets: no field reads them yet. A column the call names with anything but a literal silences every UPDATE of the table. A DELETE FROM or an INSERT INTO on the table is still reported, since cached records hold the rows it changes;
• on a cursor the method opens itself, or an environment built on one, which is a transaction of its own, when every path reaching the write opens it, as OD103 describes: after if new: self = self.with_env(self.env(cr=registry.cursor())), a write on self.env.cr is reported.

Why it matters¶

The ORM keeps the field values it read or wrote in a cache for the whole transaction, and holds back its own writes until it flushes them. SQL goes around both. Records read before the query keep their old values, so a compute, a constraint or a later write() works from data that is no longer in the table, and a pending ORM write flushed after the query overwrites what it changed.

Flush the fields the query reads or writes before it, and invalidate them after it. From 16.0, invalidate_model() and invalidate_recordset() flush first, so one call after the query is the least that keeps the cache right; before 16.0, invalidate_cache() does not flush, so call flush() before the query too.

Example¶

class ResPartner(models.Model):
    _inherit = "res.partner"

    def action_x_archive_children(self):
        self.env.cr.execute(
            "UPDATE res_partner SET active = false WHERE parent_id IN %s", [tuple(self.ids)]
        )
        return self.child_ids.filtered("active")

child_ids and their active values come from the cache, so the archived contacts are still returned.

class ResPartner(models.Model):
    _inherit = "res.partner"

    def action_x_archive_children(self):
        self.env["res.partner"].flush_model(["active", "parent_id"])
        self.env.cr.execute(
            "UPDATE res_partner SET active = false WHERE parent_id IN %s", [tuple(self.ids)]
        )
        self.env["res.partner"].invalidate_model(["active"])
        return self.child_ids.filtered("active")

Suppressing¶

self.env.cr.execute("UPDATE res_partner SET active = false")  # odooscan: ignore[OD122]

See suppressing a finding.