Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0012019Openbravo ERPA. Platformpublic2010-01-20 15:022010-01-23 00:00
Reportermtaal 
Assigned Tomtaal 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Versionpi 
Target VersionpiFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0012019: Postgres error: tuple concurrently updated, caused by call using sessionConfig value
DescriptionWhen a user calls many webservices in a short time then he/she encounters a various moments a 'tuple concurently updated' exception. See below. This happens when the sessionConfig is executed which for postgresql is this:
bbdd.sessionConfig=select update_dateFormat('DD-MM-YYYY')

The update_dateFormat SP replaces the dateFormat SP with a new implementation. It does this by creating/replacing the existing function. This create/replace is done at every connection creation.

Proposed solution:
- for postgres hardcode a call "select update_dateFormat('DD-MM-YYYY')" when the application starts (in a context listener or something)
- clear the sessionConfig value in openbravo.properties (and the template) for postgres
- create a new update_dateformat SP and clear the old one (let it not change the dateformat definition). This means that existing OB instances will call a harmless SP
- when the application starts perform once: select update_dateFormat('DD-MM-YYYY', where DD-MM-YYYY is read from the darformat.sql present in Openbravo.properties


An example stack trace:

Dec 3, 2008 9:17:59 AM org.apache.catalina.startup.Catalina start
INFO: Server startup in 25839 ms
org.postgresql.util.PSQLException: ERROR: tuple concurrently updated
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:236)
    at org.openbravo.database.OpenbravoDriverManagerConnectionFactory.executeDefaultSQL(OpenbravoDriverManagerConnectionFactory.java:67)
    at org.openbravo.database.OpenbravoDriverManagerConnectionFactory.createConnection(OpenbravoDriverManagerConnectionFactory.java:56)
    at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
    at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:771)
    at org.apache.commons.dbcp.PoolingDriver.connect(PoolingDriver.java:175)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:207)
    at org.openbravo.database.ConnectionProviderImpl.getConnection(ConnectionProviderImpl.java:210)
    at org.openbravo.database.ConnectionProviderImpl.getPreparedStatement(ConnectionProviderImpl.java:274)
    at org.openbravo.database.ConnectionProviderImpl.getPreparedStatement(ConnectionProviderImpl.java:264)
    at org.openbravo.database.CPStandAlone.getPreparedStatement(CPStandAlone.java:75)
    at org.openbravo.uiTranslation.TextInterfacesData.selectText(TextInterfacesData.java:267)
    at org.openbravo.uiTranslation.TextInterfacesData.selectText(TextInterfacesData.java:58)
    at org.openbravo.uiTranslation.TranslationUtils.retrieveLabelData(TranslationUtils.java:185)
    at org.openbravo.uiTranslation.TranslationUtils.processFormLabels(TranslationUtils.java:33)
    at org.openbravo.uiTranslation.TranslationHandler.processTranslations(TranslationHandler.java:212)
    at org.openbravo.uiTranslation.TranslationHandler.generateTranslations(TranslationHandler.java:114)
    at org.openbravo.xmlEngine.XmlDocument.print(XmlDocument.java:305)
    at org.openbravo.xmlEngine.XmlDocument.print(XmlDocument.java:251)
    at org.openbravo.erpCommon.security.Login.printPageWelcome(Login.java:112)
    at org.openbravo.erpCommon.security.Login.doPost(Login.java:54)
    at org.openbravo.base.HttpBaseServlet.doGet(HttpBaseServlet.java:275)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
    at org.openbravo.base.HttpBaseServlet.service(HttpBaseServlet.java:239)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.utils.SessionExpirationFilter.doFilter(SessionExpirationFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.utils.CharsetFilter.doFilter(CharsetFilter.java:27)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.dal.core.DalRequestFilter$1.doAction(DalRequestFilter.java:83)
    at org.openbravo.dal.core.ThreadHandler.run(ThreadHandler.java:48)
    at org.openbravo.dal.core.DalRequestFilter.doFilter(DalRequestFilter.java:89)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
    at java.lang.Thread.run(Thread.java:619)
Steps To Reproduce
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
has duplicate defect 0011635 closed mtaal Postgresql fail when query thru rest 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2010-01-20 15:02mtaalNew Issue
2010-01-20 15:02mtaalAssigned To => alostale
2010-01-20 15:02mtaalOBNetwork customer => No
2010-01-20 15:06mtaalAssigned Toalostale => mtaal
2010-01-20 15:30mtaalNote Added: 0023620
2010-01-20 15:31mtaalStatusnew => scheduled
2010-01-20 15:31mtaalNote Added: 0023621
2010-01-20 15:31mtaalfix_in_branch => pi
2010-01-20 16:27quizavariIssue Monitored: quizavari
2010-01-20 17:36hgbotCheckin
2010-01-20 17:36hgbotNote Added: 0023626
2010-01-20 17:36hgbotStatusscheduled => resolved
2010-01-20 17:36hgbotResolutionopen => fixed
2010-01-20 17:36hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/8513eb2035b9d812057971796e9533a2d1378e16 [^]
2010-01-22 17:05hudsonbotCheckin
2010-01-22 17:05hudsonbotNote Added: 0023754
2010-01-22 20:39shuehnerNote Added: 0023770
2010-01-22 20:39shuehnerStatusresolved => closed
2010-01-23 00:00anonymoussf_bug_id0 => 2937525
2010-02-12 08:28alostaleRelationship addedhas duplicate 0011635

Notes
(0023620)
mtaal   
2010-01-20 15:30   
A different and easier solution is to change the update_dateformat database function to update when the dateformat has changed, so change the definition to this:

DECLARE
BEGIN
  IF (dateformat() <> format) THEN
  EXECUTE 'CREATE OR REPLACE FUNCTION dateFormat() RETURNS VARCHAR AS '' DECLARE BEGIN RETURN ''''' || format || '''''; EXCEPTION WHEN OTHERS THEN RETURN NULL; END; '' LANGUAGE ''plpgsql'' IMMUTABLE; ';
  RETURN 'dateFormat modified';
  END IF;
  RETURN 'dateFormat not modified';
END;
(0023621)
mtaal   
2010-01-20 15:31   
Working on it
(0023626)
hgbot   
2010-01-20 17:36   
Repository: erp/devel/pi
Changeset: 8513eb2035b9d812057971796e9533a2d1378e16
Author: Martin Taal <martin.taal <at> openbravo.com>
Date: Wed Jan 20 17:36:00 2010 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/8513eb2035b9d812057971796e9533a2d1378e16 [^]

fixes issue 12019: Postgres error: tuple concurrently updated, caused by call using sessionConfig value

---
M src-db/database/model/prescript-PostgreSql.sql
---
(0023754)
hudsonbot   
2010-01-22 17:05   
A changeset related to this issue has been promoted to main after passing a series of tests and an OBX has been generated:

Changeset: http://code.openbravo.com/erp/devel/main/rev/8513eb2035b9 [^]
Merge Changeset: http://code.openbravo.com/erp/devel/main/rev/ba83bb4d9240 [^]
Tests: http://builds.openbravo.com/view/devel-int/ [^]
OBX: http://builds.openbravo.com/erp/obx/core/OpenbravoERP-2.50CI.15962.obx [^]
(0023770)
shuehner   
2010-01-22 20:39   
tested with concurrent read queries to Country entity. without patch error shows up immediately. With patch survives 50 requests at concurrency 10 without problems.