ERROR: functions in index expression must be marked IMMUTABLE

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

os: centos 7.4.1708
db: postgresql 11.8

版本

  1. # cat /etc/centos-release
  2. CentOS Linux release 7.4.1708 (Core)
  3. #
  4. #
  5. # yum list installed |grep -i postgresql
  6. postgresql11.x86_64 11.8-1PGDG.rhel7 @pgdg11
  7. postgresql11-contrib.x86_64 11.8-1PGDG.rhel7 @pgdg11
  8. postgresql11-debuginfo.x86_64 11.5-1PGDG.rhel7 @pgdg11
  9. postgresql11-devel.x86_64 11.8-1PGDG.rhel7 @pgdg11
  10. postgresql11-docs.x86_64 11.8-1PGDG.rhel7 @pgdg11
  11. postgresql11-libs.x86_64 11.8-1PGDG.rhel7 @pgdg11
  12. postgresql11-llvmjit.x86_64 11.8-1PGDG.rhel7 @pgdg11
  13. postgresql11-odbc.x86_64 12.01.0000-1PGDG.rhel7 @pgdg11
  14. postgresql11-plperl.x86_64 11.8-1PGDG.rhel7 @pgdg11
  15. postgresql11-plpython.x86_64 11.8-1PGDG.rhel7 @pgdg11
  16. postgresql11-pltcl.x86_64 11.8-1PGDG.rhel7 @pgdg11
  17. postgresql11-server.x86_64 11.8-1PGDG.rhel7 @pgdg11
  18. postgresql11-tcl.x86_64 2.4.0-2.rhel7.1 @pgdg11
  19. postgresql11-test.x86_64 11.8-1PGDG.rhel7 @pgdg11
  20. # su - postgres
  21. Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
  22. $
  23. $
  24. $ psql -c "select version();"
  25. version
  26. ---------------------------------------------------------------------------------------------------------
  27. 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
  28. (1 row)

ERROR: functions in index expression must be marked IMMUTABLE

  1. # su - postgres
  2. $ psql
  3. postgres=# create table tmp_t0 (
  4. id int8 primary key,
  5. name varchar(100),
  6. memo varchar(100)
  7. );
  8. postgres=# insert into tmp_t0 select 1,'我','我';
  9. insert into tmp_t0 select 2,'a0','a0';
  10. postgres=# select * from tmp_t0;
  11. id | name | memo
  12. ----+------+------
  13. 1 | |
  14. 2 | a0 | a0
  15. (2 rows)
  16. postgres=# create index concurrently idx_tmp_t0_name_memo_trgm on public.tmp_t0 using btree(concat(name,memo) );
  17. ERROR: functions in index expression must be marked IMMUTABLE
  18. postgres=# \ef concat
  19. CREATE OR REPLACE FUNCTION pg_catalog.concat(VARIADIC "any")
  20. RETURNS text
  21. LANGUAGE internal
  22. STABLE PARALLEL SAFE
  23. AS $function$text_concat$function$
  24. postgres=# \x
  25. postgres=# \df+ concat
  26. List of functions
  27. -[ RECORD 1 ]-------+-------------------
  28. Schema | pg_catalog
  29. Name | concat
  30. Result data type | text
  31. Argument data types | VARIADIC "any"
  32. Type | normal
  33. Volatility | stable
  34. Parallel | safe
  35. Owner | postgres
  36. Security | invoker
  37. Access privileges |
  38. Language | internal
  39. Source code | text_concat
  40. Description | concatenate values

可以看到 Volatility=stable

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

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

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

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

发表评论

表情:
评论列表 (有 0 条评论,110人围观)

还没有评论,来说两句吧...

相关阅读