lightdb中的函数可变性属性及ERROR: functions in index expression must be marked IMMUTABLE解决

　　在postgresql中，函数有个不稳定性分类属性，它会影响优化器评估函数的可优化级别、同时也会影响postgresql中并行执行的可行性。

　　函数有三种类型：VOLATILE、STABLE以及IMMUTABLE。VOLATILE是函数默认类别，也就是优化器假设函数会修改数据库，不会做任何特定的优化。STABLE可用于基于函数的索引扫描。IMMUTABLE通常用于静态常量的优化。对于一些短小逻辑的函数，典型的是数据字典翻译，但是写在SQL中会导致很复杂的逻辑，应该在创建函数时声明为STABLE。一般来说IMMUTABLE不是特别必要，更像是resultset_cache的实现。如下：

lightdb@postgres=# CREATE INDEX idx1_lem_db_log ON lem_db_log USING gin(to_tsquery(concat('field1='|| field1,' field2=' || field2,' field3=' || field3,' field4=' || field4,' field5='||field5)));
ERROR:  functions in index expression must be marked IMMUTABLE
可见在函数索引中，表达式必须为不可变的。

　　要修改系统函数的函数属性，可通过alter table函数签名进行修改，如下：

lightdb@postgres=# \df+ concat()
                                                                                 List of functions
   Schema   |  Name  | Result data type | Argument data types | Type | Volatility | Parallel |  Owner  | Security | Access privileges | Language | Source code |    Description     
------------+--------+------------------+---------------------+------+------------+----------+---------+----------+-------------------+----------+-------------+--------------------
 pg_catalog | concat | text             | VARIADIC "any"      | func | stable     | safe     | lightdb | invoker  |                   | internal | text_concat | concatenate values
(1 row)

lightdb@postgres=# alter function pg_catalog.concat(variadic "any") immutable;   -- 任何长度、任意类型的参数
ALTER FUNCTION

lightdb@postgres=# CREATE INDEX idx1_lem_db_log ON lem_db_log USING gin(to_tsquery(concat('field1='|| field1,' field2=' || field2,' field3=' || field3,' field4=' || field4,' field5='||field5)));
ERROR:  functions in index expression must be marked IMMUTABLE

lightdb@postgres=# \df+ to_tsvector()
                                                                                                           List of functions
   Schema   |    Name     | Result data type | Argument data types | Type | Volatility | Parallel |  Owner  | Security | Access privileges | Language |          Source code          |        
          Description                   
------------+-------------+------------------+---------------------+------+------------+----------+---------+----------+-------------------+----------+-------------------------------+--------
----------------------------------------
 pg_catalog | to_tsvector | tsvector         | json                | func | stable     | safe     | lightdb | invoker  |                   | internal | json_string_to_tsvector       | transfo
rm string values from json to tsvector
 pg_catalog | to_tsvector | tsvector         | jsonb               | func | stable     | safe     | lightdb | invoker  |                   | internal | jsonb_string_to_tsvector      | transfo
rm string values from jsonb to tsvector
 pg_catalog | to_tsvector | tsvector         | regconfig, json     | func | immutable  | safe     | lightdb | invoker  |                   | internal | json_string_to_tsvector_byid  | transfo
rm string values from json to tsvector
 pg_catalog | to_tsvector | tsvector         | regconfig, jsonb    | func | immutable  | safe     | lightdb | invoker  |                   | internal | jsonb_string_to_tsvector_byid | transfo
rm string values from jsonb to tsvector
 pg_catalog | to_tsvector | tsvector         | regconfig, text     | func | immutable  | safe     | lightdb | invoker  |                   | internal | to_tsvector_byid              | transfo
rm to tsvector
 pg_catalog | to_tsvector | tsvector         | text                | func | stable     | safe     | lightdb | invoker  |                   | internal | to_tsvector                   | transfo
rm to tsvector
(6 rows)
多个重载的时候，要找相同的那个重载实现。

　　修改之后，函数索引即可创建，如下：

lightdb@postgres=# CREATE INDEX idx1_lem_db_log ON lem_db_log USING gin(to_tsvector('simple',concat('field1='|| field1,' field2=' || field2,' field3=' || field3,' field4=' || field4,' field5='||field5)));
CREATE INDEX

 

  CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE sql IMMUTABLE ;
    
  explain analyze SELECT dup(42) from (select 1 from pg_catalog.generate_series(1, 1000000));

除了IMMUTABLE，还有一个可选的STRICT修饰符。

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

声明为STRICT或RETURNS NULL ON NULL INPUT表示如果任意入参是NULL，则总是返回NULL。否则NULL需要由函数编写者处理。

http://www.light-pg.com/docs/lightdb/13.3-22.2/xfunc-volatility.html

http://www.light-pg.com/docs/lightdb/13.3-22.2/sql-createfunction.html 

http://www.light-pg.com/docs/lightdb/13.3-22.2/xfunc-pl.html