ir.property records are not properly cleaned up when the record they refer to is deleted

[Ronald Portier (Therp)]

The list of properties (model ir.property) will in many cases be polluted with references to records in models that no longer exist.

ir.property can dynamically refer to a model through the field res_id. This field is filled with the name of the model that is referred to, together with the id in the model. For instance res.partner,5 will refer to the partner with id 5.

When partner 5 is deleted / unlinked, the property will remain in the system. In some cases this can result in thousands of records in ir_property that no longer refer to anything existing.

For instance to find out which partners that ir_property refers to no longer exist, use the following SQL:

with prop_pos as
    (select position(',' in res_id) as pos, res_id
    from ir_property where not res_id is null),
prop_res_id as (
    select substring(res_id, 1, pos - 1) as model, pos, substring(res_id, pos + 1)::integer as partner_id
    from prop_pos)
select * from prop_res_id where model = 'res.partner' and partner_id not in (select id from res_partner);

To see which tables ir_property refers to (and a count of records referred to):

select count(*), substring(res_id, 1, (position(',' in res_id) - 1)) as model
from ir_property where not res_id is null and not res_id = '' group by model;

I will try to create a wizard reporting on such dangling references and/or removing them.

I found the problem on 6.1 instances, but do not see a reason why it could not occur on other versions.

[Ronald Portier (Therp) (rportier1962)]

Testing on trunk, I found that normally the property will be deleted, when the record referred to is deleted. So some more research is needed in what circumstances the problem occurs. The same code to delete the properties (in the unlink method of osv) is present in 6.1 as wel...

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

Hello Ronald,

did you look at these bug report for which there are patches but not applied on the 6.1 ?
https://bugs.launchpad.net/openobject-server/+bug/858903
https://bugs.launchpad.net/openobject-server/+bug/891580

I didn't took time to analyse if it's the same issue you are reporting but just pointing you to them as I remember these similar issues made people busy before.

[Ronald Portier (Therp) (rportier1962)]

Hello Raphaël,

Thanks for pointing out the other bugreports. Had not found them when searching on ir_property.

I still think that somewhere this week I will make some simple tool to clear up ir_property, whenever it gets polluted with dangling references to no longer existing records.

And need to think about what we can to to fix the issue on 6.1 . Seems a lot of things are solved on trunk but never make it to 6.1 (or maybe not to 7.0, now it is released....). We have a server and addons backport branch at Therp (on launchpad ofcourse), mut should maybe also become a community project - like with all the extra/community addons....