Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

On 09/05/2018 01:49 AM, Gunnlaugur Thor Briem wrote:
> OK, I found the cause of the unaccent dictionary problem, and a workaround.
>
> It's not the vacuumdb version, not the unaccent version, and it's not
> even a pg_upgrade problem: I get this error also with PG 9.4.18 running
> on the old cluster, with both the 10.5 vacuumdb and the 9.4.18 vacuumdb,
> and I get the same error in both.
>
> And it's not strictly a vacuumdb problem, though vacuumdb triggers it.
>
> Here's a very minimal test case, unrelated to my DB, that you ought to
> be able to reproduce:
>
> SET search_path = "$user"; SELECT public.unaccent('fóö');
> SET
> ERROR:  text search dictionary "unaccent" does not exist
>
> and here's a workaround:
>
> SET search_path = "$user"; SELECT public.unaccent(tsdict.oid, 'fóö')
> FROM pg_catalog.pg_ts_dict tsdict WHERE dictname='unaccent';
> SET
>  unaccent
> ----------
>  foo
> (1 row)
>
> The workaround avoids the OID lookup of the dictionary ... that lookup
> (in the single-argument unaccent function) is done by unqualified name:
>
> https://github.com/postgres/postgres/blob/fb8697b31aaeebe6170c572739867dcaa01053c6/contrib/unaccent/unaccent.c#L377
>
>         dictOid =
> get_ts_dict_oid(stringToQualifiedNameList("unaccent"), false);
>
> and that fails if the search path doesn't include public. >
> So it is indeed triggered by the security changes that Bruce mentioned;
> those were backported into 9.4.17:
> https://www.postgresql.org/docs/9.4/static/release-9-4-17.html ... and
> so got pulled in by my Macports upgrades. So nothing to do with pg_upgrade.
>
> So the workaround for my vacuumdb/function-index problem is to give
> unaccent the OID of the text search dictionary, so that the search path
> isn't in play:
>
> CREATE OR REPLACE FUNCTION public.semantic_normalize(title text)
> RETURNS text
>  LANGUAGE sql
>  IMMUTABLE STRICT
> AS $function$
>   SELECT lower(public.unaccent(16603, btrim(regexp_replace($1, '\s+', '
> ', 'g'), ' "')))
> $function$;
>
> and that makes vacuumdb -z work in both 9.4.18 and 10.5, and makes
> ./analyze_new_cluster.sh complete without problems.

Nice investigation. Working off the above, I offer a suggestion:

SET search_path = "$user"; SELECT public.unaccent('unaccent', 'fóö');
SET
ERROR: text search dictionary "unaccent" does not exist
LINE 1: SELECT public.unaccent('unaccent', 'fóö');

SET search_path = "$user"; SELECT public.unaccent('public.unaccent', 'fóö');
SET
unaccent
----------
foo

That eliminates hard wiring the OID.

>
> The proper fix is, I suppose, to make the single-argument unaccent
> function explicitly look up the dictionary in the same schema as the
> function itself is in.
>
> Cheers,
> Gulli
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com