Unqualified references in evergreen.unaccent_and_squash lead to index creation failures with pg_restore

[Jeff Godin]

Affects: Evergreen 2.11 and up.
Tested with PostgreSQL 9.4 and Evergreen 2.11.3

Attempting to dump and restore an Evergreen 2.11 or higher database generates up to four errors at restore time when attempting to create the following indexes on actor.usr:

If you're using the -e / --exit-on-error option to pg_restore, your restore will stop at the first error.

actor_usr_family_name_unaccent_idx
actor_usr_first_given_name_unaccent_idx
actor_usr_second_given_name_unaccent_idx
actor_usr_usrname_unaccent_idx

For reference/background, these indexes were added in bug 1501781

If actor.usr contains no non-NULL values for a relevant column (say, actor.usr.second_given_name), an error will not be generated for that index.

Full output of one error is:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6908; 1259 286024 INDEX actor_usr_family_name_unaccent_idx evergreen
pg_restore: [archiver (db)] could not execute query: ERROR: function unaccent(text) does not exist
LINE 1: SELECT evergreen.lowercase(unaccent(regexp_replace(arg, '\s'...
                                   ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT evergreen.lowercase(unaccent(regexp_replace(arg, '\s','','g')))
CONTEXT: PL/pgSQL function evergreen.unaccent_and_squash(text) line 3 at RETURN
    Command was: CREATE INDEX actor_usr_family_name_unaccent_idx ON usr USING btree (evergreen.unaccent_and_squash(family_name));

[Jeff Godin (jgodin)]

Full error output, for benefit of future searchers:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6908; 1259 286024 INDEX actor_usr_family_name_unaccent_idx evergreen
pg_restore: [archiver (db)] could not execute query: ERROR: function unaccent(text) does not exist
LINE 1: SELECT evergreen.lowercase(unaccent(regexp_replace(arg, '\s'...
                                   ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT evergreen.lowercase(unaccent(regexp_replace(arg, '\s','','g')))
CONTEXT: PL/pgSQL function evergreen.unaccent_and_squash(text) line 3 at RETURN
    Command was: CREATE INDEX actor_usr_family_name_unaccent_idx ON usr USING btree (evergreen.unaccent_and_squash(family_name));

pg_restore: [archiver (db)] Error from TOC entry 6910; 1259 286022 INDEX actor_usr_first_given_name_unaccent_idx evergreen
pg_restore: [archiver (db)] could not execute query: ERROR: function unaccent(text) does not exist
LINE 1: SELECT evergreen.lowercase(unaccent(regexp_replace(arg, '\s'...
                                   ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT evergreen.lowercase(unaccent(regexp_replace(arg, '\s','','g')))
CONTEXT: PL/pgSQL function evergreen.unaccent_and_squash(text) line 3 at RETURN
    Command was: CREATE INDEX actor_usr_first_given_name_unaccent_idx ON usr USING btree (evergreen.unaccent_and_squash(first_given_name));

pg_restore: [archiver (db)] Error from TOC entry 6918; 1259 286023 INDEX actor_usr_second_given_name_unaccent_idx evergreen
pg_restore: [archiver (db)] could not execute query: ERROR: function unaccent(text) does not exist
LINE 1: SELECT evergreen.lowercase(unaccent(regexp_replace(arg, '\s'...
                                   ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT evergreen.lowercase(unaccent(regexp_replace(arg, '\s','','g')))
CONTEXT: PL/pgSQL function evergreen.unaccent_and_squash(text) line 3 at RETURN
    Command was: CREATE INDEX actor_usr_second_given_name_unaccent_idx ON usr USING btree (evergreen.unaccent_and_squash(second_given_name));

pg_restore: [archiver (db)] Error from TOC entry 6921; 1259 286025 INDEX actor_usr_usrname_unaccent_idx evergreen
pg_restore: [archiver (db)] could not execute query: ERROR: function unaccent(text) does not exist
LINE 1: SELECT evergreen.lowercase(unaccent(regexp_replace(arg, '\s'...
                                   ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT evergreen.lowercase(unaccent(regexp_replace(arg, '\s','','g')))
CONTEXT: PL/pgSQL function evergreen.unaccent_and_squash(text) line 3 at RETURN
    Command was: CREATE INDEX actor_usr_usrname_unaccent_idx ON usr USING btree (evergreen.unaccent_and_squash(usrname));

WARNING: errors ignored on restore: 4

[Jeff Godin (jgodin)]

At implied in the bug summary/title, and in the context of the pg_restore error output, the problem lies in the evergreen.unaccent_and_squash() function:

RETURN evergreen.lowercase(unaccent(regexp_replace(arg, '\s','','g')));

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.lowercase(public.unaccent(regexp_replace(arg, '\s','','g')));

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.unaccent_and_squash(text) line 3 at RETURN
    Command was: CREATE INDEX actor_usr_family_name_unaccent_idx ON usr USING btree (evergreen.unaccent_and_squash(family_name));

Thankfully, we can pass the fully qualified dictionary as an argument, which gives us:

RETURN evergreen.lowercase(public.unaccent('public.unaccent', regexp_replace(arg, '\s','','g')));

I think that the upgrade script for this will look like:

 - redefine the evergreen.unaccent_and_squash function to use fully qualified references to the unaccent function and unaccent dictionary

 - 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.

[Jeff Godin (jgodin)]

When replacing the function in upgrade scripts, we'll also need to account for the changes made to evergreen.unaccent_and_squash as part of bug 1613341, which landed in 2.12. This mostly means that the upgrade script for 2.11 will be different.

[Jeff Godin (jgodin)]

Work in progress at user/jeff/unaccent_fix_lp1671150

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/jeff/unaccent_fix_lp1671150

[Jeff Godin (jgodin)]

Never mind comment #3 above -- 2.11 is now security fixes only.

New rebased branch at user/jeff/lp1671150_unaccent_fix with release notes:

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/jeff/lp1671150_unaccent_fix

Should backport to 2.12 and 3.0 without much trouble.

[Jason Stephenson (jstephenson)]

I'm hitting this on 2.12. I'll give this a look, but it seems like higher value arguments to the -j option seem more likely to trigger it.

[Jason Stephenson (jstephenson)]

Solves the problem for me!

Pushed to master, rel_3_0 and rel_2_12.

Thanks, Jeff!