Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0042573Openbravo ERPI. Performancepublic2019-12-11 17:192019-12-18 12:11
Reportersalvador_campanella 
Assigned ToAtulOpenbravo 
PriorityurgentSeveritycriticalReproducibilitysometimes
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Versionpi 
Target Version3.0PR19Q4.1Fixed in Version3.0PR19Q4.1 
Merge Request Status
Review Assigned Todmiguelez
OBNetwork customerGold
Web browser
ModulesCore
Support ticket13607
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0042573: 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.0PR19Q4.1
2019-12-16 17:19AtulOpenbravoNote Added: 0116360
2019-12-18 12:07hgbotCheckin
2019-12-18 12:07hgbotNote Added: 0116397
2019-12-18 12:07hgbotStatusscheduled => resolved
2019-12-18 12:07hgbotResolutionopen => fixed
2019-12-18 12:07hgbotFixed in SCM revision => http://code.openbravo.com/erp/backports/3.0PR19Q4.1/rev/41b7178a6bd7d9d0e32e1b0f49096473e82f3fa5 [^]
2019-12-18 12:11dmiguelezReview Assigned To => dmiguelez
2019-12-18 12:11dmiguelezNote Added: 0116403
2019-12-18 12:11dmiguelezStatusresolved => closed
2019-12-18 12:11dmiguelezFixed in Version => 3.0PR19Q4.1

Notes
(0116360)
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
(0116397)
hgbot   
2019-12-18 12:07   
Repository: erp/backports/3.0PR19Q4.1
Changeset: 41b7178a6bd7d9d0e32e1b0f49096473e82f3fa5
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Wed Dec 18 10:30:37 2019 +0530
URL: http://code.openbravo.com/erp/backports/3.0PR19Q4.1/rev/41b7178a6bd7d9d0e32e1b0f49096473e82f3fa5 [^]

Fixes BUG-42573: 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
---
(0116403)
dmiguelez   
2019-12-18 12:11   
Code Review + Testing Ok