Message 64907

Author
ced
Date
2021-02-27.11:45:03
Message id
64907

Content

On 2021-02-27 11:12, Nicolas √Čvrard wrote:
> >> I have made the proposal in the review which is kind of a mix of all the ideas in this issue.
> >>
> >> - Definition of the index in a specific attribute in \_\_setup\_\_
> >
> >I do not think it is correct to store a backend specific object on
> >the Model.
> 
> I don't see a problem with that.
> It's not like we could change from one backend to another easily.

It just makes the object unpractical.
The Model definition can not depend on the backend. It must be the same
no matter the backend because it is an abstraction. Otherwise a model is
linked to a specific backend.

> >Indeed by looking at syntax of both backends they share almost the
> >same syntax/parameters.
> 
> SQLite is like the minimum of index creation.
> I also looked at MariaDB and Oracle's version of CREATE INDEX (that's
> why 'WHERE' is in the kwargs instead of the arguments). The similarity
> of the syntax is kind of an artefact of SQLite simplicity.

There is no point to have arguments that depend on the backend. They can
never be used when defining a Model because it does not depend on the
backend.

> >I think that some of the PG parameter are too specific to require our
> >support like:
> >
> >    - CONCURRENTLY: not really useful for trytond-admin as it is not
> >      supposed to be concurrent
> >    - ONLY: not supported by all PG version and only useful for
> >      partitioned table which could exist only if the DBA create the
> >      schema and so he could create the index also
> >    - INCLUDE: could be general argument that is ignored by sqlite
> >    - WITH: it seems to me that it is very specific and only DBA could
> >      choose them.
> >
> >So I think we could have an object in modelsql.py like the Constraint
> >that store the index definition to be passed to the table handler.
> 
> First I think that the TableHandler class is already way too big. It's
> why I chose to make another class.

I do not think it simplify anything to move two methods from one class
to another in the same file.

> Then the thing is that if we ever support another database there might
> by another parameter that become useful (eg FULLTEXT for MariaDB) so I
> opted for the strict minimum in the arguments and the rest in the
> kwargs rather than a kind of rosetta stone that tries to cover
> some use cases. Mainly because having the kwargs prefixed with the
> database name allows to know which feature is enabled for which
> backend (instead of not supporting it somehow silently).

The goal is to have an abstraction on index definition. This is not an
abstraction if you have to care about which parameter are used by which
backend etc.

> >The only missing point is to index method to use. We could probably
> >use the PG name and just make SQL support only btree and hash.
> 
> Methods are there for PG. SQLite do not support those AFAIK.

SQLite supports only one type of index. I think it is a kind of btree so
it works for equality and sort.
But I really think it is not good to have to define the method to use
but we must define which properties we need for the index and the
backend should choose the type that works the best.

> >> - Allow to specify some backend specific customization of the index
> >
> >For me as long as a DBA can modify (drop/create using the same name) it
> >for fine tuning, I think we should not need to support too much
> >specific.
> 
> To be honest I thought that only USING / WHERE might be used sometimes
> but since it was not difficult to add the others I thought that I
> would do so so that we can decide.

We must work the other way, define a minimal requirement and then
expand.

> >I would say that only the method is a specific that is good to
> >support but it is mainly related to the kind of operation for which
> >the index it created.
> 
> I don't understand. Are you saying that you don't think the people
> should specify the method but they should rather define the operation?

Indeed, I can see this kind of operations:

    - equality
    - range (<, >, etc)
    - begin pattern
    - 2d or vector
    - multi (inverted)

For postgresql it would result in:

    ['equality'] -> HASH
    ['equality', 'range'] -> BTREE
    ['begin pattern'] -> BTREE
    ['vector'] -> GIST
    ['multi'] -> GIN

Now if we have ['range', 'vector'], this may be difficult to choose. We
have three options:

    - create no index
    - create multi index for each group of cases
    - create one index as best effort

I think we should do the last one, just find the type of index that
provide the most of operations (+ internal preferences for equality ex:
we prefer BTREE over HASH).
History
Date User Action Args
2021-02-27 11:45:03cedsetrecipients: + yangoon, nicoe, albertca, pokoli, jcavallo, reviewbot
2021-02-27 11:45:03cedlinkissue5757 messages
2021-02-27 11:45:03cedcreate

Showing 10 items. Show all history (warning: this could be VERY long)