ERROR: functions in index expression must be marked IMMUTABLE

逃离我推掉我的手 2023-02-18 09:42 37阅读 0赞

os: centos 7.4.1708  
db: postgresql 11.8

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # 
    # 
    # yum list installed |grep -i postgresql
    postgresql11.x86_64                11.8-1PGDG.rhel7                    @pgdg11
    postgresql11-contrib.x86_64        11.8-1PGDG.rhel7                    @pgdg11
    postgresql11-debuginfo.x86_64      11.5-1PGDG.rhel7                    @pgdg11
    postgresql11-devel.x86_64          11.8-1PGDG.rhel7                    @pgdg11
    postgresql11-docs.x86_64           11.8-1PGDG.rhel7                    @pgdg11
    postgresql11-libs.x86_64           11.8-1PGDG.rhel7                    @pgdg11
    postgresql11-llvmjit.x86_64        11.8-1PGDG.rhel7                    @pgdg11
    postgresql11-odbc.x86_64           12.01.0000-1PGDG.rhel7              @pgdg11
    postgresql11-plperl.x86_64         11.8-1PGDG.rhel7                    @pgdg11
    postgresql11-plpython.x86_64       11.8-1PGDG.rhel7                    @pgdg11
    postgresql11-pltcl.x86_64          11.8-1PGDG.rhel7                    @pgdg11
    postgresql11-server.x86_64         11.8-1PGDG.rhel7                    @pgdg11
    postgresql11-tcl.x86_64            2.4.0-2.rhel7.1                     @pgdg11
    postgresql11-test.x86_64           11.8-1PGDG.rhel7                    @pgdg11
    
    # su - postgres
    Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
    $
    $
    $ psql -c "select version();"
                                                     version                                                 
    ---------------------------------------------------------------------------------------------------------
     PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
    (1 row)

# ERROR: functions in index expression must be marked IMMUTABLE #

# su - postgres
    $ psql
    
    postgres=# create table tmp_t0 (
     id   int8 primary key,
     name varchar(100),
     memo varchar(100)
    );
    
    postgres=# insert into tmp_t0 select 1,'我','我';
    insert into tmp_t0 select 2,'a0','a0';
    
    postgres=# select * from tmp_t0;
     id | name | memo 
    ----+------+------
      1 | 我   | 我
      2 | a0   | a0
    (2 rows)
    
    postgres=# create index concurrently idx_tmp_t0_name_memo_trgm on public.tmp_t0 using btree(concat(name,memo) );
    
    ERROR:  functions in index expression must be marked IMMUTABLE

postgres=# \ef concat
    CREATE OR REPLACE FUNCTION pg_catalog.concat(VARIADIC "any")
     RETURNS text
     LANGUAGE internal
     STABLE PARALLEL SAFE
    AS $function$text_concat$function$
    
    
    postgres=# \x
    postgres=# \df+ concat
    List of functions
    -[ RECORD 1 ]-------+-------------------
    Schema              | pg_catalog
    Name                | concat
    Result data type    | text
    Argument data types | VARIADIC "any"
    Type                | normal
    Volatility          | stable
    Parallel            | safe
    Owner               | postgres
    Security            | invoker
    Access privileges   | 
    Language            | internal
    Source code         | text_concat
    Description         | concatenate values

可以看到 Volatility=stable

两种方法解决  
第一种 修改 concat 函数为 IMMUTABLE

postgres=# alter function pg_catalog.concat(VARIADIC "any") IMMUTABLE;
    
    postgres=# create index concurrently idx_tmp_t0_name_memo_trgm on public.tmp_t0 using btree(concat(name,memo) );

第二种 新建一个类 concat 函数 concat\_immutable 稳定性为 IMMUTABLE

postgres=# CREATE OR REPLACE FUNCTION pg_catalog.concat_immutable(VARIADIC "any")
     RETURNS text
     LANGUAGE internal
     IMMUTABLE PARALLEL SAFE
    AS $function$text_concat$function$
    ;
    
    postgres=# create index concurrently idx_tmp_t0_name_memo_trgm on public.tmp_t0 using btree(concat_immutable(name,memo) );