pg_restore error: function raise_err(unknown) does not exist

Question

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

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 > backupsfolder/databaseName.tar

When I use this command:

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: error: could not execute query: ERROR: function raise_err(unknown) does not exist

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.

Log into your source database with psql, and do \df+ raise_err. That is not a standard function so far as I know. You should not need another function to raise an error since you can just do raise error — Mike Organek
Does that work in a function written in sql or does it have to be plpgsql? — HelpMeExitVim
\df+ raise_err does...return the function. It's not something I ever remember explicitly creating but I mean, stuff happens. — HelpMeExitVim
Sorry I did not think of that. language sql will not support it, and that probably explains why you wrote it as a function. Also, it is raise exception, not raise error as I erroneously said in my earlier comment. It looks like this sometimes happens: stackoverflow.com/q/30707533/13808319 — Mike Organek
Don't feel stupid, creating that function makes perfect sense IMO. I suspect that the fact that this is a language sql function confounded the algo's trying to find dependencies during pg_backup. — Mike Organek

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-08-21T07:26:10

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

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.

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!

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

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

pg_restore error: function raise_err(unknown) does not exist

2 Answers