> From: Paul Ramsey [mailto:pramsey@cleverelephant.ca]
> Sent: Monday, July 23, 2018 2:42 PM
> To: Regina Obe <lr@pcorp.us>
> 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 <tgl@sss.pgh.pa.us>
> 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 <ler@lerctr.org>
> Cc: "Joshua D. Drake" <jd@commandprompt.com>, pgsql-
> hackers@lists.postgresql.org
>
>
> Larry Rosenman <ler@lerctr.org> 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
withpostgis 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
fuzzystrmatchis installed
b) Stephen Frost had suggested, perhaps we should have some syntax like @extension_loc(fuzzystrmatch)...@ so that one
couldreference an extension dependency location within a function without knowing where it is installed.
