Обсуждение: Autovacuum analyze can't find C based function
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
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
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
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).