背景： 开发同事在mogdb 上创建to_char函数索引时报错
问题描述：
ERROR: Functions in index expression must be marked IMMUTABLE
问题原因：
the default attribute of the function is “Volatile”, which can be changed. When creating a function index,
you need to change the attribute of the reference function to “immutable”, that is, stable, and the function index can be created successfully.
That is, only functions with stable attributes can be used to create functional indexes.
解决步骤：
1. 还原场景
创建aaa 数据库， aaa用户，test 测试表。
MogDB=# create database aaa;
CREATE DATABASE
MogDB=# \c aaa
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "aaa" as user "omm".
aaa=# create user aaa password "aaaaaa";
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
gsql -Uaaa -daaa -r
aaa=> create table test (id int, send_time timestamp(0) without time zone);
CREATE TABLE
aaa=> \d test
Table "aaa.test"
Column | Type | Modifiers
-----------+--------------------------------+-----------
id | integer |
send_time | timestamp(0) without time zone |
2. 创建函数索引，复现报错信息。
aaa=> CREATE INDEX idx_test ON aaa.test USING btree (to_char(send_time , 'yyyy')) TABLESPACE pg_default;
ERROR: functions in index expression must be marked IMMUTABLE
3. 使用omm 账户登入aaa 数据库，并创建to_char_immutable 函数。
aaa=# select user;
current_user
--------------
omm
(1 row)
aaa=# 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 FUNCTION
aaa=# create or replace function pg_catalog.to_char_immutable(timestamp without time zone, text) returns text Language internal IMMUTABLE strict as $$timestamp_to_char$$;
CREATE FUNCTION
aaa=# \df to_char_immutable
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+-------------------+------------------+-----------------------------------+--------+------------+------------+---------
pg_catalog | to_char_immutable | text | timestamp with time zone, text | normal | f | f | f
pg_catalog | to_char_immutable | text | timestamp without time zone, text | normal | f | f | f
(2 rows)
4. 创建函数索引
aaa=> select user;
current_user
--------------
aaa
(1 row)
aaa=> CREATE INDEX idx_test ON aaa.test USING btree (to_char_immutable(send_time , 'yyyy')) TABLESPACE pg_default;
CREATE INDEX
函数索引创建成功。
可参考下列链接：
