Postgres函数medrealize在PgAdmin正常 Python调用报gist_trgm_ops错误

原因说明 #

这个报错的核心是PgAdmin和Python连接所用的数据库search_path（模式搜索路径）配置不一致：
gist_trgm_ops是pg_trgm扩展提供的操作符类，你当前的Postgres实例已经安装了pg_trgm（否则PgAdmin也无法正常执行），但两个执行环境的搜索路径不同：

• PgAdmin登录使用的数据库角色，默认search_path包含了pg_trgm扩展所在的模式（通常是public），所以执行索引创建语句时可以正常定位到操作符类。
• Python代码连接数据库使用的角色，默认search_path未包含pg_trgm所在的模式，因此无法找到对应操作符类，抛出报错。

解决方案 #

你可以任选以下一种方式修复：

• 显式在索引创建语句中指定pg_trgm所在的模式，比如pg_trgm安装在public时，修改索引语句为：

  CREATE INDEX SQLOPS_RefICD_ICD10_idx ON  t$SQLOPS_RefICD_ICD10 USING gist (code public.gist_trgm_ops );
  CREATE INDEX regexes_idx ON  t$regexes  USING gist (icdregex  public.gist_trgm_ops );
  

• 在medrealize()函数的开头添加search_path设置语句，确保会话的搜索路径包含pg_trgm所在的模式：

  SET search_path TO public, "$user";
  

• 修改Python连接使用的数据库角色的默认search_path，永久生效：

  ALTER ROLE 你的Python连接所用角色名 SET search_path TO public, "$user";
  

• 在Python代码调用存储过程前，先执行一次search_path设置：

  cursor.execute("BEGIN")
  cursor.execute("SET search_path TO public, \"$user\";")
  cursor.execute("CALL medrealize();")
  cursor.execute("COMMIT")
  

内容的提问来源于stack exchange，提问作者User123