PostgreSQL使用pg_trgm扩展运行模糊搜索报错的永久方案咨询

该报错不是pg_trgm扩展本身的稳定性问题，90%以上的场景由以下原因导致，和你描述的“运行数日才报错、重建扩展临时恢复”的特征完全匹配：

• 扩展创建位置错误：创建pg_trgm时没有指定固定公共schema，扩展被建在了临时生效的搜索路径对应的schema下，后续业务连接的search_path变更、数据库schema权限调整、连接身份切换后，数据库无法检索到pg_trgm提供的%模糊匹配操作符。
• 级联删除误删扩展对象：日常变更中执行带CASCADE的删除语句（比如删除依赖pg_trgm的自定义函数、视图、扩展时），会连带删除pg_trgm的部分操作符、函数定义，但扩展的系统元数据残留，就会出现“扩展显示存在但功能不可用”的情况，重建扩展本质是补全了被误删的对象，因此只能临时恢复。
• 升级/迁移残留：跨大版本升级PostgreSQL、跨实例迁移数据时，没有正确同步扩展的依赖对象，扩展注册信息和实际存储的对象不匹配，运行一段时间系统缓存失效后就会触发报错。

按以下步骤操作完成后，无需反复重建扩展即可稳定运行：

1. 固定扩展的创建位置
   不要将pg_trgm创建在业务专属schema下，统一放到所有用户默认可访问的public schema：

   -- 清理当前异常状态的扩展
   DROP EXTENSION IF EXISTS pg_trgm;
   -- 明确指定扩展创建到public schema
   CREATE EXTENSION pg_trgm SCHEMA public;
   

2. 固化搜索路径配置
   给业务账号、业务库配置永久默认的搜索路径，确保public schema在搜索路径范围内，禁止业务连接随意修改全局搜索路径：

   -- 替换your_biz_user为实际业务连接使用的账号名
   ALTER ROLE your_biz_user SET search_path TO public, 你需要使用的其他业务schema;
   -- 替换your_db_name为实际业务库名，配置库级全局默认搜索路径
   ALTER DATABASE your_db_name SET search_path TO public, 你需要使用的其他业务schema;
   

3. 校验配置有效性
   切换到业务账号执行以下查询，能返回匹配的操作符记录即代表配置生效：

   SELECT oprname, oprleft::regtype, oprright::regtype
   FROM pg_operator
   WHERE oprname = '%'
     AND oprleft = 'varchar'::regtype
     AND oprright = 'text'::regtype;
   

4. 规避变更风险
   排查所有数据库变更脚本、迁移工具（Flyway、Liquibase等）、定时运维任务，非必要不要在删除语句中添加CASCADE关键字，避免级联删除公共扩展的依赖对象。
5. 修复升级/迁移残留
   如果是大版本升级、迁移后的环境，执行以下语句将扩展更新到和当前数据库版本匹配的状态：

   ALTER EXTENSION pg_trgm UPDATE;
   

如果业务场景无法统一管控search_path，可以在编写SQL时显式指定操作符所属的schema，完全不依赖搜索路径配置：

-- 显式调用public schema下的%操作符
SELECT * FROM your_table WHERE your_column OPERATOR(public.%) 'match_keyword';

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