functions in index expression must be marked IMMUTABLE

背景

再通过 MTK工具做Oracle 向 MogDB/openGauss 迁移的过程中，发现有一条索引创建失败

MogDB=# create index on t(to_char(ctime,'yyyyMMdd'));
ERROR:  functions in index expression must be marked IMMUTABLE

解析

这个索引是在timestamp with time zone类型的字段上做了to_char()函数转换，也就是一个函数索引，在PG里执行此操作会有相同的报错，但是在oracle里是可以执行成功的
PG14.5

oracle 19c

原因是在PG里，创建函数索引所引用的函数必须是IMMUTABLE的，而函数默认是VOLATILE

IMMUTABLE（不可变的）：对于给定的参数值总是会返回相同的值，不能修改数据库
STABLE（稳定的） ：在一次表扫描中对于相同的参数值将返回相同的结果，不能修改数据库
VOLATILE（不稳定的） ：在一次表扫描中对于相同的参数值都有可能得到不同的结果

而MogDB/openGauss 是在PG9.2.4的基础上做的国产化开发，所以也继承了这个特性。

方法

处理这个问题一般有两种方法： 修改函数属性 和 新构建一个函数
to_char是STABLE属性的数据库内部函数，直接通过命令alter function xxx IMMUTABLE; 修改是不合适的，所以这里重新创建一个IMMUTABLE函数

--查看函数
MogDB=# \sf to_char(timestamp with time zone, text)
CREATE OR REPLACE FUNCTION pg_catalog.to_char(timestamp with time zone, text)
 RETURNS text
 LANGUAGE internal
 STABLE STRICT NOT FENCED SHIPPABLE
AS $function$timestamptz_to_char$function$;

--创建函数
create or replace function pg_catalog.to_char_immutable(timestamp with time zone, text) returns text Language internal IMMUTABLE strict as $$timestamptz_to_char$$;

create or replace function pg_catalog.to_char_immutable(timestamp without time zone, text) returns text Language internal IMMUTABLE strict as $$timestamp_to_char$$;

--检查函数
MogDB=# \sf to_char_immutable(timestamp with time zone, text)
CREATE OR REPLACE FUNCTION pg_catalog.to_char_immutable(timestamp with time zone, text)
 RETURNS text
 LANGUAGE internal
 IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
AS $function$timestamptz_to_char$function$;