PostgreSQL执行创建触发器脚本提示‘schema main不存在’的问题求助

执行的触发器脚本 #

CREATE TRIGGER pdate_day_month_decade
AFTER UPDATE ON main."YDate"
FOR EACH ROW
    WHEN (OLD."dayMonth" IS DISTINCT FROM NEW."dayMonth")
        EXECUTE FUNCTION pdate_day_month_decade_trigger();

报错信息 #

ERROR: schema "main" does not exist
SQL state: 3F000

已知环境信息 #

• 可正常对main schema执行INSERT、UPDATE操作，且该schema权限为PUBLIC
• 执行\dn的结果：

\dn
      List of schemas
  Name  |       Owner
--------+-------------------
 main   | postgres
 public | pg_database_owner

• 执行show search path的结果：

"$user", public

排查与解决方案 #

1. 检查触发器函数的归属schema
   触发器调用的函数如果不在当前搜索路径内，可能会引发解析错误。执行以下SQL查看函数所在的schema：

   SELECT proname, nspname 
   FROM pg_proc 
   JOIN pg_namespace ON pg_proc.pronamespace = pg_namespace.oid 
   WHERE proname = 'pdate_day_month_decade_trigger';
   

   如果函数不在public或main下，需要在触发器语句中指定函数的完整schema路径。

2. 明确指定函数的schema路径
   修改触发器脚本，为调用的函数添加schema前缀（假设函数在main schema下）：

   CREATE TRIGGER pdate_day_month_decade
   AFTER UPDATE ON main."YDate"
   FOR EACH ROW
       WHEN (OLD."dayMonth" IS DISTINCT FROM NEW."dayMonth")
           EXECUTE FUNCTION main.pdate_day_month_decade_trigger();
   

3. 临时调整搜索路径测试
   先将main加入当前会话的搜索路径，再尝试创建触发器：

   SET search_path TO "$user", public, main;
   CREATE TRIGGER pdate_day_month_decade
   AFTER UPDATE ON "YDate"
   FOR EACH ROW
       WHEN (OLD."dayMonth" IS DISTINCT FROM NEW."dayMonth")
           EXECUTE FUNCTION pdate_day_month_decade_trigger();
   

   如果成功，说明是搜索路径导致的解析问题。可以通过以下命令永久修改当前用户的搜索路径：

   ALTER ROLE your_username SET search_path TO "$user", public, main;
   

   替换your_username为实际数据库用户名。

4. 验证标识符大小写一致性
   PostgreSQL对未加引号的标识符会自动转为小写，确认main schema的名称是否存在大小写差异：

   SELECT nspname FROM pg_namespace WHERE nspname = 'main';
   

   如果返回空结果，说明实际schema名称可能包含大写（比如Main），此时需要用双引号包裹schema名称："main"。

内容的提问来源于stack exchange，提问作者Антон Васюхин