标签
PostgreSQL , Oracle , 函数结果缓存 , 函数三态 , immutable , stable , volatile
背景
Oracle 11g 支持的一个新特性，在创建PL/SQL函数时，可以指定这个存储过程是否需要对结果进行缓存，缓存内容在SGA内存区域。
如果这个函数的输入参数未变化，同时指定的表数据没有发生变化，那么缓存有效，直接从缓存中获取结果。
详见
https://www.oracle.com/technetwork/testcontent/o57plsql-088600.html
In Oracle Database 11g, however, we can add a line to the header of this function as follows:
FUNCTION one_employee (employee_id_in
IN employees.employee_id%TYPE)
RETURN employees%ROWTYPE
RESULT_CACHE RELIES_ON (employees)
IS
l_employee employees%ROWTYPE;
BEGIN
RESULT_CACHE 表示这个函数支持结果缓存，当输入参数未变化时，直接从缓存获取结果。
relies_on (employees)表示，当employees表未变化时，缓存有效，当这个表有变化，整个缓存全部失效。
通常用在OLAP业务系统中，用于缓存结果。
This RESULT_CACHE clause tells Oracle Database that it should remember (store in a special in-memory result cache) each record retrieved for a specific employee ID number. And when a session executes this function and passes in an employee ID that was previously stored, the PL/SQL runtime engine will not execute the function body, which includes that query.
Instead, it will simply retrieve the record from the cache and return that data immediately. The result is much faster retrieval.
In addition, by specifying RELIES_ON (employees), we inform Oracle Database that if any session commits changes to that table, any data in the result cache drawn from the table must be invalidated. The next call to the one_employee function would then have to execute the query and retrieve the data fresh from the table.
Because the cache is a part of the System Global Area (SGA), its contents are available to all sessions connected to the instance. Furthermore, Oracle Database will apply its "least recently used algorithm" to the cache, to ensure that the most recently accessed data will be preserved in the cache.
Prior to Oracle Database 11g, a similar kind of caching was possible with package-level collections, but this cache is session-specific and located in the Process Global Area (PGA). This means that if I have 1,000 different sessions running the application, I could use up an enormous amount of memory in addition to that consumed by the SGA.
The PL/SQL function result cache minimizes the amount of memory needed to cache and share this data across all sessions. This low memory profile, plus the automatic purge of cached results whenever changes are committed, makes this feature of Oracle Database 11g very practical for optimizing performance in PL/SQL applications.
PostgreSQL 函数 稳定性 - 并非cache
在一个语句中函数被多次调用时，如果函数被多次调用，并且输入的参数为常量（不变时），这个函数需要被执行多少次？
postgres=# create or replace function f1(int) returns int as $$
declare begin raise notice '1'; return 1; end;
$$ language plpgsql strict stable;
CREATE FUNCTION
postgres=# select f1(1) from generate_series(1,5);
NOTICE: 1
NOTICE: 1
NOTICE: 1
NOTICE: 1
NOTICE: 1
f1
----
1
1
1
1
1
(5 rows)
postgres=# create or replace function f1(int) returns int as $$
declare begin raise notice '1'; return 1; end;
$$ language plpgsql strict immutable;
CREATE FUNCTION
postgres=# select f1(1) from generate_series(1,5);
NOTICE: 1
f1
----
1
1
1
1
1
(5 rows)
postgres=# create or replace function f1(int) returns int as $$
declare begin raise notice '1'; return 1; end;
$$ language plpgsql strict stable;
CREATE FUNCTION
postgres=# explain verbose select f1(1) from generate_series(1,5);
QUERY PLAN
------------------------------------------------------------------------------------
Function Scan on pg_catalog.generate_series (cost=0.00..216.94 rows=1000 width=4)
Output: f1(1)
Function Call: generate_series(1, 5)
(3 rows)
postgres=# create or replace function f1(int) returns int as $$
declare begin raise notice '1'; return 1; end;
$$ language plpgsql strict immutable;
CREATE FUNCTION
postgres=#
postgres=# explain verbose select f1(1) from generate_series(1,5);
NOTICE: 1
QUERY PLAN
----------------------------------------------------------------------------------
Function Scan on pg_catalog.generate_series (cost=0.00..0.19 rows=1000 width=4)
Output: 1
Function Call: generate_series(1, 5)
(3 rows)
原理详见本文末尾PostgreSQL函数三态的介绍。
会话级语句结果缓存，暂时PG内核层面没有支持，可以通过pgpool-ii这类中间件来实现。
参考
https://www.oracle.com/technetwork/testcontent/o57plsql-088600.html
《PostgreSQL Oracle 兼容性之 - PL/SQL DETERMINISTIC 与PG函数稳定性(immutable, stable, volatile)》
《PostgreSQL 函数稳定性与constraint_excluded分区表逻辑推理过滤的CASE》
《函数稳定性讲解 - retalk PostgreSQL function's [ volatile|stable|immutable ]》
《函数稳定性讲解 - 函数索引思考, pay attention to function index used in PostgreSQL》
《函数稳定性讲解 - Thinking PostgreSQL Function's Volatility Categories》
