Is your feature request related to a problem?

Actually, when you create a custom attribute, an alter of the table is performed to add a new column to store the attribute. The main issue is that while an alter on a table is performed, the table is lock by PostgreSQL. This mean that if you have a user that add a new attribute and one that is doing a search on the same table, the search will be blocked until the alter is finished.

This problem is solved if you use serialized attributes. It uses the base_sparse_field addon to store the attributes in a serialized column, this mean that the table is not altered. But you can't perform search on these attributes.

We want to avoid the alter of the table and still be able to perform search on attributes.

Another improvement is to allow the user to define fields that would be displayed on a search panel for a given model(product.template) to perform a search without having to create a new filter in the search.

Describe the solution you'd like

We can use the jsonb field of PostgreSQL to store the attributes. PostgreSQL provide a very good support for jsonb field to perform search and index on it.

For the search panel, we could reuse the widget search panel of Odoo and add a new field to define the field that would be display on the search panel. This widget only support field if type selection or many2one. We could extend it to support boolean to.

Describe alternatives you've considered

We have considered to use Elasticsearch to store the attributes. But this implied to have a new dependency and to have a new service to maintain.

Additional context

If we want to use other type of storage, we could extend our implementation with a new type of field and have a nice base to start with the implementation done for the jsonb field.

How to use :

In the attribute creation menu you now have a new type of attribute named 'Json_postgresql' with the same option as custom field except this field can't be serialized.

Some type of field are not supported, Select and Multiselect because json_field does not support relation in PostgreSQL. But the Select type can be replaced by the Selection type that use selection field of Odoo.

For the search panel once the module is installed, you can go to Settings > Search panel fields and then select the field that should appear on the search panel. But be sure that the model you want to have the search panel inherit search.panel.mixin or nothing will show.

Once a field is selected, it appears on the search panel, it will only display the value present in the database, if you have a selection field with the values wood, plastic, iron and iron isn't selected on any model than it won't be displayed.

OCA / odoo-pim

Storage and search optimization #153

145

146

147

148

149

150

154

155

156

157

158

159

160