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

Yep, a neater workaround for sure!

Cheers,
Gulli

On Wed, Sep 5, 2018 at 2:00 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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