Odoo 16: psycopg2.InterfaceError Cursor Already Closed

The psycopg2.InterfaceError: Cursor already closed error in Odoo 16, particularly when triggered by successive onchange events, points to a fundamental misunderstanding of how Odoo's caching mechanisms interact with database cursors and the Odoo environment.

This issue typically manifests when a method decorated with @ormcache returns an Odoo recordset. While this seems like a straightforward optimization, the recordset becomes inextricably linked to the specific Odoo environment and its associated database cursor that were active when the cache was populated. When the Odoo worker process subsequently closes the cursor for a completed request, any attempt to access fields on that cached recordset in a new request will fail, as it tries to use a cursor that no longer exists.

The Root Cause: @ormcache and Recordset Lifecycles

Odoo's @ormcache decorator implements a registry-level LRU cache. This cache persists across multiple HTTP requests and onchange events, living as long as the Odoo worker process. This persistence is key to the problem. When a cached method returns a recordset, it's not just caching the data; it's caching a reference to an Odoo model instance bound to a specific database cursor from a past request.

Each Odoo HTTP request is allocated its own database cursor. This cursor is managed by the environment object (self.env) and is automatically closed upon completion of the request. If a recordset retrieved from the @ormcache was created with a cursor from a previous request, that cursor will be long gone by the time the cached recordset is accessed again, leading directly to the psycopg2.InterfaceError: Cursor already closed.

Understanding the Error Sequence

Let's trace the error sequence that leads to the psycopg2.InterfaceError. During the first onchange event, a method decorated with @ormcache is called. If it's a cache miss, the method executes, performs a search() operation, and returns a recordset. This recordset is bound to the current request's database cursor. The @ormcache then stores this recordset. Upon completion of the request, the cursor is closed.

On a subsequent onchange event, the same method is called with identical arguments. This time, it's a cache hit. The @ormcache returns the stale recordset that was cached earlier. When any field on this recordset is accessed (e.g., to compute a value for a stored field), Odoo attempts to execute a SQL query using the now-closed cursor. This is where the psycopg2.InterfaceError: Cursor already closed error is raised.

The Flaw in Caching Recordsets

The fundamental issue lies in caching the Odoo recordset object itself. A recordset is not a mere data structure; it's a live object with state that is deeply tied to its originating environment, including the database cursor. Storing such an object in a process-global cache like @ormcache creates a temporal disconnect.

Unlike simple data types such as integers, strings, or tuples, recordsets carry implicit dependencies. Even if the cache key (the arguments to the cached method) remains the same, the underlying Odoo environment and its cursor will have changed between requests. The cached recordset, therefore, becomes a relic of a past state, incapable of interacting with the current database connection.

The Correct Approach: Cache Primitives

The recommended solution is to never cache recordsets directly with @ormcache. Instead, cache primitive data types like record IDs, tuples, or dictionaries. These data types are inert and do not hold references to specific database cursors or environments.

The pattern involves creating a helper method that caches only the necessary identifiers (e.g., a contract ID). This cached ID can then be used to retrieve a fresh recordset in the current environment using self.browse() or self.env['model'].browse(). This ensures that any database interaction, including field access, uses a cursor that is valid for the current request.

Here's an example of the corrected pattern:

from odoo import api, fields, models from odoo.tools import ormcache class Contract(models.Model): _name = 'my_module.contract' @api.model @ormcache('partner_id', 'ref_date') def _find_active_contract_id(self, partner_id, ref_date): if not partner_id or not ref_date: return False contract = self.search([ ('partner_id', '=', partner_id), ('date_start', '<=', ref_date), ('date_end', '>=', ref_date), ], limit=1) return contract.id or False @api.model def find_active_contract(self, partner, ref_date): # Rebuild recordset in current env/cursor contract_id = self._find_active_contract_id(partner.id if partner else False, ref_date) return self.browse(contract_id) if contract_id else self.browse()

Cache Invalidation Strategies

A significant consideration when using @ormcache is cache invalidation. The cache does not automatically update when the underlying data changes. If you create, update, or delete records that might affect the result of a cached method, the cache needs to be explicitly cleared to prevent serving stale data.

A straightforward, albeit blunt, approach is to clear the cache in the create, write, and unlink methods of the model that might influence the cached results. This ensures that the cache is refreshed whenever relevant records are modified.

For example:

def create(self, vals): rec = super().create(vals) self._find_active_contract_id.clear_cache(self.env['my_module.contract']) return rec def write(self, vals): res = super().write(vals) self._find_active_contract_id.clear_cache(self.env['my_module.contract']) return res def unlink(self): res = super().unlink() self._find_active_contract_id.clear_cache(self.env['my_module.contract']) return res

While this pattern is effective for ensuring data consistency, more granular invalidation strategies can be implemented for performance optimization in complex scenarios.

Where This Approach Does Not Fit

This pattern of caching primitives and rebuilding recordsets is highly effective for optimizing read-heavy operations that are called frequently, especially across different user sessions or onchange events. However, it introduces complexity and requires careful management of cache invalidation.

This approach might not be suitable for situations where the overhead of cache invalidation outweighs the performance gains, or in very simple methods where the performance impact of repeated execution is negligible. Furthermore, for operations that are inherently stateful and tied to a specific user session's temporary data, a registry-level cache is inappropriate. Always consider the lifecycle of your data and the persistence requirements of your cache when implementing such optimizations in your Odoo-16 development workflow.

This article draws on Odoo 16: psycopg2.InterfaceError: Cursor already closed on second onchange call and answers to it (1, 2), on the Stack Exchange network, licensed under CC BY-SA 4.0.