Update database after the last update on dev

Hi,

After the last update of the dev when I launch the command:

./trytond/bin/trytond-admin -c trytond.conf -vvv --dev -d dev -m

I have the following error

...
psycopg2.ProgrammingError: ERREUR:  la colonne a.action n'existe pas
LINE 1: SELECT "a"."id" AS "id", "a"."action" AS "action", "a"."acti...
...

do I have to manually create the ‘action’ field in the ir_trigger table?

Normally not. I think you did not run an update an --all modules.

Yes, I didn’t do the --all, I thought I would do it after …

During the --all another error :

21595 139874552612672 [2020-04-06 09:57:35,061] DEBUG trytond.backend.postgresql.database b'ALTER TABLE "product_product" ADD CONSTRAINT "product_product_code_exclude" EXCLUDE ("code" WITH =) WHERE (("active" = true) AND ("code" != \'\'))'
Traceback (most recent call last):
  File "./trytond/bin/trytond-admin", line 23, in <module>
    admin.run(options)
  File "/home/xt0ph/Documents/technique/tryton/clients/coppernic/ate/trytond/trytond/admin.py", line 53, in run
    activatedeps=options.activatedeps)
  File "/home/xt0ph/Documents/technique/tryton/clients/coppernic/ate/trytond/trytond/pool.py", line 162, in init
    lang=lang, activatedeps=activatedeps)
  File "/home/xt0ph/Documents/technique/tryton/clients/coppernic/ate/trytond/trytond/modules/__init__.py", line 422, in load_modules
    _load_modules(update)
  File "/home/xt0ph/Documents/technique/tryton/clients/coppernic/ate/trytond/trytond/modules/__init__.py", line 392, in _load_modules
    load_module_graph(graph, pool, update, lang)
  File "/home/xt0ph/Documents/technique/tryton/clients/coppernic/ate/trytond/trytond/modules/__init__.py", line 218, in load_module_graph
    cls.__register__(module)
  File "/home/xt0ph/Documents/technique/tryton/clients/coppernic/ate/trytond/trytond/modules/product/product.py", line 311, in __register__
    super().__register__(module)
  File "/home/xt0ph/Documents/technique/tryton/clients/coppernic/ate/trytond/trytond/model/modelsql.py", line 307, in __register__
    table.add_constraint(ident, constraint)
  File "/home/xt0ph/Documents/technique/tryton/clients/coppernic/ate/trytond/trytond/backend/postgresql/table.py", line 431, in add_constraint
    % (self.table_name, ident, constraint), constraint.params)
  File "/home/xt0ph/Documents/technique/tryton/clients/coppernic/ate/trytond/trytond/backend/postgresql/database.py", line 68, in execute
    cursor.execute(self, sql, args)
psycopg2.IntegrityError: ERREUR:  n'a pas pu créer la contrainte d'exclusion « product_product_code_exclude »
DETAIL:  La clé (code)=(123) est en conflit avec la clé (code)=(123).

It seems you have two products with the same code which is not allowed on newer versions.

You should update the product code to make it unique.

Yes indeed, thank you.

Just got hit with this trying to upgrade to 5.6.

This is extremely unfortunate for a modern system… we have somewhere between 2-3% of our articles (roughly 500) with duplicate codes, though mostly blank codes. This is like forcing us back a decade. There is already a unique ‘id’.

What is the real justification for this?
The constraint could/should be put on multiple fields as opposed to only the product code.

For example we use the product code only for product codes of a brand name (like PRESTO or IDEAL STANDARD), otherwise only the supplier/distributor codes are set and product code remains blank. It is more than possible that differing products may have the same code but different brands and/or product types.

IMHO it goes against to the argument I’ve always read in the Tryton community from my earliest days about giving a solution with minimal restrictions.
In fact unique keys have been removed from many models along versions.
Having an unique code is not a common requirement.

Blank codes are excluded from the constraint. So you can have as many blanks as you want.

The code is now considered the SKU.
The code had been always considered as unique identifier for the product.

Then you should set the product code on the supplier information as this is the code of the supplier not your code on the system. There is no constraint there so you can enter as much duplicates as you want.

Contraints are added only when needed. There is also a unique code for parties.

Why not? If you use a code to identify a product you should set a unique one.

There is now a sequence that will automatically set the code for new products. So you have multiple options here:

  • Do not use codes by setting the value as blank
  • Generate the code using a sequence
  • Use the universal code of this product (GTIN, etc).
  • Set an unique code manually

The product code is neither an SKU (unique stock keeping unit) nor a UPC (or EAN or IAN) unique product code. They should be in other optional fields (which can be made obligatory on a site basis with very little effort).

This is against both best practices and common sense.

Then you should set the product code on the supplier information as this is the code of the supplier not your code on the system.

This is not logical as suppliers typically have their own article codes, but the article supplied is the same.

Ok So it makes senses to me.

That’s the improvement we did on the new release. Make the code a SKU.
For EAN and other codes that should be validated there are the product identifiers.

This will produce data duplication. I think you should think the other way arround: If you need an optional code with other requirements just add a field which mets your requirements.

I beg to disagree.

An SKU is optional and must be unique. It is even recommended not to have one if not truly necessary.
One could always implement an SKU generator, if need be.

There is no real data duplication as, truly, an SKU is best when independent of enterprise, manufacturer or supplier product codes…

Please simply create an optional attribute ‘inventory code’ on product (which is what the SKU is) and don’t force the hand on internal business process decisions.

All of this statments are already implemented on Tryton.

Feel free to create any optional attributes you need on your customization modules.

What I disagree with is to use the product number (code) as an SKU when it most certainly is not one, forcing what is in effect retail or POS oriented kludges on those who neither want nor need that. Especially given that it is relatively simple to provide the SKU in a modular manner.

This is a very high impact change that we consider a regression.
This same issue is the reason we aborted using SAGE Gestion Commercial almost a decade ago.

In other words, if not using a modular approach by creating an inventory code (SKU) attribute and managing it separately following best practices (nomenclature, by warehouse and/or POS), at least make the constraint creation optional but configurable for those who consciously want to activate it.

Production (and materials) management is complex enough without artificially increasing the burden upon finite human resources.

The new product code system is nice and very flexible except unique code requirement, IMHO.
At first, there is backward compatibility problem. Some users who for some reasons used to use duplicate codes must not only rewrite codes in database but also must change their product coding system, retrain users, reprint leaflets etc.
At second, the strongest side of Tryton is the flexibility. If someone want to use duplicate codes, why not? For example, the record name “[White] IPhone 11” is more user and customer friendly than “[125468774455White] IPhone 11”, isn’t?
My dilettant’s idea - maybe it’s possible to add into tryton.config file a new key a la “we_dont_need_unique_code” and test it before creating product code constraint?

You can do that by creating a fake constraint with the same name. But I do not recommend it as other code may rely on this uniqueness.

Thank you, nice idea, I’ll try. The extendibility of Tryton still have many terre incognite for me.

May rely but may not rely. Or related code may be not need for us. As minimum we get some time for comfort life with duplicated codes.

Such code is certainly erroneous, once the product is selected in either sales or purchase or even a logistics reception of goods, it is forcibly the id (which is already unique because it is selected) that should be used and not the code.
Otherwise, as already indicated, if such a unique ‘code’ is needed as SKU, create that special unique attribute (e.g. inventory code or SKU).

Wouldn’t renaming this field solve the misunderstanding?

Of course the issue is that previously people did not use the code as the SKU, we would need to find something to solve this for them.

Wouldn’t it be much simpler (and more user friendly) to follow the proposal of risto3?

  • revert this backward incomatible change
  • create a new unique field SKU (or even better create the unique identifier SKU for products)

→ no renaming of the field needed, no upgrade worries, usage of identifiers for what they are meant