背景
在从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或者修改表字段类型，然后就建立了这样的函数索引，尤其是与日期相关的字段。
下面是一个经典的不合理用法
create table test_table(id int,b date);
create index test_table_i1 on test_table(b);
insert into test_table values (1,to_date('2023-07-11','yyyy-mm-dd'));
select * from test_table where to_char(b,'yyyy-mm-dd')='2023-07-11';
像这个查询sql，就用不上对于字段b的索引，正确的sql应该为
select * from test_table where b=date'2023-07-11';
或
select * from test_table where b=to_date('2023-07-11','yyyy-mm-dd')
即，在索引字段所在的这一侧，不要通过函数去转换，因为数据库在函数转换前，不知道函数会转换成什么值，就只能把表里这列所有的值都转换一次，然后再去匹配条件另一侧的值。按照正确的sql改写方式，则会将右侧一个确定的值通过索引去进行检索，能更快地返回所需要的记录。
当开发不愿意修改SQL时，就会在表上再创建一个函数索引，比如
create index test_table_i2 on test_table(to_char(b,'yyyy-mm-dd'));
此时那个不合理用法，就也可以使用上索引了。
但这种索引，其实是将转换后的值，额外再存储了一列，所以，一旦出现有某种函数，在不同的环境变量下，存储的值是可能会不一样的，就会出现数据和索引不一致的情况，下面举个很多人会认为是BUG的例子。
ORACLE支持to_char函数作为函数索引所带来的问题
SQL> select sessiontimezone from dual; --查询当前会话时区
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00
SQL> create table test_func_index_t2 (a timestamp with local time zone); --建表
Table created
SQL> insert into test_func_index_t2 values (systimestamp); --插入一行数据
1 row inserted
SQL> SELECT * FROM test_func_index_t2;
A
--------------------------------------------------------------------------------
11-JUL-23 10.03.15.282534 PM
SQL> CREATE INDEX I1_test_func_index_t2 ON test_func_index_t2(TO_CHAR(A,'YYYY-MM-DD HH24')); --创建函数索引
Index created
SQL> SELECT TO_CHAR(A, 'YYYY-MM-DD HH24'), A FROM test_func_index_t2 t;
TO_CHAR(A,'YYYY-MM-DDHH24') A
--------------------------- --------------------------------------------------------------------------------
2023-07-11 22 11-JUL-23 10.03.15.282534 PM
SQL> SELECT /*+ full(t) */ TO_CHAR(A, 'YYYY-MM-DD HH24'), A FROM test_func_index_t2 t WHERE TO_CHAR(A, 'YYYY-MM-DD HH24') = '2023-07-11 22';--使用全表扫描查询
TO_CHAR(A,'YYYY-MM-DDHH24') A
--------------------------- --------------------------------------------------------------------------------
2023-07-11 22 11-JUL-23 10.03.15.282534 PM
SQL> SELECT /*+index(t I1_test_func_index_t2)*/ TO_CHAR(A, 'YYYY-MM-DD HH24'), A FROM test_func_index_t2 t WHERE TO_CHAR(A, 'YYYY-MM-DD HH24') = '2023-07-11 22';--使用索引扫描查询
TO_CHAR(A,'YYYY-MM-DDHH24') A
--------------------------- --------------------------------------------------------------------------------
2023-07-11 22 11-JUL-23 10.03.15.282534 PM
SQL> ALTER SESSION SET TIME_ZONE='+00:00'; --修改会话时区，下面的查询都不再修改时区
Session altered
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00
SQL> SELECT /*+ full(t) */ TO_CHAR(A, 'YYYY-MM-DD HH24'), A FROM test_func_index_t2 t WHERE TO_CHAR(A, 'YYYY-MM-DD HH24') = '2023-07-11 22'; --全表查询查不到
TO_CHAR(A,'YYYY-MM-DDHH24') A
--------------------------- --------------------------------------------------------------------------------
SQL> SELECT /*+index(t I1_test_func_index_t2)*/ TO_CHAR(A, 'YYYY-MM-DD HH24'), A FROM test_func_index_t2 t WHERE TO_CHAR(A, 'YYYY-MM-DD HH24') = '2023-07-11 22';--索引扫描可以查到
TO_CHAR(A,'YYYY-MM-DDHH24') A
--------------------------- --------------------------------------------------------------------------------
2023-07-11 22 11-JUL-23 02.03.15.282534 PM
SQL> SELECT TO_CHAR(A, 'YYYY-MM-DD HH24'), A FROM test_func_index_t2 t ; --不带条件可以查到，但查询结果的第一个字段，和上面索引扫描不一致
TO_CHAR(A,'YYYY-MM-DDHH24') A
--------------------------- --------------------------------------------------------------------------------
2023-07-11 14 11-JUL-23 02.03.15.282534 PM
SQL>
从上面这个例子中可以看到，在Oracle数据库里，对于同一个表的同一条记录，在环境变量确定的情况下，使用完全相同的查询条件，有时能查到，有时不能查到，而且to_char函数所返回的值，对于同一个确定的入参，返回的结果都可能不一样！如果开发人员随意使用函数索引，极有可能引起数据混乱！至于函数索引所带来的存储问题，本文不再提及，存储问题对于应用开发人员来说，感知不大。
无论如何就是要，怎么处理？
前文已说过，不建议使用函数索引，但如果就是要用，那么该如何处理呢？
答案是，再建一个自定义函数，标记为"immutable"，并且对应的sql中，where 条件里也改成使用这个自定义函数
CREATE OR REPLACE FUNCTION pg_catalog.to_char2(timestamp without time zone, text)
RETURNS text
LANGUAGE internal
immutable STRICT NOT FENCED SHIPPABLE
AS $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的优先级还要高。于是，我们可以创建一个这样的函数
CREATE OR REPLACE FUNCTION whale.to_char(timestamp without time zone, text)
RETURNS text
LANGUAGE internal
immutable STRICT NOT FENCED SHIPPABLE
AS $function$timestamp_to_char$function$;
然后用常规的方式去创建函数索引，就会自动使用到whale下的这个函数了。当然，使用不当造成本文中oracle出现的那个异常数据，只能由开发人员自己负责了。
后记
在做国产数据库替代时，大家经常会发现oracle/mysql这些数据库中设计不合理的一些地方，那么国产数据库是应该去"兼容"这些不合理的行为？还是应该坚守初心，打造一个各方面设计都合理的产品？当然这种问题不是非黑即白，如何把握好其中的度，会是每个国产数据库都要去考虑的事。
