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.

[strk]

What about getting the function in w/out any handling of "raster" / "noraster" for now, given there's no such thing as those version modifiers yet ?

[robe]

I'm working on one. I figure I should have a pull request like yours so I can learn about checkout and git branching and tagging. If it doesn't go then I'll just commit without the noraster bit as you suggest.

​https://git.osgeo.org/gogs/robe/postgis/src/postgis-extension-noraster

also make sure there are no flaws in my approach which I hadn't thought of. Then people can test both approaches and see which they prefer.

[strk]

I think it still makes sense to have 2 commits. The simple one would be for the current case. IFF something else is committed, it can include a change in the function.

Note that my solution would need a different change to that script…

[robe]

In 15970:

postgis_extensions_upgrade(). Can't remember which postgis extensions you installed and are two lazy to figure out and upgrade each with ALTER EXTENSION.
just do

SELECT postgis_extensions_upgrade();

Closes #3896

[robe]

I didn't include noraster in this version, but realized I should probably include the special case of dev upgrade. when both installed version and default version have "dev" in the name.

So dev should always upgrade regardless of if it is the same as the default_version.

Soo if installed_version has dev in the name (but no next), we should upgrade to the "next" version. If it has "next" then take out the "next" if it has a "next". This we can test as part of our upgrade tests.

[strk]

I think it would be nice to always get back right after going to "next" so it never happens that a user is left with the "next" version advertised in the pg_extension.

[robe]

In 15971:

(The changeset message doesn't reference this ticket)

Sorry I had put the comment on wrong ticket in my commit.

strk so now that I do

SELECT postgis_extensions_upgrade();

If I am already at latest dev it goes thru the next loop so my output and notification looks like

'POSTGIS="2.5.0dev r15970" [EXTENSION] PGSQL="100" GEOS="3.7.0dev-CAPI-1.11.0 8fe2ce6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.2, released 2017/09/15" LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" RASTER (raster procs from "2.5.0dev r15968" need upgrade)'

NOTICE:  ALTER EXTENSION postgis UPDATE TO "2.5.0devnext";
NOTICE:  ALTER EXTENSION postgis UPDATE TO "2.5.0dev";
Successfully run. Total query runtime: 838 msec.
1 rows affected.

I'm a bit puzzled though why my raster is showing a require upgrade and didn't get upgrade to the latest, but I suspect that is a different issue like something not being rebuilt.

[robe]

In 15972:

missed last execute statement.
references #3896

[strk]

Can we have run_test.pl test this path when using —extension and —upgrade ? Possibly as an additional step (to be well thought)

[strk]

Robe can this ticket be closed as completed ? The idea to add the call to run_test.pl can go in another ticket, eventually. Did the function include documentation and NEWS item ?

[komzpa]

2.5.0 can't advertise it since upgrading from 2.4 can't be done using it, so it's fixed for now.

[strk]

Documentation and NEWS item are still missing, as far as I can tell.

[strk]

Sorry, now I see it is documented, I was just puzzled by MixedCase _and_ plural (extensions_upgrade)