Hello all,
I have a rather big database in Odoo 13 (around 55Milion records ~ 50GB postgres database, without filestore).
I have done a clean setup with Ubuntu 20.04 LTS, backed the DB from the old server and restored using pg_restore into the new server. I am using Postgres 13.1 but this also happens with Postgres 12.4.
The database opens fine and I can do all the expected operations with it (create, edit, search, ...).
The problem is quite simple: When I try to upgrade an addon, Odoo generates a HUGE query with ALL of the 55M IDs. Postgres gives the error: "ERROR: invalid memory alloc request size 1073741824". I am doing the update via command-line with a command like this:
setup/odoo -u myaddonname -d database_name --addons-path=/home/developer/eclipse-workspace/odoo-13.0/odoo/addons
I have tweaked with the postgres parameters and even rented a 32CPU + 128 GB RAM VM at AWS just to make sure it was not a problem with resources.
Postgres is running with the following parameters:
postgres -c max_connections=20 -c shared_buffers=32GB -c effective_cache_size=96GB -c maintenance_work_mem=1GB -c checkpoint_completion_target=0.9 -c wal_buffers=16MB -c default_statistics_target=500 -c random_page_cost=1.1 -c effective_io_concurrency=200 -c work_mem=52428kB -c min_wal_size=4GB -c max_wal_size=16GB -c max_worker_processes=32 -c max_parallel_workers_per_gather=16 -c max_parallel_workers=32
Is there a way to make the addon update process simpler? Or is there a parameter that I can change in Postgres to allow such a big query to be executed?
Any clue would be much appreciated!
Fernando
Which add-on?
The problem seems to be with a field that is declared as inheritence by delegation (not the normal inheritance used in most of Odoo's addons). The relation is declared using the _inherits and not the _inherit.
We have several levels of inheritance and Odoo does a search for ALL of the IDs in all of the inheritance levels. This leads to a 55M ids in the IN part of the query for ALL models in the database... :/
Hello Ray,
Its a custom addon with a simple model with about 20 fields. This works fine with about 100K records (test database). But when I run the same process with the production database that has 55M records I allways get this error!
I have noticed that if I change the source-code at line 5739 (_modified_triggers(self, tree, create=False):
And change this to:
It runs rather slow but it seems to avoid the error...
I didn't, however, wait for it to complete... maybe it would give the same error...
Is there any way to increase this 1073741824 memory limit in postgres? I have search a lot in google and it seems that this value (exactly 1GB) is hardcoded in Postgres' source-code.
Any clue would be VERY appreciated... I have spent days with this and without any success, so far...
Thanks!
EDIT: I have also tested using the most recent Odoo 13.0.0 from source (from todays' date: 2020-12-25). The models.py file is different, but the result is the same... :/
The problem is that Odoo runs a query to get ALL the IDs at once (55M ids) and then uses those IDs in the next query in an "IN" statement like this: SELECT "table".id FROM "table" WHERE ("table"."iid" in ( [ Here we have the 55M Ids ] ).
For what I have researched the problem is because of the computation of dependent fields (fields that are related to other fields). Odoo iterates through ALL of the records to compute these fields...