Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux