pg_restore ignores search_path variable

22 views Asked by At

I have database mydb, it has two schemas, public and s1. There is unaccent extension installed. It is in schema s1.

There is also a function, which calls unaccent function:

CREATE OR REPLACE FUNCTION case_accent_insensitive(text)
RETURNS text
AS $func$ SELECT unaccent('unaccent', lower($1)) $func$  LANGUAGE sql IMMUTABLE;

I use this function in index:

CREATE INDEX project_name_case_accent_insensitive_idx ON s1.project USING btree (s1.case_accent_insensitive((name)::text));

Notice, that the calling of unaccent is not qualified by schema. I have search_path set to s1, public, so PostgreSQL finds unaccent function there and everything works fine.

But I have a problem with pg_restore. I create another DB with name mydb2, again with schemas public and s1. I know the search_path setting is not present in the dump so I set it by hand in mydb2:

ALTER DATABASE mydb2 SET search_path TO s1,public;

When I try to restore mydb to mydb2 I get errors:

$ pg_dump -d mydb -U postgres -Fc -f mydb.dump
$ pg_restore -d mydb2 -U postgres -Fc mydb.dump
...
pg_restore: from TOC entry 6988; 1259 427901 INDEX project_name_case_accent_insensitive_idx postgres
pg_restore: error: could not execute query: ERROR:  function unaccent(unknown, text) does not exist
LINE 1:  SELECT unaccent('unaccent', lower($1)) 
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:   SELECT unaccent('unaccent', lower($1)) 
CONTEXT:  SQL function "case_accent_insensitive" during inlining
Command was: CREATE INDEX project_name_case_accent_insensitive_idx ON s1.project USING btree (s1.case_accent_insensitive((name)::text));

It just did not find unaccent in s1 scheme. When I log in to the database and execute CREATE INDEX ... manually, it works fine. Why does pg_restore ignore search_path? Thanks.

0

There are 0 answers