背景
在从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这些数据库中设计不合理的一些地方,那么国产数据库是应该去"兼容"这些不合理的行为?还是应该坚守初心,打造一个各方面设计都合理的产品?当然这种问题不是非黑即白,如何把握好其中的度,会是每个国产数据库都要去考虑的事。