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:[email protected]] Sent: Wednesday, November 08, 2017 7:52 PM To: 'PostGIS Users Discussion' <[email protected]> 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:[email protected]] On Behalf Of Andrew Joseph Sent: Wednesday, November 08, 2017 2:12 PM To: [email protected] 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 [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
