Обсуждение: "interesting" issue with restore from a pg_dump with a database-widesearch_path

I have the following:
----
\set DB `echo $DB`
CREATE SCHEMA IF NOT EXISTS postgis;
CREATE SCHEMA IF NOT EXISTS topology;
CREATE SCHEMA IF NOT EXISTS tiger;
SET search_path=public,postgis,tiger,topology;
ALTER DATABASE :DB SET search_path=public,postgis,tiger,topology;
\c wm_test
CREATE EXTENSION fuzzystrmatch schema postgis;
-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis schema postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology schema topology;
-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
CREATE EXTENSION postgis_sfcgal schema postgis;
-- rule based standardizer
CREATE EXTENSION address_standardizer schema postgis;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us schema postgis;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder schema tiger;
-- routing functionality
CREATE EXTENSION pgrouting schema postgis;
-- spatial foreign data wrappers
CREATE EXTENSION ogr_fdw schema postgis;
-- LIDAR support
CREATE EXTENSION pointcloud schema postgis;
-- LIDAR Point cloud patches to geometry type cases
CREATE EXTENSION pointcloud_postgis schema postgis;
-----

when I pg_dump -Fc the database and then try to restore it after a
create database, I get errors.  To get a clean restare I need to do:

---
\set DB `echo ${DB}`
CREATE SCHEMA IF NOT EXISTS postgis;
CREATE SCHEMA IF NOT EXISTS topology;
CREATE SCHEMA IF NOT EXISTS tiger;
SET search_path=public,postgis,tiger,topology;
ALTER DATABASE :DB SET search_path=public,postgis,tiger,topology;
\c
CREATE EXTENSION fuzzystrmatch schema postgis;
-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis schema postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology schema topology;
-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
CREATE EXTENSION postgis_sfcgal schema postgis;
-- rule based standardizer
CREATE EXTENSION address_standardizer schema postgis;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us schema postgis;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder schema tiger;
-- routing functionality
CREATE EXTENSION pgrouting schema postgis;
-- spatial foreign data wrappers
CREATE EXTENSION ogr_fdw schema postgis;
-- LIDAR support
CREATE EXTENSION pointcloud schema postgis;
-- LIDAR Point cloud patches to geometry type cases
CREATE EXTENSION pointcloud_postgis schema postgis;
----
Is the need to do this expected?

This is 10.4 on FreeBSD.



--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: ler@lerctr.org
US Mail: 5708 Sabbia Drive, Round Rock, TX 78665-2106

On 07/06/2018 11:27 AM, Larry Rosenman wrote:
> when I pg_dump -Fc the database and then try to restore it after a
> create database, I get errors.  To get a clean restare I need to do:

Knowing the errors would be helpful.

jD


> ---
> \set DB `echo ${DB}`
> CREATE SCHEMA IF NOT EXISTS postgis;
> CREATE SCHEMA IF NOT EXISTS topology;
> CREATE SCHEMA IF NOT EXISTS tiger;
> SET search_path=public,postgis,tiger,topology;
> ALTER DATABASE :DB SET search_path=public,postgis,tiger,topology;
> \c
> CREATE EXTENSION fuzzystrmatch schema postgis;
> -- Enable PostGIS (includes raster)
> CREATE EXTENSION postgis schema postgis;
> -- Enable Topology
> CREATE EXTENSION postgis_topology schema topology;
> -- Enable PostGIS Advanced 3D
> -- and other geoprocessing algorithms
> CREATE EXTENSION postgis_sfcgal schema postgis;
> -- rule based standardizer
> CREATE EXTENSION address_standardizer schema postgis;
> -- example rule data set
> CREATE EXTENSION address_standardizer_data_us schema postgis;
> -- Enable US Tiger Geocoder
> CREATE EXTENSION postgis_tiger_geocoder schema tiger;
> -- routing functionality
> CREATE EXTENSION pgrouting schema postgis;
> -- spatial foreign data wrappers
> CREATE EXTENSION ogr_fdw schema postgis;
> -- LIDAR support
> CREATE EXTENSION pointcloud schema postgis;
> -- LIDAR Point cloud patches to geometry type cases
> CREATE EXTENSION pointcloud_postgis schema postgis;
> ----
> Is the need to do this expected?
>
> This is 10.4 on FreeBSD.
>
>
>

-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
*****     Unless otherwise stated, opinions are my own.   *****

On Fri, Jul 06, 2018 at 11:35:41AM -0700, Joshua D. Drake wrote:
> On 07/06/2018 11:27 AM, Larry Rosenman wrote:
> > when I pg_dump -Fc the database and then try to restore it after a
> > create database, I get errors.  To get a clean restare I need to do:
>
> Knowing the errors would be helpful.
>
> jD
ler=# drop database wm_common;create database wm_common
DROP DATABASE
ler-# ;
CREATE DATABASE
ler=# \q
borg.lerctr.org /home/ler $ pg_restore -d wm_common wm_t
borg.lerctr.org /home/ler $ cd WM
borg.lerctr.org /home/ler/WM $ pg_restore -d wm_common wm_test.dump
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.
    Command was: CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder WITH SCHEMA tiger;



pg_restore: [archiver (db)] Error from TOC entry 5400; 0 0 COMMENT EXTENSION postgis_tiger_geocoder
pg_restore: [archiver (db)] could not execute query: ERROR:  extension "postgis_tiger_geocoder" does not exist
    Command was: COMMENT ON EXTENSION postgis_tiger_geocoder IS 'PostGIS tiger geocoder and reverse geocoder';



pg_restore: [archiver (db)] Error from TOC entry 11; 3079 887754 EXTENSION postgis_topology
pg_restore: [archiver (db)] could not execute query: ERROR:  type "geometry" does not exist
    Command was: CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;



pg_restore: [archiver (db)] Error from TOC entry 5401; 0 0 COMMENT EXTENSION postgis_topology
pg_restore: [archiver (db)] could not execute query: ERROR:  extension "postgis_topology" does not exist
    Command was: COMMENT ON EXTENSION postgis_topology IS 'PostGIS topology spatial types and functions';


>
>
> > ---
> > \set DB `echo ${DB}`
> > CREATE SCHEMA IF NOT EXISTS postgis;
> > CREATE SCHEMA IF NOT EXISTS topology;
> > CREATE SCHEMA IF NOT EXISTS tiger;
> > SET search_path=public,postgis,tiger,topology;
> > ALTER DATABASE :DB SET search_path=public,postgis,tiger,topology;
> > \c
> > CREATE EXTENSION fuzzystrmatch schema postgis;
> > -- Enable PostGIS (includes raster)
> > CREATE EXTENSION postgis schema postgis;
> > -- Enable Topology
> > CREATE EXTENSION postgis_topology schema topology;
> > -- Enable PostGIS Advanced 3D
> > -- and other geoprocessing algorithms
> > CREATE EXTENSION postgis_sfcgal schema postgis;
> > -- rule based standardizer
> > CREATE EXTENSION address_standardizer schema postgis;
> > -- example rule data set
> > CREATE EXTENSION address_standardizer_data_us schema postgis;
> > -- Enable US Tiger Geocoder
> > CREATE EXTENSION postgis_tiger_geocoder schema tiger;
> > -- routing functionality
> > CREATE EXTENSION pgrouting schema postgis;
> > -- spatial foreign data wrappers
> > CREATE EXTENSION ogr_fdw schema postgis;
> > -- LIDAR support
> > CREATE EXTENSION pointcloud schema postgis;
> > -- LIDAR Point cloud patches to geometry type cases
> > CREATE EXTENSION pointcloud_postgis schema postgis;
> > ----
> > Is the need to do this expected?
> >
> > This is 10.4 on FreeBSD.
> >
> >
> >
>
> --
> Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
> ***  A fault and talent of mine is to tell it exactly how it is.  ***
> PostgreSQL centered full stack support, consulting and development.
> Advocate: @amplifypostgres || Learn: https://postgresconf.org
> *****     Unless otherwise stated, opinions are my own.   *****
>

--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: ler@lerctr.org
US Mail: 5708 Sabbia Drive, Round Rock, TX 78665-2106

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

> 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. 

"Regina Obe" <lr@pcorp.us> writes:
> 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

OK.

> 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. 

You don't really need any new syntax for this particular case, I think.
You can declare the function in the extension like this:

create function ... set search_path from current;

which will cause it to absorb the search path that's set while running
the extension script, which should be what you want.

            regards, tom lane

> 
> > 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.
> 
> You don't really need any new syntax for this particular case, I think.
> You can declare the function in the extension like this:
> 
> create function ... set search_path from current;
> 
> which will cause it to absorb the search path that's set while running the
> extension script, which should be what you want.
> 
>             regards, tom lane

But then the search_path would be local variable to the function.  Wouldn't
that impact performance?

We had originally tried that in PostGIS functions (well not that but
explicitly setting the functions local search path to where postgis is
installed by adding a search_path variable to the function)
And things got 10 times slower.

"Regina Obe" <lr@pcorp.us> writes:
>> You don't really need any new syntax for this particular case, I think.
>> You can declare the function in the extension like this:
>> create function ... set search_path from current;

> But then the search_path would be local variable to the function.  Wouldn't
> that impact performance?

Yeah, but it would *work*.  Never put performance before functionality.

> We had originally tried that in PostGIS functions (well not that but
> explicitly setting the functions local search path to where postgis is
> installed by adding a search_path variable to the function)
> And things got 10 times slower.

I can imagine that you'd take a noticeable hit for SQL functions that'd
otherwise be inline-able, but I doubt that it makes much difference for
index functions.

            regards, tom lane