When an error is reported when creating a functional index, the error information is the title, and the following is the details.

Table 1 Definition

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) |

Now you need to create a federated index on the fields “skyid”, “create_time”.

2 create index

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” means that the function must be marked as “immutable” when creating a function index.

3 view to_ Char function

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. Connect with Postgres super user and modify to_ Char function properties

1
2

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

Note: due to table test_ Column create on 39_ The time type is “timestamp with time zone”, so the function to should be modified when modifying the function_ Char (timestamp with time zone, text). For your own safety, do not modify to directly_ Char function. It is recommended to create a to of immutable attribute_ char_ Immutable function.

5 validation

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$

As can be seen from “immutable strict”, the function has been modified to “immutable” attribute.

6 connect with skytf and create the index again

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

Note: when modifying function to_ After the char (timestamp with time zone, text) attribute, the index is created successfully.

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 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 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.