ERROR: Functions in index expression must be marked IMMUTABLE

今天在创建函数索引时遇到报错，报错信息即为标题，弄了很久，百思不得其解，后来向德哥咨询了下，很快就搞定了，下面是详细信息。

1 表定义

1
2
3
4
5
6
7

skytf=> /d test_39;  
 Table "skytf.test_39"  
 Column | Type | Modifiers  
-------------+--------------------------+-----------  
skyid | integer |  
create_time | timestamp with time zone |  
name | character varying(32) |

现在需要在字段 “skyid”, “create_time” 上创建联合索引。

2 创建索引

1
2

skytf=> create index CONCURRENTLY idx_test_skyid_ctime on test_39 using btree (skyid, to_char(create_time, 'YYYY-MM-DD') );  
ERROR: functions in index expression must be marked IMMUTABLE

创建函数索引报错，”functions in index expression must be marked IMMUTABLE” ，意思为建立函数索引时 函数必须标识为 “IMMUTABLE”。

3 查看 to_char 函数

1
2
3
4
5
6
7
8
9
10
11
12
13

skytf=> /df to_char();  
 List of functions  
 Schema | Name | Result data type | Argument data types | Type  
------------+---------+------------------+-----------------------------------+--------  
pg_catalog | to_char | text | bigint, text | normal  
pg_catalog | to_char | text | double precision, text | normal  
pg_catalog | to_char | text | integer, text | normal  
pg_catalog | to_char | text | interval, text | normal  
pg_catalog | to_char | text | numeric, text | normal  
pg_catalog | to_char | text | real, text | normal  
pg_catalog | to_char | text | timestamp with time zone, text | normal  
pg_catalog | to_char | text | timestamp without time zone, text | normal  
(8 rows)

4 以 postgres 超级用户连接，修改 to_char 函数属性

1
2

skytf=# alter function to_char(timestamp with time zone, text) IMMUTABLE;  
ALTER FUNCTION

备注：由于表 test_39 上的列 create_time 类型为 “timestamp with time zone” , 所以修改函数时应该修改函数 to_char(timestamp with time zone, text)，为了安全己见，不要直接修改 to_char 函数，建议新建一个 IMMUTABLE 属性的 to_char_immutable 函数。

5 验证是否生效

1
2
3
4
5
6

skytf=> /ef to_char(timestamp with time zone, text)
CREATE OR REPLACE FUNCTION pg_catalog.to_char(timestamp with time zone, text)  
RETURNS text  
LANGUAGE internal  
IMMUTABLE STRICT  
AS $function$timestamptz_to_char$function$

从“IMMUTABLE STRICT” 中可以看出，函数已经修改成 “IMMUTABLE”属性。

6 以 skytf 连接, 再次创建索引

1
2

skytf=> create index CONCURRENTLY idx_test_skyid_ctime on test_39 using btree (skyid, to_char(create_time, 'YYYY-MM-DD') );  
CREATE INDEX

备注：在修改函数 to_char(timestamp with time zone, text) 属性后，创建索引就成功了。

1
2
3
4
5
6
7
8
9

skytf=> /d test_39  
 Table "skytf.test_39"  
 Column | Type | Modifiers  
-------------+--------------------------+-----------  
skyid | integer |  
create_time | timestamp with time zone |  
name | character varying(32) |  
Indexes:  
 "idx_test_skyid_ctime" btree (skyid, to_char(create_time, 'YYYY-MM-DD'::text))

7 手册上关于 “IMMUTABLE” 属性解释

IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.

8 总结
函数的默认属性为 “VOLATILE”， 即可变的，在创建函数索引时，需要将引用函数的属性改为”IMMUTABLE”, 即稳定的，函数索引才能创建成功。也就是说，只有属性为稳定的函数，才能用来创建函数索引。