PostgreSQL 13转17中search_path异常问题排查与解决咨询

前置条件 #

正在将数据库从PostgreSQL 13迁移至17版本，使用相同迁移脚本重建架构，数据通过以下命令传输：

pg_dump -a -U postgres -h pg13 | psql -h pg17 -U postgres

现象 #

现象1：触发器 #

迁移时触发器内函数无法通过search_path解析，临时解决方案是先禁用触发器、完成数据填充后恢复触发器。迁移完成后，相同psql连接下触发器可正常工作。

现象2：物化视图 #

数据库中存在嵌套依赖函数的物化视图（依赖链：view -> func_1 -> func_2），架构生成无问题，但数据迁移后执行refresh materialized view my_view;时，func_1可被解析，但其内部的func_2无法解析。在相同psql连接中直接调用func_1却正常。为func_1显式指定search_path="$user",public可解决当前问题，但其他函数会出现同样问题。

已尝试操作 #

所有数据库实体均在public架构下，理论默认search_path="$user", public应适用。已尝试通过postgres.conf、ALTER DATABASE、ALTER USER设置search_path，并执行GRANT USAGE ON SCHEMA public TO postgres，但均未彻底解决问题。

问题解答 #

1. 触发器、物化视图刷新时运行的函数与普通SQL调用的差异 #

• search_path继承逻辑不同：
  • 普通SQL调用：直接继承当前会话的search_path配置（会话级、用户级或数据库级的默认值）。
  • 触发器执行：触发器函数的执行上下文继承的是触发器创建时所在的search_path，而非当前会话的配置。如果迁移重建触发器时的环境search_path与原库不一致，或数据导入阶段会话search_path异常，就会触发解析失败。
  • 物化视图刷新：刷新操作在系统内部上下文执行，该上下文的search_path会忽略会话级设置，使用物化视图创建时的search_path或更严格的系统默认值。嵌套函数场景下，内层函数func_2的解析依赖外层函数func_1执行时的search_path，若func_1创建时未固定search_path，就会出现找不到func_2的情况。
• 执行身份的隐性差异：
  触发器、物化视图刷新可能以函数定义者身份执行（若函数为SECURITY DEFINER），此时search_path会使用定义者的默认设置，而非当前会话用户的配置，若定义者的search_path异常，就会引发解析问题。

2. 正确配置search_path的方案 #

方案1：统一架构创建时的search_path

在重建架构的脚本开头固定search_path，确保所有对象创建都基于该上下文：

SET search_path TO "$user", public;
-- 后续执行所有表、函数、触发器、物化视图的创建脚本

这样所有对象创建时都会继承该search_path，后续执行时可正常解析依赖。

方案2：为所有函数显式绑定search_path

创建或修改函数时，通过SET search_path子句固定其执行时的路径，避免依赖上下文：

-- 创建函数时指定
CREATE OR REPLACE FUNCTION func_1()
RETURNS ...
LANGUAGE plpgsql
SET search_path = "$user", public
AS $$
-- 函数体
$$;

-- 批量修改现有函数
ALTER FUNCTION func_1() SET search_path = "$user", public;
ALTER FUNCTION func_2() SET search_path = "$user", public;
-- 对所有自定义函数执行此操作

方案3：全局强制统一search_path并验证

1. 在postgres.conf中设置全局默认：

search_path = '"$user", public'

2. 重启服务后，检查并重置数据库/用户级的自定义search_path：

-- 查看数据库级设置
SHOW search_path FROM DATABASE your_db_name;
-- 查看用户级设置
SHOW search_path FROM USER postgres;

-- 重置自定义设置
ALTER DATABASE your_db_name RESET search_path;
ALTER USER postgres RESET search_path;

3. 确保public架构权限完整：

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO postgres;
GRANT USAGE, SELECT ON ALL TABLES IN SCHEMA public TO postgres;

方案4：数据导入时指定会话search_path

执行数据导入命令时，显式设置会话的search_path：

pg_dump -a -U postgres -h pg13 | psql -h pg17 -U postgres -c "SET search_path TO '$user', public;" -f -

确保数据插入时的会话上下文search_path正确，避免触发器执行时找不到函数。

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