ERROR: Functions in index expression must be marked IMMUTABLE

created at 11-10-2021 views: 148

When you encounter an error when creating a functional index, the error message is the title, and the following is the detailed information.

1 Table definition

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

Now you need to create a joint index on the fields "skyid", "create_time".

2 Create an index

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

An error is reported when creating a function index, functions in index expression must be marked IMMUTABLE, which means that the function must be marked as IMMUTABLE when creating a function index.

3 View to_char function

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 Connect as the postgres super user and modify the to_char function attribute

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

Note: Since the column create_time on the table test_39 has a type of timestamp with time zone, the function to_char(timestamp with time zone, text) should be modified when modifying the function. For your own safety, do not modify to_char directly Function, it is recommended to create a new to_char_immutable function with IMMUTABLE attribute.

5 Verify that it works

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$

It can be seen from the "IMMUTABLE STRICT" that the function has been modified to the "IMMUTABLE" attribute.

6 Connect with skytf, create index again

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

Note: After modifying the attribute of the function to_char(timestamp with time zone, text), the index creation is successful.

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 Explanation of "IMMUTABLE" attribute in the manual

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 Summary

The default attribute of the function is "VOLATILE", which is variable. When creating a function index, you need to change the attribute of the reference function to "IMMUTABLE", which is stable, so that the function index can be created successfully. In other words, only functions whose properties are stable can be used to create a function index.

created at:11-10-2021
edited at: 11-10-2021: