Odoo Specific Information

Odoo Transaction Behavior

Odoo operates by putting every request in a transaction, and further, specifies "repeatable read" as part of this. It is this transaction behavior that actually causes much of the overhead of Odoo on the database, and as a result, Heimdall is not able to do caching or read/write split by default, so removing the majority of benefits that Odoo users desire.

In order to resolve this, Heimdall provides the option for "delayed transactions", in the VDB advanced tab. The way this works is to trap when a transaction is desired, and actually NOT start the transaction then. If a DML (insert/update/delete/etc) is performed, then the transaction is started, and used until the transaction either commits or is rolled back. Technically, this behavior is in violation of the transaction "repeatable read" specification, in that even reads before a DML should read consistent data, but with this option, only after a DML is this correct. This has not been observed to cause issues with customers. If this is a concern, please note that NO solution can provide caching and/or read/write split without violating this in some way. Other customers are using this successfully without it causing errors, but if you have custom Odoo modules that depend on strict repeatable read behavior, please contact Heimdall support. We do have ways to change behaviors via rules so that if an issue is identified, we can modify the behavior for the queries in question, while providing benefit for others.

Configuration Items for Odoo

You can use a ready-made template, which should be selected in the wizard, or you can do it manually. If so, follow the instructions below.

First, configure the data source using the Wizard and verify connectivity. Then following these additional steps:

  • In the postgresql.conf, set "default_transaction_isolation = 'repeatable read'" to set the default isolation level to repeatable read by default, avoiding the need for this to be set on connections. If you don't have access to the postgresql.conf or can't set it globally, you can alternatively set it at the database level with:
ALTER DATABASE SET DEFAULT_TRANSACTION_ISOLATION TO 'repeatable read';

  • In the VDB, for Odoo, specify in the Advanced tab:

    • Multiplex with a timeout of 0
    • Delayed Transactions

  • In the Data source tab:

    • Make sure the catalog field in the JDBC url is filled in with ${catalog}
    • In connection properties the defaultCatalog parameter is specified, generally with "odoo" as the default
    • Connection pooling is enabled

  • In the rules tab, besides the normal caching and read/write split rules as needed, a rule with regex of "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ", intrans NOT set, Action of Result, and responseInt of "1". This rule will prevent the commands to set the transaction isolation level from being sent to the server, which with delayed transactions will result in the server generating warnings in it's logs.

Internally, Heimdall will capture certain types of queries that appear to be operating against sequences (which Odoo does) and will automatically flag these as potential DML operations. This will be noted in the Analytics by some queries not getting cached that may otherwise appear cachable. Please see the help section to debug caching for information on how to debug why a query is not cached.

Cache Validation

if there is concern about if stale data is being returned from the cache, Heimdall includes a feature to validate cache data, and report if it was stale. To do this, on the cache rule, specify the property of "revalidateOnly" and set to "true". This will bypass serving from the cache, but will still cache objects and compare the cached object with what was previously there. In the event an object is not byte-for-byte identical, then an alert will be generated on the GUI with the query that generated the issue. This can be used to generate cache bypass rules if there are issues.