Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0042574Openbravo ERPI. Performancepublic2019-12-11 17:192019-12-18 12:11
Reportersalvador_campanella 
Assigned ToAtulOpenbravo 
PriorityurgentSeveritycriticalReproducibilitysometimes
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Versionpi 
Target Version3.0PR19Q3.3Fixed in Version3.0PR19Q3.3 
Merge Request Status
Review Assigned Todmiguelez
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0042574: The m_explodebomnotstock function creates locks in m_pricelist table causing big performance problems
DescriptionThe m_explodebomnotstock function creates locks in m_pricelist table causing big performance problems


Extract of Postgres log
-----------------------
2019-12-09 10:30:15 CST 2019-12-09 09:56:43 CST 19347 11/3630307 2777469593 LOG: process 19347 acquired ShareLock on transaction 2777459601 after 1013158.638 ms
2019-12-09 10:30:15 CST 2019-12-09 09:56:43 CST 19347 11/3630307 2777469593 CONTEXT: while locking tuple (534,3) in relation "m_pricelist"
    SQL statement "SELECT C_ORDER.c_order_id, C_ORDER.c_bpartner_id, c_order.M_PriceList_ID, c_orderline.explode
                                                                        FROM C_ORDER JOIN m_pricelist ON c_order.m_pricelist_id = m_pricelist.m_pricelist_id JOIN c_orderline ON c_order.c_order_id = c_orderline.c_order_id
        WHERE c_orderline.c_orderline_id=v_Record_ID FOR UPDATE"
    PL/pgSQL function m_explodebomnotstock(character varying,character varying) line 73 at SQL statement
    SQL statement "SELECT M_EXPLODEBOMNOTSTOCK(null, CUR_BOM_Line.c_orderline_ID)"
    PL/pgSQL function c_order_post1(character varying,character varying,character varying) line 1176 at PERFORM
    SQL statement "SELECT C_ORDER_POST1($1, $2, 'Y')"
    PL/pgSQL function c_order_post1(character varying,character varying) line 3 at PERFORM
    SQL statement "SELECT C_ORDER_POST1(p_PInstance_ID, NULL)"
    PL/pgSQL function c_order_post(character varying) line 17 at PERFORM
Steps To Reproducen/a
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
blocks defect 0042521 closed AtulOpenbravo The m_explodebomnotstock function creates locks in m_pricelist table causing big performance problems 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2019-12-16 17:19AtulOpenbravoTypedefect => backport
2019-12-16 17:19AtulOpenbravoTarget Version => 3.0PR19Q3.3
2019-12-16 17:19AtulOpenbravoNote Added: 0116361
2019-12-18 12:08hgbotCheckin
2019-12-18 12:08hgbotNote Added: 0116398
2019-12-18 12:08hgbotCheckin
2019-12-18 12:08hgbotNote Added: 0116400
2019-12-18 12:08hgbotStatusscheduled => resolved
2019-12-18 12:08hgbotResolutionopen => fixed
2019-12-18 12:08hgbotFixed in SCM revision => http://code.openbravo.com/erp/backports/3.0PR19Q3.3/rev/8645d2244ee93719b5e126ed0348301078d05314 [^]
2019-12-18 12:11dmiguelezReview Assigned To => dmiguelez
2019-12-18 12:11dmiguelezNote Added: 0116402
2019-12-18 12:11dmiguelezStatusresolved => closed
2019-12-18 12:11dmiguelezFixed in Version => 3.0PR19Q3.3

Notes
(0116361)
AtulOpenbravo   
2019-12-16 17:19   
Test Plan:

Go to Product Window and create a new record with:
  - Organization: *
  - SearchKey: Test
  - Name: Test
  - Bill of Materials: Yes
  - Stocked: No
Go to Bill Of Materials Tab and create two records with:
  - Product: Bebida energética | Quantity: 1
  - Product: Agua sin gas | Quantity: 1
Go to Price Tab and create a new record with:
  - Price List Version: Tarifa de ventas
  - Unit Price: 10
  - List Price: 10

Go to Sales Order Window and create a new record with:
  - Organization: F&B España - Región Norte
  - Business Partner: Alimentos y Supermercados
Go to Lines Tab and create a new record with:
  - Product: Test
  - Ordered Quantity: 1

With a terminal, log in into psql using the same database as the Openbravo instance that is running
For example: psql -U tad -d openbravo -h localhost
Write:
  - begin;
  - select * from c_order_post1(null, 'ORDER_ID'); -- where order_id is the id of the order created previously

In Openbravo, go to Sales Order Window and create a new record with:
  - Organization: F&B España - Región Norte
  - Business Partner: Alimentos y Supermercados
Go to Lines Tab and create a new record with:
  - Product: Cerveza Ale
  - Ordered Quantity: 1
Book it. The process finishes successfully

Go to the previously created order and Book it.
The process is stuck because it is blocked by the previous session. This is the expected behavior
(0116398)
hgbot   
2019-12-18 12:08   
Repository: erp/backports/3.0PR19Q3.3
Changeset: f2bf2a6fb19585bcea6b02a8e123db0ee8b55621
Author: David Miguelez <david.miguelez <at> openbravo.com>
Date: Wed Dec 18 11:23:08 2019 +0100
URL: http://code.openbravo.com/erp/backports/3.0PR19Q3.3/rev/f2bf2a6fb19585bcea6b02a8e123db0ee8b55621 [^]

Related to Issue 42574. Partial fix of Issue 40823 must be included in order
to be compatible with the changes done to solve the proper issue.

The changes included are to avoid using functions that are deprecated.

---
M src-db/database/model/functions/M_EXPLODEBOMNOTSTOCK.xml
M src-db/database/model/functions/M_INVEXPLODEBOMNOTSTOCK.xml
---
(0116400)
hgbot   
2019-12-18 12:08   
Repository: erp/backports/3.0PR19Q3.3
Changeset: 8645d2244ee93719b5e126ed0348301078d05314
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Wed Dec 18 11:26:19 2019 +0100
URL: http://code.openbravo.com/erp/backports/3.0PR19Q3.3/rev/8645d2244ee93719b5e126ed0348301078d05314 [^]

Fixes BUG-42574:Solves problem with lock in pricelist table

  * Remove join to pricelist table, that is not used, from the query
  * Remove extra 'For Update' sentence, as the document and document tables
    are alredy blocked by the first 'For Update' statement

---
M src-db/database/model/functions/M_EXPLODEBOMNOTSTOCK.xml
M src-db/database/model/functions/M_INOUTEXPLODEBOMNOTSTOCK.xml
M src-db/database/model/functions/M_INVEXPLODEBOMNOTSTOCK.xml
---
(0116402)
dmiguelez   
2019-12-18 12:11   
Code Review + Testing Ok