pg_restore 错误:函数 raise_err(unknown) 不存在 [英] pg_restore error: function raise_err(unknown) does not exist

问题描述

我使用 pg_dump 和 pg_restore 对我的数据库进行每日备份，但在我推送更新后最近停止工作.

I run a daily backup of my database using pg_dump and pg_restore that recently stopped working after I pushed an update.

我有一个 validate_id 函数，它是一个 Case/When 语句，用于快速检查一些存在完整性问题的数据.看起来像这样:

I have a function validate_id that's a Case/When statement just as a quick check for some the data that has integrity issues. Looks something like this:

CREATE OR REPLACE FUNCTION validate_id(
    _string text,
    _type type
) RETURNS boolean AS
$$
SELECT
    CASE WHEN (stuff) THEN TRUE
    WHEN (other stuff) THEN TRUE
    When (more stuff) THEN raise_err('Not an accepted type, the accepted types are: x y z')
ELSE FALSE
$$
LANGUAGE SQL;

自从我添加了这个功能，当我使用这个命令转储时:

Since I added this function, when I dump using this command:

pg_dump -U postgres -h ipaddress -p 5432 -w -F t databaseName >备份文件夹/数据库名称.tar

当我使用这个命令时:

pg_restore -U postgres -h localhost -p 5432 -d postgres -C "backupsfolder/databaseName.tar"

截至两天前，这会引发错误:

As of two days ago, this now throws an error:

pg_restore:错误:无法执行查询:错误:函数 raise_err(unknown) 不存在

我不知道该怎么做.我认为可能发生的事情是它试图在恢复 raise_err 函数之前恢复这个函数.我认为这是 postgres 内置的(我可以SELECT raise_err('Hello, World');).这可能吗?这是我的 CASE 语句，因为我只需要返回布尔值吗?所有权限似乎都正确，并且使用以前的备份进行恢复工作正常.

I'm pretty lost on what to do. I think what might be going on is that it's trying to restore this function before it restores the raise_err function. Which I thought was built-in to postgres (I can SELECT raise_err('Hello, World');). Is this possible? Is it my CASE statement because I need to return only Booleans? All of the permissions seem correct and restoring with previous backups works fine.

推荐答案

问题是 raise_err 不是您的函数代码中的架构限定.

The problem is that raise_err is not schema qualified in your function code.

这是潜在的危险:恶意用户可以创建自己的函数 raise_err 并设置 search_path 从而调用错误的函数.

This is potentially dangerous: a malicious user could create his own function raise_err and set search_path so that the wrong function is called.

由于 pg_restore 通常由超级用户运行，这可能是一个安全问题.想象一下在索引定义中使用了这样一个函数！

Since pg_restore is typically run by a superuser, this can be a security problem. Imagine such a function being used in an index definition!

出于这些原因，pg_dump 和 pg_restore 在当前版本的 PostgreSQL 中设置了一个空的 search_path.

For these reasons pg_dump and pg_restore set an empty search_pathin current versions of PostgreSQL.

解决您的问题的方法是在您的 SQL 语句中显式使用函数的架构.

The solution to your problem is to explicitly use the function's schema in your SQL statement.

这篇关于pg_restore 错误:函数 raise_err(unknown) 不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！