PostgreSQL中IMMUTABLE函数能否访问表，生产环境使用是否安全？

核心问题结论 #

1. 技术上是否允许访问表的IMMUTABLE函数存在？ #

PostgreSQL不会对函数的实际逻辑和 volatility 标签的匹配性做强制校验，所以你写的访问表的IMMUTABLE函数不会直接报错，测试场景下也可能得到正确结果，但这是违反官方约定的使用方式。

2. 能保证表数据完全不变的情况下是否可用？ #

如果你可以100%确保以下所有条件完全满足，可以有限使用：

• 函数依赖的所有表（你的场景下是customer、com_code）永远不会执行任何DML操作（增删改）
• 上述表的结构永远不会变更，不会修改、删除关联字段
• 所有使用该函数的业务场景，都能接受表数据变更后（哪怕是误操作）函数返回错误结果的风险

3. 生产环境使用是否安全？ #

极不推荐在生产环境使用这种不符合规范的IMMUTABLE函数，隐藏风险极高，包括但不限于：

• 常量参数的函数调用结果会被PostgreSQL在查询规划阶段直接替换为常量值，后续表数据变更后，相同入参的调用会一直返回旧值，这类问题非常隐蔽，很难排查
• 如果你用该函数创建表达式索引，索引数据会和表数据完全脱节，导致所有走该索引的查询返回错误结果
• 后续维护人员不知道你隐含的表不可变约定，只要修改了表数据就会引发业务故障

高性能替代方案 #

如果你是为了获得IMMUTABLE带来的性能收益，可以选择更安全的实现方式：

• 如果com_code是完全不变的码表，可以直接把码值映射关系硬编码到IMMUTABLE函数中，不需要查表，完全符合官方规范
• 对于必须查表的场景，使用STABLE标签即可，你的测试函数逻辑中用到的查询都是走主键索引，性能和IMMUTABLE函数的差距极小
• 如果需要用该函数创建表达式索引，可以先把依赖的表设置为只读（通过权限控制或触发器禁止DML），确认无变更风险后再使用IMMUTABLE标签，同时要在函数注释中标明依赖的表不可变的约定

测试代码参考 #

测试表初始化脚本 #

drop table if exists customer;
create table customer (
cust_no        numeric not null,
cust_nm        character varying(100),
register_date  timestamp(0),
register_dt    varchar(8),
cust_status_cd varchar(1),
register_channel_cd varchar(1),
cust_age       numeric(3),
active_yn      varchar(1),
sigungu_cd     varchar(5),
sido_cd        varchar(2)
);
insert into customer
select i, chr(65+mod(i,26))||i::text||'CUST_NM'
     , current_date - mod(i,10000)
     , to_char((current_date - mod(i,10000)),'yyyymmdd') as register_dt
     , mod(i,5)+1 as cust_status_cd
     , mod(i,3)+1 as register_channel_cd
     , trunc(random() * 100) +1 as age
     , case when mod(i,22) = 0 then 'N' else 'Y' end as active_yn
     , case when mod(i,1000) = 0 then '11007'
            when mod(i,1000) = 1 then '11006'
            when mod(i,1000) = 2 then '11005'
            when mod(i,1000) = 3 then '11004'
            when mod(i,1000) = 4 then '11003'
            when mod(i,1000) = 5 then '11002'
            else '11001' end                  as sigungu_cd
      , case when mod(i,3) = 0 then '01'
             when mod(i,3) = 1 then '02'
             when mod(i,3) = 2 then '03' end as sido_cd
  from generate_series(1,1000000) a(i);
ALTER TABLE customer ADD CONSTRAINT customer_pk
  PRIMARY KEY (cust_no);

create table com_code (
group_cd  varchar(10),
cd        varchar(10),
cd_nm     varchar(100));
insert into com_code values ('G1','11001','SEOUL')
                           ,('G1','11002','PUSAN')
                           ,('G1','11003','INCHEON')
                           ,('G1','11004','DAEGU')
                           ,('G1','11005','JAEJU')
                           ,('G1','11006','ULEUNG')
                           ,('G1','11007','ETC');
insert into com_code values ('G2','1','Infant')
                           ,('G2','2','Child')
                           ,('G2','3','Adolescent')
                           ,('G2','4','Adult')
                           ,('G2','5','Senior');
insert into com_code values ('G3','01','Jeonbuk')
                           ,('G3','02','Kangwon')
                           ,('G3','03','Chungnam');
alter table com_code add constraint com_code_pk
  primary key (group_cd, cd);

STABLE函数创建脚本 #

CREATE OR REPLACE FUNCTION F_GET_NM_STABLE (
    p_cust_no  IN NUMERIC)
RETURNS VARCHAR
LANGUAGE PLPGSQL STABLE
AS
$$
DECLARE
  v_out_name  VARCHAR(100);
BEGIN
  SELECT B.CD_NM
    INTO v_out_name
    FROM CUSTOMER A LEFT JOIN COM_CODE B
      ON (A.SIGUNGU_CD = B.CD
          AND A.CUST_NO = p_cust_no)
   WHERE B.GROUP_CD = 'G1';
  RETURN v_out_name;
END;
$$

不符合规范的IMMUTABLE函数创建脚本 #

CREATE OR REPLACE FUNCTION F_GET_NM_IMMUTABLE (
    p_cust_no  IN NUMERIC)
RETURNS VARCHAR
LANGUAGE PLPGSQL IMMUTABLE
AS
$$
DECLARE
  v_out_name  VARCHAR(100);
BEGIN
  SELECT B.CD_NM
    INTO v_out_name
    FROM CUSTOMER A LEFT JOIN COM_CODE B
      ON (A.SIGUNGU_CD = B.CD
          AND A.CUST_NO = p_cust_no)
   WHERE B.GROUP_CD = 'G1';
  RETURN v_out_name;
END;
$$

函数测试脚本 #

SELECT SIGUNGU_CD, F_GET_NM_STABLE(CUST_NO)
  FROM CUSTOMER
 WHERE CUST_NO BETWEEN 1 AND 5;
SELECT SIGUNGU_CD, F_GET_NM_IMMUTABLE(CUST_NO)
  FROM CUSTOMER
 WHERE CUST_NO BETWEEN 1 AND 5;

内容的提问来源于stack exchange，提问作者JAEGEUN YU