© 2004
Canonical Ltd.
•
Terms of use
•
Data privacy
•
Contact Launchpad Support
•
Blog
•
Careers
•
System status
•
67d34a1
(Get the code!)
At implied in the bug summary/title, and in the context of the pg_restore error output, the problem lies in the evergreen.
RETURN evergreen.
The call to unaccent() is unqualified, and relies on being executed in an environment where search_path contains a schema which contains unaccent().
In this scenario, pg_restore is not such an environment. At the point where these indexes are created, search_path has been explicitly set by pg_restore to contain only the actor and pg_catalog schemas.
In an Evergreen database, the unaccent extension is created in the public schema, so we would qualify the function call as public.unaccent():
RETURN evergreen.
This then reveals another unqualified reference issue: the default tsearch dictionary used by unaccent():
pg_restore: [archiver (db)] could not execute query: ERROR: text search dictionary "unaccent" does not exist
CONTEXT: PL/pgSQL function evergreen.
Command was: CREATE INDEX actor_usr_
Thankfully, we can pass the fully qualified dictionary as an argument, which gives us:
RETURN evergreen.
I think that the upgrade script for this will look like:
- redefine the evergreen.
- ensure that the four relevant indexes on actor.usr exist (because they may have been lost in a previous pg_dump/pg_restore)
The last bit can be done either by attempting to blindly create the indexes in a way that's tolerant of failures, or by dropping and re-creating them. There are a few ways to test for their existence beforehand, but doing so in a simple IF NOT EXISTS doesn't come along until Postgres 9.5.