> From: Paul Ramsey [mailto:[email protected]] > Sent: Monday, July 23, 2018 2:42 PM > To: Regina Obe <[email protected]> > Subject: Fwd: "interesting" issue with restore from a pg_dump with a > database-wide search_path > > Seen this one? > P > > > ---------- Forwarded message ---------- > From: Tom Lane <[email protected]> > Date: Fri, Jul 6, 2018 at 1:10 PM > Subject: Re: "interesting" issue with restore from a pg_dump with a > database-wide search_path > To: Larry Rosenman <[email protected]> > Cc: "Joshua D. Drake" <[email protected]>, pgsql- > [email protected] > > > Larry Rosenman <[email protected]> writes: > > On Fri, Jul 06, 2018 at 11:35:41AM -0700, Joshua D. Drake wrote: > >> Knowing the errors would be helpful. > > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > > pg_restore: [archiver (db)] Error from TOC entry 12; 3079 887963 > > EXTENSION postgis_tiger_geocoder > > pg_restore: [archiver (db)] could not execute query: ERROR: function > > soundex(character varying) does not exist > > HINT: No function matches the given name and argument types. You > might need to add explicit type casts. > > This looks like a problem with the postgis_tiger_geocoder extension. > It's depending on the fuzzystrmatch extension (which has the soundex > function), but seemingly this dependency is not declared in the extension's > control file. If it were, the search path would've been set to include the > schema of the fuzzystrmatch extension during CREATE EXTENSION. > > regards, tom lane [Regina Obe]
Sorry for not posting from the thread. Paul alerted me to this one and I am aware of the issue. 1) I do have fuzzstrmatch listed as a dependency in the control file. I know because I often install the geocoder with CREATE EXTENSION postgis_tiger_geocoder CASCADE; And it installs postgis and fuzzystrmatch 2) I have brought this issue up before and that's why we in fact had to schema qualify all postgis functions cause even with postgis within the same extension, things like materialized views fail to load. 3) My guess as to how this happens a) In this particular case, I have a function that uses fuzzystrmatch and is used in functional indexes. I unfortunately can't schema qualify the use of soundex, because I don't know where the user may have installed fuzzystrmatch is installed b) Stephen Frost had suggested, perhaps we should have some syntax like @extension_loc(fuzzystrmatch)...@ so that one could reference an extension dependency location within a function without knowing where it is installed.
