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.