In previous versions the max size of a domain was bounded by psycopg
memory limits. With the new SQL formatting mechanism the limit is bound
by the maximum recursion limit in Python side. The purpose of this patch
is to restore previous behavior.
In 16.0:
```
>>> def make_dom(N):
... return [*('|' for x in range(N-1)), *(('login', '=', 'admin') for x in range(N))]
...
>>> u.search(make_dom(9984))
res.users(2,)
>>> u.search(make_dom(9985))
Traceback (most recent call last):
File "<input>", line 1, in <module>
u.search(make_dom(9985))
File "/home/odoo/src/odoo/16.0/odoo/models.py", line 1520, in search
return res if count else self.browse(res)
File "/home/odoo/src/odoo/16.0/odoo/models.py", line 5140, in browse
if not ids:
File "/home/odoo/src/odoo/16.0/odoo/tools/query.py", line 217, in __bool__
return bool(self._result)
File "/home/odoo/src/odoo/16.0/odoo/tools/func.py", line 28, in __get__
value = self.fget(obj)
File "/home/odoo/src/odoo/16.0/odoo/tools/query.py", line 210, in _result
self._cr.execute(query_str, params)
File "/home/odoo/src/odoo/16.0/odoo/sql_db.py", line 321, in execute
res = self._obj.execute(query, params)
psycopg2.errors.SyntaxError: memory exhausted at or near ""login""
LINE 1: ...((("res_users"."login" = 'admin') OR ("res_users"."login" = ...
```
in 17.0 without this patch
```
>>> u.search(make_dom(1480))
res.users(2,)
>>> u.search(make_dom(1481))
<shortened output ...>
File "/home/odoo/src/odoo/17.0/odoo/tools/sql.py", line 85, in code
child = stack[-1].send(child)
File "/home/odoo/src/odoo/17.0/odoo/tools/sql.py", line 86, in <genexpr>
if isinstance(child, SQL):
File "/home/odoo/src/odoo/17.0/odoo/tools/sql.py", line 85, in code
child = stack[-1].send(child)
File "/home/odoo/src/odoo/17.0/odoo/tools/sql.py", line 86, in <genexpr>
if isinstance(child, SQL):
File "/home/odoo/src/odoo/17.0/odoo/tools/sql.py", line 85, in code
child = stack[-1].send(child)
File "/home/odoo/src/odoo/17.0/odoo/tools/sql.py", line 86, in <genexpr>
if isinstance(child, SQL):
File "/home/odoo/src/odoo/17.0/odoo/tools/sql.py", line 85, in code
child = stack[-1].send(child)
RecursionError: maximum recursion depth exceeded
```
This issue was observed in upgrades in multiple instances. Example: MRP
produces an OR domain with 2K terms for warehouse sub-locations that
fail.
closesodoo/odoo#153394
Signed-off-by: Raphael Collet <rco@odoo.com>
For very large bits of SQL code with potentially repeated terms, it is
useful to use named parameters instead of positional parameters:
sql = SQL(
"SELECT %(column)s FROM %(table)s WHERE %(column)s IS NOT NULL",
table=SQL.identifier("foo"),
column=SQL.identifier("foo", "bar"),
)
Part-of: odoo/odoo#138019
We introduce a new class of objects to wrap SQL code together with its
parameters. It is designed to be easily composable and to discourage
SQL injections. Its API is similar to the methods of module 'logging':
the code is a format string, and the positional parameters are meant to
be merged into it using the string formatting operator.
# default and increment are parameters of the SQL code in first argument
term = SQL("COALESCE(value, %s) + %s", default, increment)
# term can safely be injected into another SQL, besides regular parameters
query = SQL("SELECT %s FROM mytable WHERE id = %s", term, id_)
The SQL wrapper can return the final SQL code string as query.code, and
the corresponding parameters as query.params (list). The cursor method
execute() can now take an SQL object, and execute it just like
cr.execute(query.code, query.params)
It is quite easy to make SQL objects safe against SQL injections: if the
code is a string literal, then the SQL object is guaranteed safe,
provided the SQL objects within its parameters are themselves safe.
Part-of: odoo/odoo#134677
The function `drop_view_if_exists` only works when the view in question is a
regular view. Here we allow for materialized views to be dropped without any
extra logic added from the caller side.
closesodoo/odoo#121814
X-original-commit: f0db3454bde6759af9aa1b3c2751845a3b2949e7
Signed-off-by: Xavier Morel (xmo) <xmo@odoo.com>
There was two issues regarding the slides public views counter
1. If the `public_views` is set to `NULL` in database,
`increment_fields_skiplock` wasn't properly incrementing the count.
Indeed, in SQL, doing NULL + 1 returns NULL
```sql
16.0=# SELECT NULL + 1;
?column?
----------
(1 row)
```
To have the result we expect, COALESCE must be used
```sql
16.0=# SELECT COALESCE(NULL, 0) + 1;
?column?
----------
1
(1 row)
```
2. There is a mechanism, using the session,
supposed to prevent incrementing the public views
counter when a same user visits multiple times the same slide.
However, since 84d17e57e8
the visited slide was never actually added in the session,
because it was adding the slide id in a copy of the set
in session rather than adding in the set from the session.
Or, as this commit does, to re-assign the new set in the session.
closesodoo/odoo#119370
X-original-commit: fa5962d6f08979017842985004b3ec43416ee181
Signed-off-by: Denis Ledoux (dle) <dle@odoo.com>
17c4f47b0a updated table_kind to return
`pg_class.relkind`, however the semantics are *not* the same, and that
was ignored: `relkind = t` is for *toast* tables, not *temporary*
tables. In `pg_class` the temporary-ness is instead signaled by
`relpersistence` (which applies to both tables and sequences), temp
(and unlogged) tables have `relkind = r`. `existing_tables` does that
correctly, possibly unwittingly.
While at it, upgrade `table_kind` to return an `enum` (whose value is
the old discriminant).
closesodoo/odoo#117444
Related: odoo/enterprise#39185
Signed-off-by: Xavier Morel (xmo) <xmo@odoo.com>
Sometimes a constraint is too complex to be expressed in the general
`_sql_contraint`.
This allows to hook on a constraint if it matches the name in order to
display a user friendly error message.
X-original-commit: e1f06479a526c703ccabc441b1e194646206b966
Part-of: odoo/odoo#106325
Field indexed by trigram cannot become translated because the
`convert_column_translatable` is based on the old index naming convention
(changed in https://github.com/odoo/odoo/pull/100736).
Then it generates a PostgreSQL error:
`psycopg2.errors.DatatypeMismatch: operator class "gin_trgm_ops" does not accept data type jsonb`
closesodoo/odoo#105295
Signed-off-by: Raphael Collet <rco@odoo.com>
For a trigram indexed translated field field_x,
domain leaf ('field_x', '=', value) and ('field_x', 'ilike', pattern)
will miss records if the pattern contains accent characters
This commit set ensure_ascii=False for json.dumps to avoid escaping special
characters
opw-3048182
closesodoo/odoo#105240
X-original-commit: 26b5c8c2baeaee9fcd5384aafa5f9b39543d6903
Signed-off-by: Raphael Collet <rco@odoo.com>
Signed-off-by: Wang Chong (cwg) <cwg@odoo.com>
There are two issues with the index naming convention used by the ORM:
Problem 1: it is possible to have naming conflict for indexes. For
instance, the name 'slide_channel_tag_group_sequence_index' is used for
both fields slide.channel.tag.group.sequence and
slide.channel.tag.group_sequence. Only the first index will be created.
Solution 1: we separate the model and field names with a double
underscore instead of a single one, which is the same strategy as with
LEFT JOIN aliases. This is correct because model names don't contain
such double underscores or underscores as prefix or suffix (it is not
forbiden but model name should follow the 'dot notation'.)
Problem 2: index names can be longer than 63 chars, but PostgreSQL
silently truncates it. This doesn't actually break anything (PostgreSQL
also truncates values when we check the existence of indexes) but it can
lead to using the same name twice. There is hopefully not any example
in our code.
Solution 2: if the name is too large, we truncate it and pad it with a
hash of the complete name to match 63 characters, which is also the
strategy used for LEFT JOIN aliases.
task-2984730
closesodoo/odoo#100736
Related: odoo/upgrade#3957
Signed-off-by: Raphael Collet <rco@odoo.com>
The trigram index function jsonb_path_query_array("column_name", '$.*')::text
uses all translations' representations to build the indexed text. So the
original text needs to be JSON-escaped correctly to match it.
X-original-commit: 7547df664945dddcb839e4903068f7f25ecfc08c
Part-of: odoo/odoo#103031
Translated fields no longer use the model ir.translation. Instead they store
all their values as JSON, and store them into JSONB columns in the model's
table. The field's column value is either NULL or a JSON dict mapping language
codes to text (the field's value in the corresponding language), and must
contain an entry for key 'en_US' (as it is used as a fallback for all other
languages). Empty text is allowed in translation values, but not NULL.
Here are examples for a field with translate=True:
NULL
{"en_US": "Foo"}
{"en_US": "Foo", "fr_FR": "Bar", "nl_NL": "Baz"}
{"en_US": "Foo", "fr_FR": "", "nl_NL": "Baz"}
Like before, writing False to the field makes it NULL, i.e., False in all
languages. However, writing "" to the field makes its value empty in the
current language, but does not discard the values in the other languages.
Here are examples for a field with translate=xml_translate:
NULL
{"en_US": "<div>Foo<p>Bar</p></div>", "fr_FR": "<div>Fou<p>Barre</p></div>"}
Change for callable(translate) fields: one can now write any value in any
language on such a field. The new value will be adapted in all languages, based
on the mapping of terms between languages in the old values. Basically the
structure of the value must remain the same in all languages, like before.
Reading a translated field is now both simpler and faster than the former
implementation. We fetch the value of the field in the current language by
coalescing its value with the 'en_US' value of the field:
SELECT id, COALESCE(name->>'fr_FR', name->>'en_US') AS name ...
The raw cache of the field contains either None or a dict which is conceptually
a subset of the JSON value in database (except for missing languages). For the
sake of simplicity, most cache operations deal with the dict and return the text
value in the current language.
Trigram indexes have been adapted to the new storing strategy, and should enable
to search in any language. Before this change, only the source value of the
field ('en_US') could be indexed.
Computed stored translated fields are not supported by the framework, because of
the complexity of the computation itself: the field would need to be computed in
all active languages. We chose to not provide any hook to compute a field in
all languages at once, and the framework always invokes a compute method once to
recompute it.
Code translations are no longer stored into the database. They become static,
and are extracted from the PO files when needed. The worker simply uses a cache
with extracted code translations for performance. This is reasonable, since
fr_FR code translations for all modules takes around 2MB of memory, and the
cache can be shared among all registries in the worker. Changing code
translations requires to update the corresponding PO file and reloading the
worker(s).
Performance summary:
(+) reading 'model' translated fields is faster
(+) reading 'model_terms' translated fields is much faster (no need to inject
translations into the source value)
(+) searching translated fields with operator 'ilike' is much faster when the
field is indexed with 'trigram'
(+) updating translated fields requires less ORM flushing
(-) importing translations from PO files is 2x slower
Some extra fixes:
- make field 'name' of ir.actions.actions translated; because of the PG
inheritance, this is necessary to make the column definition consistent in
all models that inherit from ir.actions.actions.
- add some backend API for the web/website client for editing translations
- move methods get_field_string() to model ir.model.fields
- move _load_module_terms to model ir.module.module
- adapt tests in test_impex, test_new_api
- because env.lang is injected into SQL queries, its returned value is
now guaranteed to correspond to a valid active language or None
- remove wizard to insert missing translations (no longer makes sense)
task-id: 2081307
Co-authored-by: Fabien Pinckaers <fp@openerp.com>
Co-authored-by: Raphael Collet <rco@odoo.com>
We added trigram index for char fields since https://github.com/odoo/odoo/pull/83015.
But if `unaccent` is installed in the database (and isn't force to
`False` on field), these new trigram indexes are pointless and cost a
lot for nothing (almost nothing, it still can be used for equality
operator but in this case a btree will be far more efficient).
The simple way to fix it is to add `unaccent(<column>)` in the index
trigram definition, but unfortunately `unaccent` is not immutable and
may therefore not be indexed. In order to make `unaccent` indexable, we
must declare it as immutable (see
https://stackoverflow.com/questions/11005036/does-postgresql-support-accent-insensitive-collations/11007216#11007216
for more information and how to do that).
With this patch, trigram indexes are created with `unaccent(<column>)`
if the function `unaccent` is available in the database, and for the
fields that are not declared with `unaccent=False`. Moreover, we issue
a warning when `unaccent` is available but is not immutable, in which
case most trigram indexes will be useless.
odoo/upgrade#3736
task-2551518
closesodoo/odoo#95943
Signed-off-by: Rémy Voet <ryv@odoo.com>
* = blog, forum, slides
For performance reasons, allow to increment multiple fields of the same record
within the same query.
With python tests.
Task-2663320
Part of odoo/odoo#79615
This follows up e045e76e35.
Change the heuristics for ordering columns, as padding is not determined
by column size, but by column alignment inside a row. Because in Odoo a
row always starts with a column of size 4, the following columns should
be the ones aligned on 4 bytes, then the ones aligned on 1 byte, then
the ones aligned on 8 bytes.
The analysis in the commit message of e045e76e was not correct, as it
did not take into account the fact that rows themselves are aligned on 8
bytes. So we have: before each row used 40 bytes (+24b header)
attname | typname | typlen
-------------+-----------+--------
id | int4 | 4
create_uid | int4 | 4
create_date | timestamp | 8
write_uid | int4 | 4 -> 4 bytes padding
write_date | timestamp | 8
active | bool | 1 -> 7 bytes padding
After each row uses 32 bytes (8 bytes saved per row):
attname | typname | typlen
-------------+-----------+--------
id | int4 | 4
create_uid | int4 | 4
write_uid | int4 | 4
active | bool | 1 -> 3 bytes padding
create_date | timestamp | 8
write_date | timestamp | 8
Of course, when more columns are present, the space savings depend on
the alignment of the other columns.
closesodoo/odoo#88084
Signed-off-by: Vincent Schippefilt (vsc) <vsc@odoo.com>
Postgres is aligning columns to 4 or 8 bytes, depending on their type.
So, consecutive fixed-length columns of differing size will be padded
with empty bytes due to the alignment requirements.
Before this patch, columns where created in their definition order. Now,
they are ordered based on their size, in order to minimize the padding.
As an example, before each row uses 36 bytes (+24b header):
attname | typname | typlen
-------------+-----------+--------
id | int4 | 4
create_uid | int4 | 4
create_date | timestamp | 8
write_uid | int4 | 4 -> 4 bytes padding
write_date | timestamp | 8
active | bool | 1 -> 3 bytes padding
After each row uses 32 bytes (4 bytes saved per row):
attname | typname | typlen
-------------+-----------+--------
id | int4 | 4
create_uid | int4 | 4
write_uid | int4 | 4
active | bool | 1 -> 3 bytes padding
create_date | timestamp | 8
write_date | timestamp | 8
This saving scheme applies to all rows in all tables. We save between 4
and 8 bytes per row just on the usual create_uid, create_date,
write_uid, write_date.
closesodoo/odoo#87896
Signed-off-by: Raphael Collet <rco@odoo.com>
The possible index names have been renamed "btree", "btree_not_null"
(instead of "not null") and "trigram" (instead of "gin").
Task 2742526
Part-of: odoo/odoo#83274
Three supported types:
- btree (default for index=True)
- btree not null (when >90% of the data are null)
- gin trigram search (for char fields)
Review of indexes on all objects.
closesodoo/odoo#83015
Signed-off-by: Fabien Pinckaers <fp@odoo.com>
* add configuration for `flake8[flake8-rst-docstring]`
* enable docstring-related checks
* fix invalid docstrings in odoo's core & `base`
* fix a few more bits (mostly missing or incorrect `:param:` info
fields) are out of scope for the lint but my editor catches
closesodoo/odoo#74604
Signed-off-by: Xavier Morel (xmo) <xmo@odoo.com>
When computing the foreign key name,
`check_foreign_keys` didn't take into account the limit of 63 characters
for constraint names.
Because of this, some constraints were dropped and recreated
over and over while they were correct, during install and upgrades.
For instance, when installing `base`
when adding the foreign key for which the name was computed
`base_partner_merge_automatic_wizard_res_partner_rel_base_partner_merge_automatic_wizard_id_fkey`
Postgresql created the constraint under the name
`base_partner_merge_automatic__base_partner_merge_automatic_fkey`
and therefore, as the name did not match,
the constraint was dropped and re-created.
closesodoo/odoo#72234
X-original-commit: 43a4738ebf8a74a389b99f8f58330b3044beaa0c
Signed-off-by: Denis Ledoux (dle) <dle@odoo.com>
Before 1721ec1363 and fdc4ef97c9, Odoo did work fine when the
user had another default schema than 'public'.
This commit restores this behaviour by searching
for existing objects in the user's current schema,
which is the first in the schema search path and
the one used when no schema is specified when
creating objects.
closesodoo/odoo#68144
X-original-commit: 223781b34afacd1c0c5674d395cece6d472b048c
Signed-off-by: Olivier Dony (odo) <odo@openerp.com>
*blog, forum, slides
Based on https://github.com/odoo/odoo/pull/48552#discussion_r440061218 suggestion
Add a new method 'increment_skip_lock' in tools.sql to allow to easily
increment a specific field of 1 if the record is not locked.
The method return boolean if at least 1 record has been incremented.
closesodoo/odoo#58765
X-original-commit: d92e61e89f468c2db2fbd68b2f0d6b36c77f1065
Signed-off-by: Jérémy Kersten (jke) <jke@openerp.com>
Create the table with all the columns from scratch, with the NOT NULL
constraint when required. Also do not call `_check_removed_columns()`
on a new table.
This saves 0.5% of the total installation time.
X-original-commit: 0727cacf5194a143b15ab4cb9893f3035a67be1f
Collisions in table names of ORM models with built-in PG structures such
as "attributes", "domains", "routines", "parameters", ... could occur
and render the result of `table_kind` meaningless.
Based on what was done via https://github.com/odoo/odoo/pull/16651 more
than 2 years ago, it seems relying on our tables being in the 'public'
schema is safe, even though it's only a default from PG. We reuse that
same logic rather than the alternative of excluding
('information_schema', 'pg_catalog', ...), even though it looks safer at
first. If we did the latter we'd have to change the other comparison for
consistency, i.e. more risks.
closesodoo/odoo#42358
X-original-commit: d8e74eb14990c82f65a44ffe163aa84159d6ccc4
Signed-off-by: Denis Vermylen <Icallhimtest@users.noreply.github.com>
This branch is the combination of several optimizations in the ORM:
* store field values once in the cache: the cache reflects more
faithfully the database, only fields that explicitly depend on the
context have an extra indirection in the cache;
* delay recomputations by default: use method `recompute` to explicitly
flush out pending recomputations;
* delay updates in method `write`: updates are stored in a data
structure that can be flushed efficiently to the database with method
`flush` (which also flush out recomputations);
* make method `modified` take advantage of inverse fields to inverse
dependencies;
* filter records by evaluating a domain on records in Python;
* a computed field with `readonly=False` behaves like a normal field
with an onchange method;
* computed fields are computed in superuser mode by default.
Work done by Toufik Ben Jaa, Raphael Collet, Denis Ledoux and Fabien
Pinckaers.
closesodoo/odoo#35659
Signed-off-by: Denis Ledoux <beledouxdenis@users.noreply.github.com>
and use it to determine if the constraint definition was changed. This prevents
endless readding of constraints that are reformatted by postgresql in an
unrecognizable way (in which e.g. "CHECK (credit*debit=0)" becomes
"CHECK ((credit * debit) = 0::numeric)")
Avoid `SELECT *` on `information_schema.columns` because specific access
right restrictions in the context of shared hosting (Heroku, OVH, ...)
might prevent a postgres user to read this field.
Problem: the update of custom models/fields is not fully transactional, and may
potentially lead to an inconsistent database. An other problem is creating two
custom fields by writing on a model: if the second one fails, the first one has
been committed without notice. Retrying the request will give an unexpected
error (duplicate field name).
Solution: never commit in the middle of a request. If the changes have an
impact on the registry, then mark it as invalid (with a new flag), and signal
registry invalidation after everything has been committed. If the request
fails, reset the registry. Both registry and cache invalidation are handled
the same way.
The dictionary `_group_by_full` is replaced by a field parameter `group_expand`
that is assigned to the method name. The API of the method has been simplified
as well:
@api.multi
def _read_group_stage_ids(self, domain, read_group_order=None, access_rights_uid=None):
# the stages are given by self.ids (wrong model);
# read_group_order is the order given to read_group() on self;
# return stages.name_get(), {stage.id: stage.fold)
_group_by_full = {'stage_id': _read_group_stage_ids}
is now written:
stage_id = fields.Many2one(..., group_expand='_read_group_stage_ids')
@api.model
def _read_group_stage_ids(self, stages, domain, order):
# stages is a recordset;
# order is the order to use on stages' model;
# return a recordset which is a superset of stages
Alvaro Fuentes