0029943: support to define DB functions volatility - Openbravo Issue Tracking System

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0029943

Openbravo ERP

Y. DBSourceManager

public

2015-05-20 13:28

2019-04-02 17:57

Reporter

vmromanos

Assigned To

alostale

Priority

normal

Severity

major

Reproducibility

always

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

Fixed in Version

3.0PR19Q2

Merge Request Status

Review Assigned To

caristu

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0029943: support to define DB functions volatility

Description

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 http://www.postgresql.org/docs/9.1/static/sql-createfunction.html [^] :
"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: http://wiki.openbravo.com/wiki/Projects:Configurable_DB_Functions_Volatility [^]

Steps To Reproduce

Proposed Solution

Additional Information

Tags

Performance

Relationships

related to	defect	0038179		closed	alostale	cannot restore pg dump
related to	feature request	0030179	3.0PR15Q4	closed	AugustoMauch	DBSourceManager support indexes with functions
related to	defect	0029664	3.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