[postgis-users] Importing relations from database where postgis extension was created with a different schema

[Regina Obe]

Okay I just tested installing postgis in public schema.  Then creating a whole bunch of foreign tables with ogr_fdw and confirmed the geometries showed as public.geometry and confirmed I could query with ST_SetSRID  and ST_Transform.

Then I moved postgis to postgis schema, making sure to add postgis to the database search_path first.
Opened a new database connection as was able to open up my ogr tables fine.

Now if I remove postgis schema from database search path and reconnect, then my database crashes if I try to query an ogr_fdw table.

-- so basic steps

CREATE EXTENSION postgis;

CREATE SERVER svr_shp FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (datasource 'C:/fdw_data/massgis/shps',
 format 'ESRI Shapefile'
);
CREATE SCHEMA shps;

IMPORT FOREIGN SCHEMA ogr_all
FROM SERVER svr_shp INTO shps;

-- then disconnect from database and reopen.  
-- this works fine
SELECT ST_Transform(geom, 4269) from shps.towns_poly limit 10;

CREATE SCHEMA postgis;

ALTER DATABASE test_db
SET search_path = public, postgis;

ALTER EXTENSION postgis 
  SET SCHEMA postgis;
 
ALTER EXTENSION postgis 
  UPDATE TO "2.4.1next";
 
ALTER EXTENSION postgis 
  UPDATE TO "2.4.1";

-- disconnect and make new connection
-- if you are using pgAdmin, make sure to refresh as pgAdmin will show a cached structure from before.
-- inspected table shps.towns_poly using pgAdmin and confirmed geometry column now reads postgis.geometry
-- ran query

-- then disconnect from database and reopen.  
-- this works fine
SELECT ST_Transform(geom, 4269) from shps.towns_poly limit 10;


-- now if I remove postgis from search_path by either removing it from my database search_path and reconnecting.
-- or doing

set search_path=public;

-- returns true -- all nulls - aka bad
SELECT postgis.ST_Transform(geom, 4269) is null from shps.towns_poly limit 10;

-- this one is a bit weird in that it still requires me to disconnect and reconnect before trying, as there still seems to be a shared read hook on table if I try to reuse the same connection.  Without disconnecting I sometimes get invalid transform (all nulls back) and a crash in one case.

set search_path=public,postgis;

-- returns false -- no nulls aka good
SELECT postgis.ST_Transform(geom, 4269) is null from shps.towns_poly limit 10;

Anyway I am able to repeat the above to switch postgis back to public and all seems fine as far as I can tell.





-----Original Message-----
From: Regina Obe [mailto:lr at pcorp.us] 
Sent: Wednesday, November 08, 2017 7:52 PM
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
Subject: RE: [postgis-users] Importing relations from database where postgis extension was created with a different schema

You can move it back I've done that before.

Though I'm puzzled how this broke ogr_fdw.  I do recall some code in ogr_fdw that looks for where postgis is installed.

It looks for it in search_path 


https://github.com/pramsey/pgsql-ogr-fdw/blob/07238c75d8bee08acb14619c413495a6965405d6/ogr_fdw.c#L187

Are you sure you have the schema that postgis resides in in your database search_path?


The error might be a temporary one caused by open connections during the move.  The issue should resolve if you kill all connections or restart the database service.

I'll try to replicate the issue on my end.  When did you get the proj error below?  Was it during querying a foreign table or a physical one?




Thanks,
Regina




-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Andrew Joseph
Sent: Wednesday, November 08, 2017 2:12 PM
To: postgis-users at lists.osgeo.org
Subject: Re: [postgis-users] Importing relations from database where postgis extension was created with a different schema

I just learned that the hard way: all my ogr_fdw foreign tables broke after the relocation. I attempted moving the extension back to the public schema
using:

UPDATE pg_extension
  SET extrelocatable = TRUE
    WHERE extname = 'postgis';

ALTER EXTENSION postgis
  SET SCHEMA public;

ALTER EXTENSION postgis UPDATE TO "2.4.0devnext";

ALTER EXTENSION postgis UPDATE TO "2.4.0dev";

UPDATE pg_extension
  SET extrelocatable = FALSE
    WHERE extname = 'postgis';

However, it appears this doesn't actually move the relations back to the proper schema, and also seems to have corrupted proj4 in a way that persists across databases:

[2017-11-08 13:04:00] [XX000] ERROR: AddToPROJ4SRSCache: could not parse
proj4 string '+proj=lcc +lat_1=31.88333333333333 +lat_2=30.11666666666667
+lat_0=29.66666666666667 +lon_0=-100.3333333333333 
++x_0=699999.9998983998
+y_0=3000000 +datum=NAD83 +units=us-ft +no_defs ' unknown elliptical
parameter name

Is it safe to assume moving the schema using the methods you outlined is a one-time only operation (i.e you can't move it back after importing the relevant data)?
 



--
Sent from: http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users