PostgreSQL中IMMUTABLE函数在存储过程中结果缓存的疑问

问题场景 #

创建存储常量的表和获取常量的函数：

CREATE TABLE constants (key varchar PRIMARY KEY, value varchar);

CREATE OR REPLACE FUNCTION get_constant(_key varchar) RETURNS varchar
AS $$ SELECT value FROM constants WHERE key = _key; $$ LANGUAGE sql
IMMUTABLE;

插入初始常量：

insert into constants(key, value)
values('const', '1');

修改constants表中const的value后，直接调用select get_constant('const');能返回正确的新值，但在如下存储过程中调用该函数时，结果始终是首次调用的旧值，只有重新编译存储过程才能获取新值：

create or REPLACE PROCEDURE etl.test()
LANGUAGE plpgsql
AS $$
declare  
begin
   raise notice '%', etl.get_constant('const');
END $$;

原因分析 #

问题出在get_constant函数的**IMMUTABLE稳定性标记**上：

• PostgreSQL中，IMMUTABLE函数被定义为「结果仅由输入参数决定，完全不依赖数据库状态」，PostgreSQL会对这类函数进行极致优化——在编译调用它的对象（比如存储过程、视图、复杂查询）时，会直接将函数的执行结果硬编码到调用者的执行计划中，后续调用时不再执行函数或查询数据表。
• 直接调用函数时，由于每次调用都是独立的即时执行，PostgreSQL会重新执行函数查询表；但存储过程在首次创建/编译时，就把get_constant('const')的结果固化到了自身的执行逻辑里，后续调用不会再去查询constants表。

解决方案 #

将get_constant的稳定性级别改为**STABLE**，因为该函数的结果依赖数据库表的状态，同一事务内结果稳定，但不同事务中可能随表数据变化而改变：

CREATE OR REPLACE FUNCTION get_constant(_key varchar) RETURNS varchar
AS $$ SELECT value FROM constants WHERE key = _key; $$ LANGUAGE sql
STABLE;

修改后，存储过程每次调用时都会执行函数查询数据表，获取最新的常量值，无需重新编译。

相关文档说明 #

PostgreSQL的函数稳定性级别定义：

• IMMUTABLE：函数结果仅由输入参数决定，与数据库状态无关。PostgreSQL可自由缓存其结果，甚至在编译阶段将其替换为常量值。
• STABLE：函数在同一事务内返回的结果稳定，但不同事务中若数据库状态变化，结果可能改变。PostgreSQL会在同一事务内缓存结果，但不会在编译调用者时硬编码结果。
• VOLATILE：函数每次调用结果都可能变化，即使输入参数相同，PostgreSQL不会缓存其结果。

内容的提问来源于stack exchange，提问作者Vladislav Zhilmanov