Thread: Autovacuum analyze can't find C based function

Autovacuum analyze can't find C based function

From
Thomas Butz
Date:
While installing Nominatim(OSM geocoding service) I stumbled upon this issue:
https://github.com/openstreetmap/Nominatim/issues/1097#issue-341799919

The function "transliteration" has been properly created during the installation processes according to the following
template:
https://github.com/openstreetmap/Nominatim/blob/v3.1.0/sql/functions.sql#L25

The strange thing is that i'm able to execute the function while background analyzation of the table "placex"
repeatedlyfails with the following error: 

2018-07-17 06:59:35.908 UTC [30641] ERROR:  function transliteration(text) does not exist at character 23
2018-07-17 06:59:35.908 UTC [30641] HINT:  No function matches the given name and argument types. You might need to add
explicittype casts. 
2018-07-17 06:59:35.908 UTC [30641] QUERY:  SELECT gettokenstring(transliteration(name))
2018-07-17 06:59:35.908 UTC [30641] CONTEXT:  PL/pgSQL function public.make_standard_name(text) line 5 at assignment
        automatic analyze of table "nominatim.public.placex"

I can execute the same function without an error in a psql session though:

# psql -d nominatim -U www-data -c "SELECT transliteration('some w€êîrd text')"
 transliteration
-----------------
 some weird text
(1 row)

Restarting the database stops the anaylze errors from occurring.

Postgres:  10.4
Postgis:    2.4
Nominatim: 3.1.0

--
Best regards


Thomas Butz


Re: Autovacuum analyze can't find C based function

From
Tom Lane
Date:
Thomas Butz <tbutz@optitool.de> writes:
> While installing Nominatim(OSM geocoding service) I stumbled upon this issue:
https://github.com/openstreetmap/Nominatim/issues/1097#issue-341799919
> The function "transliteration" has been properly created during the installation processes according to the following
template:
> https://github.com/openstreetmap/Nominatim/blob/v3.1.0/sql/functions.sql#L25

> The strange thing is that i'm able to execute the function while background analyzation of the table "placex"
repeatedlyfails with the following error: 

> 2018-07-17 06:59:35.908 UTC [30641] ERROR:  function transliteration(text) does not exist at character 23

In recent PG releases, autovacuum executes stuff under a restrictive
search_path setting (just pg_catalog, in fact).  So you need to do
something explicit to reference stuff in other schemas; either schema
qualify the name, or add a "SET search_path" clause to the function
definition.  This is good practice anyway: what you're seeing is that this
function fails with any search_path other than the one you use by default.

            regards, tom lane


Re: Autovacuum analyze can't find C based function

From
Thomas Butz
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes:
> In recent PG releases, autovacuum executes stuff under a restrictive
> search_path setting (just pg_catalog, in fact).

This should probably be added to section 24.1. of the documentation. At least i couldn't find it elsewhere.

> So you need to do something explicit to reference stuff in other schemas; either schema
> qualify the name

That solved my problem. Now the autovacuum daemon is able to find the function.

> or add a "SET search_path" clause to the function
>definition.

Could you elaborate a bit more on this? It seems like this would only limit/change which objects should be accessed
within the function as described by the CREATE FUNCTION documentation. The function itself wouldn't still be picked
up by the autovacuum daemon or am i missing something?

> This is good practice anyway: what you're seeing is that this
> function fails with any search_path other than the one you use by default.
> 
>             regards, tom lane


-- 
Best regards


Thomas Butz


Re: Autovacuum analyze can't find C based function

From
Noah Misch
Date:
On Thu, Jul 19, 2018 at 10:29:27AM +0200, Thomas Butz wrote:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
> > In recent PG releases, autovacuum executes stuff under a restrictive
> > search_path setting (just pg_catalog, in fact).
> 
> This should probably be added to section 24.1. of the documentation. At least i couldn't find it elsewhere.

That wouldn't be unreasonable, but I think it's too much detail for the
documentation.  What I'd find more promising is to refine the HINT to react
when the function exists in a schema outside search_path.  Perhaps it would
then say "HINT:  No function matches the given name, but other schemas have
functions of that name.  You might need to schema-qualify the function name."
(I don't plan to implement that myself.)

> > or add a "SET search_path" clause to the function
> >definition.
> 
> Could you elaborate a bit more on this? It seems like this would only limit/change which objects should be accessed
> within the function as described by the CREATE FUNCTION documentation. The function itself wouldn't still be picked
> up by the autovacuum daemon or am i missing something?

Based on the error you posted, you would add the "SET search_path" clause to
function public.make_standard_name(text).  Autovacuum did find
public.make_standard_name(text), but it did not find the transliteration(text)
function called inside the body of public.make_standard_name(text).