当前位置：首页 > news >正文

【openGauss】从“functions in index expression must be marked IMMUTABLE“谈起

news 2026/5/12 16:01:52

背景

在从oracle迁移到openGauss中，创建函数索引的时候，偶尔会出现此类报错

functions in index expression must be marked IMMUTABLE

大概意思是,函数索引里的函数，必须是“IMMUTABLE”的，如果不是"IMMUTABLE",比如是“STABLE”,就会出现这个报错。
其中最常见的就是使用to_char/to_date这两个与日期有关的函数，而此限制，在原生PG中也同样存在。
本篇就来说说，为什么to_char()不能在openGauss/postgresql中作为函数索引，并且会举Oracle中的一个反常识的现象来进行举证,说明使用此类函数作为索引所造成的风险。

为什么要使用函数索引？

首先，函数索引无论在哪个数据库中，都不是推荐用法，往往是应用开发过程中，开发人员没有遵循最优的表结构设计以及SQL编写规则，在表的数据量积累到一定阶段时，SQL性能变慢，而不愿意去修改SQL或者修改表字段类型，然后就建立了这样的函数索引，尤其是与日期相关的字段。
下面是一个经典的不合理用法

createtabletest_table(idint,bdate);createindextest_table_i1ontest_table(b);insertintotest_tablevalues(1,to_date('2023-07-11','yyyy-mm-dd'));select*fromtest_tablewhereto_char(b,'yyyy-mm-dd')='2025-12-10';

像这个查询sql，就用不上对于字段b的索引，正确的sql应该为

select*fromtest_tablewhereb=date'2025-12-10';

或

select*fromtest_tablewhereb=to_date('2025-12-10','yyyy-mm-dd')

即，在索引字段所在的这一侧，不要通过函数去转换，因为数据库在函数转换前，不知道函数会转换成什么值，就只能把表里这列所有的值都转换一次，然后再去匹配条件另一侧的值。按照正确的sql改写方式，则会将右侧一个确定的值通过索引去进行检索，能更快地返回所需要的记录。
当开发不愿意修改SQL时，就会在表上再创建一个函数索引，比如

createindextest_table_i2ontest_table(to_char(b,'yyyy-mm-dd'));

此时那个不合理用法，就也可以使用上索引了。
但这种索引，其实是将转换后的值，额外再存储了一列，所以，一旦出现有某种函数，在不同的环境变量下，存储的值是可能会不一样的，就会出现数据和索引不一致的情况，下面举个很多人会认为是BUG的例子。

ORACLE支持to_char函数作为函数索引所带来的问题

下面这个测试是在oracle 21c环境中运行出来的效果

SQL>selectsessiontimezonefromdual;--查询当前会话时区SESSIONTIMEZONE---------------------------------------------------------------------------+08:00SQL>createtabletest_func_index_t2(atimestampwithlocaltimezone);--建表TablecreatedSQL>insertintotest_func_index_t2values(systimestamp);--插入一行数据1rowinsertedSQL>SELECT*FROMtest_func_index_t2;A--------------------------------------------------------------------------------11-JUL-2310.03.15.282534PMSQL>CREATEINDEXI1_test_func_index_t2ONtest_func_index_t2(TO_CHAR(A,'YYYY-MM-DD HH24'));--创建函数索引IndexcreatedSQL>SELECTTO_CHAR(A,'YYYY-MM-DD HH24'),AFROMtest_func_index_t2 t;TO_CHAR(A,'YYYY-MM-DDHH24')A--------------------------- --------------------------------------------------------------------------------2025-12-102210-DEC-2310.03.15.282534PMSQL>SELECT/*+ full(t) */TO_CHAR(A,'YYYY-MM-DD HH24'),AFROMtest_func_index_t2 tWHERETO_CHAR(A,'YYYY-MM-DD HH24')='2025-12-10';--使用全表扫描查询TO_CHAR(A,'YYYY-MM-DDHH24')A--------------------------- --------------------------------------------------------------------------------2025-12-102210-DEC-2310.03.15.282534PMSQL>SELECT/*+index(t I1_test_func_index_t2)*/TO_CHAR(A,'YYYY-MM-DD HH24'),AFROMtest_func_index_t2 tWHERETO_CHAR(A,'YYYY-MM-DD HH24')='2025-12-10';--使用索引扫描查询TO_CHAR(A,'YYYY-MM-DDHH24')A--------------------------- --------------------------------------------------------------------------------2025-12-102210-DEC-1010.03.15.282534PMSQL>ALTERSESSIONSETTIME_ZONE='+00:00';--修改会话时区，下面的查询都不再修改时区SessionalteredSQL>selectsessiontimezonefromdual;SESSIONTIMEZONE---------------------------------------------------------------------------+00:00SQL>SELECT/*+ full(t) */TO_CHAR(A,'YYYY-MM-DD HH24'),AFROMtest_func_index_t2 tWHERETO_CHAR(A,'YYYY-MM-DD HH24')='2025-12-10';--全表查询查不到TO_CHAR(A,'YYYY-MM-DDHH24')A--------------------------- --------------------------------------------------------------------------------SQL>SELECT/*+index(t I1_test_func_index_t2)*/TO_CHAR(A,'YYYY-MM-DD HH24'),AFROMtest_func_index_t2 tWHERETO_CHAR(A,'YYYY-MM-DD HH24')='2025-12-10';--索引扫描可以查到TO_CHAR(A,'YYYY-MM-DDHH24')A--------------------------- --------------------------------------------------------------------------------2025-12-102210-DEC-1002.03.15.282534PMSQL>SELECTTO_CHAR(A,'YYYY-MM-DD HH24'),AFROMtest_func_index_t2 t;--不带条件可以查到，但查询结果的第一个字段，和上面索引扫描不一致TO_CHAR(A,'YYYY-MM-DDHH24')A--------------------------- --------------------------------------------------------------------------------2025-12-101410-DEC-1002.03.15.282534PMSQL>

从上面这个例子中可以看到，在ORACLE数据库里，对于同一个表的同一条记录，在环境变量确定的情况下，使用完全相同的查询条件，有时能查到，有时不能查到，而且to_char函数所返回的值，对于同一个确定的入参，返回的结果都可能不一样！如果开发人员随意使用函数索引，极有可能引起数据混乱！至于函数索引所带来的存储问题，本文不再提及，存储问题对于应用开发人员来说，感知不大。

无论如何就是要，怎么处理？

前文已说过，不建议使用函数索引，但如果就是要用，那么该如何处理呢？
答案是，再建一个自定义函数，标记为"immutable"，并且对应的sql中，where 条件里也改成使用这个自定义函数

CREATEORREPLACEFUNCTIONpg_catalog.to_char2(timestampwithouttimezone,text)RETURNStextLANGUAGEinternal immutable STRICTNOTFENCED SHIPPABLEAS$function$timestamp_to_char$function$;

但显然，本来就是因为不愿意改sql，才用的函数索引，可是由于函数同名会冲突，要改函数名，sql里就得跟着改，又绕回去了。。。

然而，此问题并非无解，此时就要提到openGauss的插件框架了。
https://gitee.com/opengauss/Plugin
在openGauss中，有两个比较特殊的插件，分别是dolphin和whale。其中dolphin能实现的能力，在我以前的文章也有介绍过，我只能用太强了来描述其对MYSQL的兼容性；另外一个whale，目前社区版本没有什么内容，但框架是在的。openGauss的商业发行版之一，MogDB,就在whale插件中做了大量的oracle兼容特性。
这个插件框架能做到什么？从dolphin来看，它甚至可以覆盖原有数据库自带的数据类型、语法、操作符、函数等，那么whale里同样可以做到，其原理之一就是内置的search_path，插件的schema比内置pg_catalog的优先级还要高。于是，我们可以创建一个这样的函数

CREATEORREPLACEFUNCTIONwhale.to_char(timestampwithouttimezone,text)RETURNStextLANGUAGEinternal immutable STRICTNOTFENCED SHIPPABLEAS$function$timestamp_to_char$function$;