Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0029943Openbravo ERPY. DBSourceManagerpublic2015-05-20 13:282019-04-02 17:57
0029943: support to define DB functions volatility
Right now the db functions created by the dbsm have the annotation VOLATILE.

It would be great to support also the STABLE keyword, which returns always the same result when the same parameters are passed.

Definition from [^] :
"STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. (It is inappropriate for AFTER triggers that wish to query rows modified by the current command.) Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction."

This flag could be used in some common functions to drastically increase the performance of processes where these functions are involved. Example of candidate functions: ad_isorgincluded, ad_org_isinnaturaltree, etc.

See project: [^]
related to defect 0038179 closed alostale cannot restore pg dump 
related to feature request 00301793.0PR15Q4 closed AugustoMauch DBSourceManager support indexes with functions 
related to defect 00296643.0PR15Q3 closed vmromanos Create Price List process is slow 
blocks defect 0039349 closed AtulOpenbravo Return from Customer pick/edit lines process takes more time to be loaded on an environment with thousands of orders. 
blocks defect 0040865 closed AtulOpenbravo Set VOLATILITY LEVEL for Database functions 
Issue History
2015-05-20 13:28vmromanosNew Issue
2015-05-20 13:28vmromanosAssigned To => platform
2015-05-20 13:28vmromanosModules => Core
2015-05-20 13:28vmromanosTriggers an Emergency Pack => No
2015-05-20 13:36vmromanosDescription Updatedbug_revision_view_page.php?rev_id=8592#r8592
2015-05-20 13:36vmromanosTag Attached: Performance
2015-05-20 15:53vmromanosRelationship addedrelated to 0029664
2015-05-21 18:34shuehnerIssue Monitored: shuehner
2015-05-21 18:34shuehnerNote Added: 0077633
2018-10-19 08:09vmromanosRelationship addedblocks 0039349
2019-03-05 14:04alostaleRelationship addedrelated to 0038179
2019-03-20 13:40alostaleRelationship addedrelated to 0030179
2019-04-02 09:45alostaleSummaryAdd STABLE annotation support to db functions => support to define DB functions volatility
2019-04-02 09:46alostaleReview Assigned To => caristu
2019-04-02 09:46alostaleAssigned Toplatform => alostale
2019-04-02 09:46alostaleDescription Updatedbug_revision_view_page.php?rev_id=18567#r18567
2019-04-02 09:57hgbotCheckin
2019-04-02 09:57hgbotNote Added: 0110960
2019-04-02 09:57hgbotStatusnew => resolved
2019-04-02 09:57hgbotResolutionopen => fixed
2019-04-02 09:57hgbotFixed in SCM revision => [^]
2019-04-02 09:58hgbotCheckin
2019-04-02 09:58hgbotNote Added: 0110961
2019-04-02 09:58hgbotFixed in SCM revision [^] => [^]
2019-04-02 17:57caristuNote Added: 0110969
2019-04-02 17:57caristuStatusresolved => closed
2019-04-02 17:57caristuFixed in Version => 3.0PR19Q2
2019-05-13 18:16AtulOpenbravoRelationship addedblocks 0040865

2015-05-21 18:34   
I think that may happen as part of adding support 'Functional indices' (tentatively thought of q4)

As you can only use functions for functional indices which marked as stable (at leasT)
2019-04-02 09:57   
Repository: erp/devel/pi
Changeset: d6ffa58fa832a1ca1e43767282a009e2c7ff5ab1
Author: Asier Lostalé <asier.lostale <at>>
Date: Tue Apr 02 09:57:08 2019 +0200
URL: [^]

fixes 29943: support to define DB functions volatility

M src-db/database/lib/dbsourcemanager.jar
2019-04-02 09:58   
Repository: erp/devel/dbsm-main
Changeset: 7e0ba43031c75b49de7b3d6dd70c2112fe9f8607
Author: Asier Lostalé <asier.lostale <at>>
Date: Tue Apr 02 09:54:41 2019 +0200
URL: [^]

fixes 29943: support to define DB functions volatility

M .classpath
M .settings/org.eclipse.jdt.core.prefs
M build.xml
M src-test/model/functions/SIMPLE_FUNCTION.xml
M src-test/src/log4j2-test.xml
M src-test/src/org/openbravo/dbsm/test/base/
M src-test/src/org/openbravo/dbsm/test/model/
M src/mapping.xml
M src/org/apache/ddlutils/io/
M src/org/apache/ddlutils/model/
M src/org/apache/ddlutils/platform/
M src/org/apache/ddlutils/platform/
M src/org/apache/ddlutils/platform/oracle/
M src/org/apache/ddlutils/platform/oracle/
M src/org/apache/ddlutils/platform/postgresql/
M src/org/apache/ddlutils/platform/postgresql/
A src-test/model/functions/IMMUTABLE_FUNCTION.xml
A src-test/model/functions/STABLE_FUNCTION.xml
A src-test/src/org/openbravo/dbsm/test/model/
A src/org/apache/ddlutils/io/
2019-04-02 17:57   
Reviewed as part of the project[1]

[1] [^]