installing postgis_raster from unpackaged tries to use wrong schema

[robe]

I'm not sure if this is because I wasn't using the latest PostGIS 2.5.

I tried to upgrade a database that has installed PostGIS 2.5.1 (in postgis schema) to PostGIS 3.0.0.

Using these commands:

SELECT postgis_extensions_upgrade();

As expected it was successful, but told me that raster is now unbundled so needs to be rebundled, and to run

SELECT postgis_extensions_upgrade();

On the bundling run, I got this notice

NOTICE:  Extension postgis_sfcgal is not available or not packagable for some reason
NOTICE:  Packaging extension postgis_raster
WARNING:  'postgis.gdal_datapath' is already set and cannot be changed until you reconnect
WARNING:  'postgis.gdal_enabled_drivers' is already set and cannot be changed until you reconnect
WARNING:  'postgis.enable_outdb_rasters' is already set and cannot be changed until you reconnect

ERROR:  function tiger.st_srid(geometry) does not exist
LINE 1: ..._makeemptyraster($1, $2, $3, $4, $5, -($5), 0, 0, tiger.ST_S...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:   SELECT  tiger.ST_makeemptyraster($1, $2, $3, $4, $5, -($5), 0, 0, tiger.ST_SRID('POINT(0 0)'::geometry)) 
CONTEXT:  SQL statement "CREATE EXTENSION postgis_raster FROM unpackaged"
PL/pgSQL function postgis_extensions_upgrade() line 48 at EXECUTE
SQL state: 42883

I think tiger might change path during upgrade, so guessing that's why it picked that up. So I thought I'll just start a new connection and run the upgrade process again.

And what happened is it tried to install in default location:

NOTICE:  Extension postgis_sfcgal is not available or not packagable for some reason
NOTICE:  Packaging extension postgis_raster
WARNING:  'postgis.gdal_datapath' is already set and cannot be changed until you reconnect
WARNING:  'postgis.gdal_enabled_drivers' is already set and cannot be changed until you reconnect
WARNING:  'postgis.enable_outdb_rasters' is already set and cannot be changed until you reconnect

ERROR:  function public.st_srid(geometry) does not exist
LINE 1: ..._makeemptyraster($1, $2, $3, $4, $5, -($5), 0, 0, public.ST_...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:   SELECT  public.ST_makeemptyraster($1, $2, $3, $4, $5, -($5), 0, 0, public.ST_SRID('POINT(0 0)'::geometry)) 
CONTEXT:  SQL statement "CREATE EXTENSION postgis_raster FROM unpackaged"
PL/pgSQL function postgis_extensions_upgrade() line 48 at EXECUTE
SQL state: 42883

So it seems our postgis_extensions_upgrade, instead of trying to install in current default path, should do something like:

CREATE EXTENSION postgis_raster FROM unpackaged SCHEMA <path-that-postgis-is-installed-in>

Indeed I got around the issue by doing this:

CREATE EXTENSION postgis_raster FROM unpackaged SCHEMA postgis;

[Regina Obe <lr@…>]

In 7613d9ae/git:

postgis_raster installed from unpackaged needs to specify schema of postgis. References #4581 for master PostGIS 3.1

[robe]

I was thinking I should only enforce the postgis schema only if the extension is postgis_raster or postgis_sfcgal, but I dismissed that concern because if a schema is explicitly specified in the control file (as is the case with postgis_topology and postgis_tiger_geocoder), then the SCHEMA … part of CREATE EXTENSION I think is ignored UNLESS it has a CASCADE clause. Then I think in the case of CASCADE, all extensions the extension relies on that don't have a schema specified, use the one CREATE EXTENSION specifies. I'm going to confirm that before I commit to PostGIS 3.0 branch.

[Regina Obe <lr@…>]

In d8a3b2e/git:

Don't try to specify schema if postgis extension itself is being installed. References #4581 for PostGIS 3.1 master

[Regina Obe <lr@…>]

In eacf0c5/git:

Don't try to set schema on extensions that have a schema specified (postgis_topology, postgis_tiger_geocoder). References #4581

[strk]

Regina will this code try to package postgis_topology in the wrong schema ?

[robe]

Hmm I have to test that one out. I explicitly left out postgis_topology so it won't do anything differnt than it did before.

[robe]

I updated PostGIS 3.1 / master in previous commits but have not backported to 3.0.1 yet. I'll retest with postgis_topology.

strk I don't think postgis_topology needs a schema specified as I think it will still read the postgis_topology.control file and since it's schema forced to topology schema, the SCHEMA part is not needed anyway. Same goes with postgis_tiger_geocoder.

[Regina Obe <lr@…>]

In 75d0d13/git:

postgis_extensions_upgrade change to allow repackaging postgis_tiger_geocoder and dealing with non-standard schema postgis installs. References #4610 and Closes #4581 for PostGIS 3.0.1