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.