postgis_extensions_upgrade function to upgrade all your packaged postgis extensions

[robe]

One of the annoyances of having so many postgis extensions is having to remember to upgrade all of them when you install a new postgis.

This function would check to see what extensions of postgis you have installed and upgrade them to the latest. I put in the noraster bit with the mindset that if we encode the noraster version in the version as I had proposed

then this function can be used to check which version of postgis a user has and upgrade them to the right one (with or without raster). So if they have the noraster edition, it would upgrade them to the next noraster version regardless what the default is set to in postgis.control. That would allow us to make the noraster be the default and users who want raster, would have to install using

CREATE EXTENSION postgis VERSION "2.5.0";

CREATE OR REPLACE FUNCTION postgis_extensions_upgrade() RETURNS text AS
$$
BEGIN
 DECLARE rec record; BEGIN for rec in SELECT 'ALTER EXTENSION ' || name || ' UPDATE TO "' || CASE WHEN installed_version ILIKE '%noraster' THEN replace(default_version,'noraster','') ||'noraster' ELSE replace(default_version,'noraster','') END    || '";' AS sql
FROM pg_available_extensions 
WHERE installed_version > '' AND name IN('address_standardizer', 'postgis', 'postgis_sfcgal', 'postgis_tiger_geocoder', 'postgis_topology')
and ( replace(default_version,'noraster','') <> replace(installed_version, 'noraster','') ) LOOP

    EXECUTE rec.sql; 
    RAISE NOTICE '%', rec.sql;
END LOOP; END;

RETURN postgis_full_version();

END 

$$ language plpgsql;

I tested using a database that has sfcgal and postgis installed at 2.3.1

NOTICE showed this:

NOTICE:  ALTER EXTENSION postgis UPDATE TO "2.4.0";
CONTEXT:  PL/pgSQL function postgis_extension_upgrade() line 9 at RAISE
NOTICE:  ALTER EXTENSION postgis_sfcgal UPDATE TO "2.4.0";
CONTEXT:  PL/pgSQL function postgis_extension_upgrade() line 9 at RAISE

We could also maybe allow the function to take in an argument.

[strk]

Sorry I'm not clear about the noraster replace. What it is for ? Should we avoid doing any of that for now as there's no such "noraster" flavor ?

Instead, can you add support for upgrading to "next" and back when the current version has a "dev" substring ?

[strk]

Also, it could be useful if this function would also check for unpackaged extensions and raised a WARNING about that: "extension XXX is unpackaged, so it won't be possible to upgrade with this function, HINT: create from unpackaged…"

[robe]

strk the noraster replace, is so it can just compare the version part.

So if you are at 2.5.0noraster, then you shouldn't be upgrade to 2.5.0.

If you are at 2.5.0noraster and new version is 2.6.0, then you should be upgraded to 2.6.0noraster regardless of what the postgis.control file says.

Similarly if you were at 2.5.0 and your postgis.control default is 2.6.0noraster, you should be upgraded to 2.6.0 (so you still have your raster functionality).

I'm thinking once we have this in place, we could actually switch the default at 2.6.0 to the noraster version without paying any penalty.

Because only users creating from scratch would have the noraster version. Existing users would continue to have the regular version unless they didn't use this function, in which case when they do.

ALTER EXTENSION postgis UPDATE;

Our update script would look to see if raster_columns is empty, and if it is, it would throw an error naturally because it can't drop raster support if tables rely on it.

Otherwise it would just drop with a notice saying

We noticed you are not using raster, we have dropped it for you.
If you want it back, run 

ALTER EXTENSION postgis UPDATE TO "2.6.0";

At that point we could even call it "2.6.0withraster"

Then come 3.0 the task of splitting the two extensions will be much easier and no one would notice because we'd have gotten everyone used to using our helper function and we can do anything we want in the function like — have a dummy postgis_raster extension with nothing in it and manually move raster functions/data by querying the postgis extension catalog. So the extension would be called postgis_raster VERSION "3.0.0presplit"; and have nothing in it.

and then ALTER EXTENSION postgis_raster UPDATE VERSION "3.0.0" would update all the functions, types etc in it using our regular script.

[robe]

I updated the function to return postgis_full_version() details of their install.