[postgis-tickets] r16880 - Have postgis_extensions_upgrade() package unpackaged extensions
Sandro Santilli
strk at kbt.io
Wed Oct 10 01:36:51 PDT 2018
Author: strk
Date: 2018-10-10 13:36:51 -0700 (Wed, 10 Oct 2018)
New Revision: 16880
Modified:
trunk/doc/reference_management.xml
trunk/postgis/postgis.sql.in
Log:
Have postgis_extensions_upgrade() package unpackaged extensions
See #4196
NOTE: it doesn't re-package tiger geocoder because I would not
know how to tell if the support for it is installed and
it does't re-package topology due to bug #2503
Modified: trunk/doc/reference_management.xml
===================================================================
--- trunk/doc/reference_management.xml 2018-10-08 23:19:50 UTC (rev 16879)
+++ trunk/doc/reference_management.xml 2018-10-10 20:36:51 UTC (rev 16880)
@@ -348,9 +348,10 @@
<refnamediv>
<refname>PostGIS_Extensions_Upgrade</refname>
- <refpurpose>Upgrades installed postgis packaged extensions (e.g. postgis_sfcgal, postgis_topology, postgis_sfcgal)
- to latest installed version. Reports full postgis version and build configuration
- infos after.</refpurpose>
+ <refpurpose>
+Packages and upgrades postgis extensions (e.g. postgis_sfcgal,
+postgis_topology, postgis_sfcgal) to latest available version.
+ </refpurpose>
</refnamediv>
<refsynopsisdiv>
@@ -366,17 +367,18 @@
<refsection>
<title>Description</title>
- <para>Upgrades installed postgis packaged extensions
- to latest installed version. Only extensions you have installed in the database will be upgraded and if they are already at last installed version,
- they will not be upgraded. Reports full postgis version and build configuration
- infos after. This is short-hand for doing multiple ALTER EXTENSION .. UPDATE for each postgis extension.
+ <para>Packages and upgrades postgis extensions
+ to latest version. Only extensions you have installed in the
+ database will be packaged and upgraded if needed.
+ Reports full postgis version and build configuration infos after.
+ This is short-hand for doing multiple CREATE EXTENSION .. FROM
+ unpackaged and ALTER EXTENSION .. UPDATE for each postgis extension.
Currently only tries to upgrade extensions postgis,
postgis_raster, postgis_sfcgal, postgis_topology, and postgis_tiger_geocoder.</para>
<para>Availability: 2.5.0</para>
<note>
- <para>Changed: 3.0.0 to support separate postgis_raster extension
- and repackage loose raster functions into postgis_raster.</para>
+ <para>Changed: 3.0.0 to repackage loose extensions and support postgis_raster.</para>
</note>
</refsection>
@@ -385,15 +387,19 @@
<programlisting>SELECT PostGIS_Extensions_Upgrade();</programlisting>
<screen>
-NOTICE: ALTER EXTENSION postgis_tiger_geocoder UPDATE TO "2.5.0dev";
-NOTICE: ALTER EXTENSION postgis_topology UPDATE TO "2.5.0dev";
+NOTICE: Packaging extension postgis
+NOTICE: Packaging extension postgis_raster
+NOTICE: Packaging extension postgis_sfcgal
+NOTICE: Extension postgis_topology is not available or not packagable for some reason
+NOTICE: Extension postgis_tiger_geocoder is not available or not packagable for some reason
postgis_extensions_upgrade
----------------------------------------------------------------------------------
-POSTGIS="2.5.0dev r15966" [EXTENSION] PGSQL="100"
-GEOS="3.7.0dev-CAPI-1.11.0 8fe2ce6" SFCGAL="1.3.1"
-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" TOPOLOGY RASTER
+POSTGIS="3.0.0dev r16878" [EXTENSION] PGSQL="96"
+GEOS="3.8.0dev-CAPI-1.11.0 a67b6f86" SFCGAL="1.3.5" PROJ="Rel. 4.9.3,
+15 August 2016" GDAL="GDAL 2.3.0dev, released 2017/99/99"
+LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" TOPOLOGY
+[UNPACKAGED!] RASTER
(1 row)</screen>
</refsection>
Modified: trunk/postgis/postgis.sql.in
===================================================================
--- trunk/postgis/postgis.sql.in 2018-10-08 23:19:50 UTC (rev 16879)
+++ trunk/postgis/postgis.sql.in 2018-10-10 20:36:51 UTC (rev 16880)
@@ -2825,51 +2825,78 @@
AS $$
DECLARE rec record; sql text; var_schema text;
BEGIN
- -- if at a version different from default version or we are at a dev version,
- -- then do an upgrade to default version
- FOR rec in SELECT name, default_version, installed_version
- FROM pg_available_extensions
- WHERE installed_version > '' AND name IN(
+ FOR rec IN
+ SELECT name, default_version, installed_version
+ FROM pg_catalog.pg_available_extensions
+ WHERE name IN (
'postgis',
'postgis_raster',
'postgis_sfcgal',
- 'postgis_tiger_geocoder',
- 'postgis_topology')
- AND ( default_version <> installed_version OR
- ( default_version = installed_version AND
- default_version ILIKE '%dev%' AND
- installed_version ILIKE '%dev%' ) )
+ 'postgis_topology',
+ 'postgis_tiger_geocoder'
+ )
+ ORDER BY length(name) -- this is to make sure 'postgis' is first !
LOOP
+ IF rec.installed_version IS NULL THEN
+ -- If the support installed by available extension
+ -- is found unpackaged, we package it
+ IF
+ -- PostGIS is always available (this function is part of it)
+ rec.name = 'postgis'
- -- we need to upgrade to next so our installed is different from current
- -- and then we can upgrade to default_version
- IF rec.installed_version = rec.default_version THEN
- sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' || quote_ident(rec.default_version || 'next') || ';';
- EXECUTE sql;
- RAISE NOTICE '%', sql;
- END IF;
+ -- PostGIS raster is available if type 'raster' exists
+ OR ( rec.name = 'postgis_raster' AND EXISTS (
+ SELECT 1 FROM pg_catalog.pg_type
+ WHERE typname = 'raster' ) )
- sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' || quote_ident(rec.default_version) || ';';
- EXECUTE sql;
- RAISE NOTICE '%', sql;
+ -- PostGIS SFCGAL is availble if
+ -- 'postgis_sfcgal_version' function exists
+ OR ( rec.name = 'postgis_sfcgal' AND EXISTS (
+ SELECT 1 FROM pg_catalog.pg_proc
+ WHERE proname = 'postgis_sfcgal_version' ) )
- -- If postgis raster functions still exist, but postgis_raster extension is not installed
- -- then repackage as postgis_raster
- IF rec.name = 'postgis'
- AND NOT EXISTS(SELECT 1 FROM pg_extension WHERE extname='postgis_raster')
- AND EXISTS(SELECT 1 FROM pg_proc WHERE proname LIKE 'raster%') THEN
- SELECT n.nspname INTO var_schema
- FROM pg_catalog.pg_extension AS e
- INNER JOIN pg_catalog.pg_namespace AS n ON e.extnamespace = n.oid
- WHERE e.extname = 'postgis';
+ -- PostGIS Topoology is available if
+ -- 'topology.topology' table exists
+ -- NOTE: disabled due to https://trac.osgeo.org/postgis/ticket/2503
+ OR ( FALSE AND rec.name = 'postgis_topology' AND EXISTS (
+ SELECT 1 FROM pg_catalog.pg_class c
+ JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid )
+ WHERE n.nspname = 'topology' AND c.relname = 'topology') )
- sql = 'CREATE EXTENSION postgis_raster FROM unpackaged SCHEMA ' || quote_ident(var_schema) || ';';
+ -- TODO: How do we tell if PostGIS Tiger Geocoder is available ?
+ THEN
+ sql = 'CREATE EXTENSION ' || rec.name || ' FROM unpackaged';
+ RAISE NOTICE 'Packaging extension %', rec.name;
+ RAISE DEBUG '%', sql;
+ EXECUTE sql;
+ ELSE
+ RAISE NOTICE 'Extension % is not available or not packagable for some reason', rec.name;
+ END IF;
+ ELSIF rec.default_version > rec.installed_version
+ THEN
+ sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' ||
+ quote_ident(rec.default_version) || ';';
+ RAISE NOTICE 'Updating extension % from % to %',
+ rec.name, rec.installed_version, rec.default_version;
+ RAISE DEBUG '%', sql;
EXECUTE sql;
-
+ ELSIF rec.default_version = rec.installed_version AND
+ rec.installed_version ILIKE '%dev%'
+ THEN
+ -- we need to upgrade to next and back
+ RAISE NOTICE 'Updating dev extension % %',
+ rec.name, rec.installed_version;
+ sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' ||
+ quote_ident(rec.default_version || 'next') || ';';
+ RAISE DEBUG '%', sql;
+ EXECUTE sql;
+ sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' ||
+ quote_ident(rec.default_version) || ';';
+ RAISE DEBUG '%', sql;
+ EXECUTE sql;
END IF;
-
END LOOP;
RETURN @extschema at .postgis_full_version();
More information about the postgis-tickets
mailing list