Projects:Configurable DB Functions Volatility/Specs

Contents 1 Introduction 2 Oracle vs PostgreSQL 3 Backwards compatibility 4 Implementation

Introduction

In PostgreSQL, functions can define different volatility levels, begin them VOLATILE, STABLE and IMMUTABLE.

By default, unless differently specified, functions are VOLATILE. This is the only volatility level that currently (PR19Q2) Openbravo supports.

Defining STABLE and IMMUTABLE functions, when it makes sense, allows the planner to execute those functions less often, making those queries faster.

This project will allow dbsm to support this feature.

Oracle vs PostgreSQL

Oracle does not implement anything equivalent to PostgreSQL stable functions.

PostgreSQL	Oracle
Volatile (default)	Default
Stable	N/A
Immutable	Deterministic

Backwards compatibility

Previously there was no indicator about function volatility, being all of them volatile. This behavior will be kept so that if no volatility is indicated, volatile will be used.

Implementation

A new attribute in function xml definition will be added to indicate if the function is stable or immutable. For volatile functions, this attribute will not be exported maintaining in this way backward consistency.

Stable functions in Oracle will be created as default functions adding a comment to keep consistency.