Re: unaccent(text) fails depending on search_path (WAS: pg_upgradefails saying function unaccent(text) doesn't exist) - Mailing list pgsql-general

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.klaver@aklaver.com