From: | Gunnlaugur Thor Briem <gunnlaugur(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Bruce Momjian <bruce(at)momjian(dot)us>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist) |
Date: | 2018-09-05 08:49:50 |
Message-ID: | CAPs+M8LYh1fmuU8=yG21nyAuZOOY22EGZsMCwWPH8TLhOqK9tg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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:
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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jehan-Guillaume (ioguix) de Rorthais | 2018-09-05 09:38:35 | Re: increasing HA |
Previous Message | Michael Paquier | 2018-09-05 04:27:17 | Re: scram-sha-256 authentication broken in FIPS mode |
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Korotkov | 2018-09-05 09:26:46 | Re: Bug in ginRedoRecompress that causes opaque data on page to be overrun |
Previous Message | Andrey Borodin | 2018-09-05 08:37:46 | Re: A strange GiST error message or fillfactor of GiST build |