Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0035605Retail ModulesMulti UPC/EAN for Productpublic2017-03-22 18:132017-10-09 11:17
ReporterJONHM 
Assigned Togorka_gil 
PriorityhighSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionRR18Q1 
Merge Request Status
Review Assigned Tomarvintm
OBNetwork customer
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0035605: Performance issue found caused by trigger obmupc_prod_check_upc_trg
DescriptionIt causes a performance issue when updating a column from M_Product
Steps To Reproduce<ASK FOR THE ENVIRONMENT TO JONHM>

Case 1: with trigger obmupc_prod_check_upc_trg enabled:
update m_product set updated=now() where m_product_id='some_id'
Elapsed time: 4.1 sec

Case 2: with trigger obmupc_prod_check_upc_trg disabled:
update m_product set updated=now() where m_product_id='some_id'
Elapsed time: 12 msec
Proposed SolutionThe solution could be to check if 'upc' field has been changed and execute the query only in that case
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2017-03-22 18:13JONHMNew Issue
2017-03-22 18:13JONHMAssigned To => Retail
2017-03-22 18:13JONHMTriggers an Emergency Pack => No
2017-03-22 18:14JONHMResolution time => 1491948000
2017-03-23 09:03PracticsIssue Monitored: Practics
2017-03-29 17:01adrianromeroStatusnew => scheduled
2017-03-29 17:01adrianromeroAssigned ToRetail => reinaldoguerra
2017-04-04 09:23hgbotCheckin
2017-04-04 09:23hgbotNote Added: 0095818
2017-04-04 09:23hgbotStatusscheduled => resolved
2017-04-04 09:23hgbotResolutionopen => fixed
2017-04-04 09:23hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/1050a329105bfa4d5979cdf204651ec02ad07a64 [^]
2017-04-04 09:23adrianromeroAssigned Toreinaldoguerra => adrianromero
2017-04-04 09:24adrianromeroReview Assigned To => marvintm
2017-04-07 11:07marvintmStatusresolved => closed
2017-04-07 11:07marvintmFixed in Version => RR17Q2
2017-04-07 11:07marvintmFixed in VersionRR17Q2 => RR17Q3
2017-06-02 12:46marvintmResolution time1491948000 => 1498946400
2017-06-02 12:46marvintmAssigned Toadrianromero => Retail
2017-06-02 12:46marvintmStatusclosed => new
2017-06-02 12:46marvintmResolutionfixed => open
2017-06-02 12:46marvintmFixed in VersionRR17Q3 =>
2017-06-02 12:46marvintmNote Added: 0097105
2017-06-02 12:51hgbotCheckin
2017-06-02 12:51hgbotNote Added: 0097106
2017-06-15 11:59ranjith_qualiantech_comAssigned ToRetail => ranjith_qualiantech_com
2017-06-15 11:59ranjith_qualiantech_comStatusnew => scheduled
2017-06-19 16:07ranjith_qualiantech_comAssigned Toranjith_qualiantech_com => Retail
2017-06-19 16:07ranjith_qualiantech_comStatusscheduled => acknowledged
2017-06-28 08:28marvintmResolution time1498946400 => 1499637600
2017-07-06 15:56marvintmResolution time1499637600 => 1500242400
2017-07-13 16:00migueldejuanaAssigned ToRetail => migueldejuana
2017-07-13 16:03migueldejuanaAssigned Tomigueldejuana => Retail
2017-07-13 17:02migueldejuanaResolution time1500242400 => 1500847200
2017-07-18 11:11migueldejuanaResolution time1500847200 => 1501711200
2017-07-18 16:59jorge-garciaAssigned ToRetail => jorge-garcia
2017-07-19 11:16hgbotCheckin
2017-07-19 11:16hgbotNote Added: 0098124
2017-07-19 15:33hgbotCheckin
2017-07-19 15:33hgbotNote Added: 0098137
2017-08-04 14:16marvintmResolution time1501711200 => 1502748000
2017-08-21 08:45marvintmResolution time1502748000 => 1503525600
2017-08-24 09:13marvintmResolution time1503525600 => 1504130400
2017-09-01 13:28marvintmResolution time1504130400 => 1505426400
2017-09-01 13:30PracticsIssue End Monitor: Practics
2017-09-15 12:58marvintmResolution time1505426400 => 1506031200
2017-09-22 11:59marvintmResolution time1506031200 => 1506636000
2017-09-26 12:57marvintmAssigned Tojorge-garcia => gorka_gil
2017-09-29 11:24gorka_gilStatusacknowledged => scheduled
2017-10-05 18:20hgbotCheckin
2017-10-05 18:20hgbotNote Added: 0099779
2017-10-05 18:20hgbotStatusscheduled => resolved
2017-10-05 18:20hgbotResolutionopen => fixed
2017-10-05 18:20hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/1050a329105bfa4d5979cdf204651ec02ad07a64 [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/5f8ec1c34bdbcc5d957110f113548d41c44e74da [^]
2017-10-09 11:17marvintmStatusresolved => closed
2017-10-09 11:17marvintmFixed in Version => RR18Q1

Notes
(0095818)
hgbot   
2017-04-04 09:23   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 1050a329105bfa4d5979cdf204651ec02ad07a64
Author: Adrián Romero <adrianromero <at> openbravo.com>
Date: Mon Apr 03 19:19:37 2017 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/1050a329105bfa4d5979cdf204651ec02ad07a64 [^]

Fixes issue 0035605: Performance issue found caused by trigger obmupc_prod_check_upc_trg

---
M src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
---
(0097105)
marvintm   
2017-06-02 12:46   
Fix seems not to be correct in Oracle, as its causing a mutating trigger error.
(0097106)
hgbot   
2017-06-02 12:51   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 1292df0e34a8e20e2edb4121d4e141540616b1f7
Author: Miguel de Juana <miguel.dejuana <at> openbravo.com>
Date: Fri Jun 02 12:49:10 2017 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/1292df0e34a8e20e2edb4121d4e141540616b1f7 [^]

Related issue 0035605: Performance issue found caused by trigger obmupc_prod_check_upc_trg

- Fix seems not to be correct in Oracle, as its causing a mutating trigger error.
Backed out changeset 1050a329105b

---
M src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
---
(0098124)
hgbot   
2017-07-19 11:16   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: af89dbff980191344982c147b2ee6d377cdbe905
Author: Jorge Garcia <jorge.garcia <at> openbravo.com>
Date: Wed Jul 19 11:15:49 2017 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/af89dbff980191344982c147b2ee6d377cdbe905 [^]

Related issue 0035605: Performance issue found caused by trigger obmupc_prod_check_upc_trg

Re-apply code to check the fix in our current CI.

---
M src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
---
(0098137)
hgbot   
2017-07-19 15:33   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 3524321e78bfbaa0212f803dd38344726f8685e3
Author: Jorge Garcia <jorge.garcia <at> openbravo.com>
Date: Wed Jul 19 15:32:17 2017 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/3524321e78bfbaa0212f803dd38344726f8685e3 [^]

Related issue 0035605: Performance issue found caused by trigger obmupc_prod_check_upc_trg

Backed out changeset af89dbff9801

---
M src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
---
(0099779)
hgbot   
2017-10-05 18:20   
Repository: erp/pmods/org.openbravo.multiupc
Changeset: 5f8ec1c34bdbcc5d957110f113548d41c44e74da
Author: Gorka Gil <gorka.gil <at> openbravo.com>
Date: Thu Oct 05 18:03:54 2017 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/5f8ec1c34bdbcc5d957110f113548d41c44e74da [^]

Fixed issue 35605: Performance issue found caused by trigger obmupc_prod_check_upc_trg

Remove the trigger and created an event handler

Details:
- The old trigger was slow because do a join of m_product and obmupc_prod_multiupc
to check that there is no duplicates in all the table
- First aproach of the fix was to instead do a statement trigger do a for each row,
and only check if the upc that we are adding exists already
- It has the problem that in Oracle is not possible to do a select in the same table
that we are updating with a trigger for each row, gives error mutating trigger

Other discarded options:
- First idea was to deprecate the upc column in m_product and use only multiupc table,
optionally handle with triggers to have syncronized the upc in m_product with the upc
in multiupc table. This was discarded becasue is a big functional change, and means
change how is working for the past 4 years.
- Second idea was to create a index without duplicates in m_product. In multiupc
already has that index. So the trigger will be very simple to check in the other table
of the one that we are inserting. This was not possible because was needed to create a
unique index that accepts nulls. But this works in oracle only for one column, not for
two or more. Found a workaround:
create unique index EM_OBMUPC_PROD_UPC_IDX on m_product (
   case when upc is null then
     null
   else
     ad_client_id
   end,
   upc
) ;
But it creates db inconsistencies when export from pg.
- Other option is to use expecial triggers of oracle, but are not supported by
dbsourcemaner, also needed to check if exists in postgres.

---
A src/org/openbravo/multiupc/event/NoDuplicateUpcHandler.java
R src-db/database/model/triggers/OBMUPC_CHECK_UPC_TRG.xml
R src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
---