Thread: "interesting" issue with restore from a pg_dump with a database-widesearch_path
"interesting" issue with restore from a pg_dump with a database-widesearch_path
From
Larry Rosenman
Date:
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
Attachment
Re: "interesting" issue with restore from a pg_dump with adatabase-wide search_path
From
"Joshua D. Drake"
Date:
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. *****
Re: "interesting" issue with restore from a pg_dump with adatabase-wide search_path
From
Larry Rosenman
Date:
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
Attachment
Re: "interesting" issue with restore from a pg_dump with a database-wide search_path
From
Tom Lane
Date:
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
RE: "interesting" issue with restore from a pg_dump with a database-wide search_path
From
"Regina Obe"
Date:
> 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.
Re: "interesting" issue with restore from a pg_dump with a database-wide search_path
From
Tom Lane
Date:
"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
RE: "interesting" issue with restore from a pg_dump with a database-wide search_path
From
"Regina Obe"
Date:
> > > 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.
Re: "interesting" issue with restore from a pg_dump with a database-wide search_path
From
Tom Lane
Date:
"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