[postgis-tickets] [SCM] PostGIS branch stable-3.2 updated. 3.2.0-3-g82d84900f
git at osgeo.org
git at osgeo.org
Sun Dec 19 07:21:25 PST 2021
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, stable-3.2 has been updated
via 82d84900fdd26678767fee72677664f3f76b3db6 (commit)
from 9f4eff0600e70153c1647f9597e39e34c74c349f (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 82d84900fdd26678767fee72677664f3f76b3db6
Author: Sandro Santilli <strk at kbt.io>
Date: Tue Dec 14 22:54:55 2021 +0100
Allow upgrades in presence of views using deprecated functions
Support adding a 'Replaces' comment above functions to improve upgrade
When a function has a line like the following in in the comments
above its signature:
Replaces ST_Intersection(geometry, geometry) deprecated in 3.1.0
The upgrade script will try to drop the deprecated functions and in case
of failure leave them behind renamed with a _deprecated_by_postgis_<version>
suffix, warns the user about such occurrence and gives her hints on
how to fix.
The postgis_full_version() will report presence of such deprecated
functions as an "incomplete upgrade".
References #5033 in 3.2 branch (3.2.1dev)
Includes regression tests
diff --git a/NEWS b/NEWS
index 454281d12..cedae046c 100644
--- a/NEWS
+++ b/NEWS
@@ -1,3 +1,10 @@
+PostGIS 3.2.1dev
+YYYY/MM/DD
+
+* Bug Fixes *
+ - #5033, allow upgrades in presence of views using deprecated
+ functions (Sandro Santilli)
+
PostGIS 3.2.0 (Olivier Courtin Edition)
2021/12/17
This version requires PostgreSQL 9.6 or higher, GEOS 3.6 or higher
diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in
index f54bf20cc..003d8b60f 100644
--- a/postgis/postgis.sql.in
+++ b/postgis/postgis.sql.in
@@ -2926,7 +2926,10 @@ $$ LANGUAGE 'sql' STABLE;
-- Changed: 3.0.0 also upgrade postgis_raster if it exists
CREATE OR REPLACE FUNCTION postgis_extensions_upgrade() RETURNS text
AS $$
-DECLARE rec record; sql text; var_schema text;
+DECLARE
+ rec record;
+ sql text;
+ var_schema text;
BEGIN
FOR rec IN
@@ -2940,7 +2943,7 @@ BEGIN
'postgis_tiger_geocoder'
)
ORDER BY length(name) -- this is to make sure 'postgis' is first !
- LOOP
+ LOOP --{
IF rec.installed_version IS NULL THEN
-- If the support installed by available extension
-- is found unpackaged, we package it
@@ -3020,7 +3023,7 @@ BEGIN
EXECUTE sql;
END IF;
- END LOOP;
+ END LOOP; --}
RETURN 'Upgrade completed, run SELECT postgis_full_version(); for details';
@@ -3211,6 +3214,12 @@ BEGIN
fullver = fullver || ' (sfcgal procs from "' || sfcgal_scr_ver || '" need upgrade)';
END IF;
+ -- Check for the presence of deprecated functions
+ IF EXISTS ( SELECT oid FROM pg_catalog.pg_proc WHERE proname LIKE '%_deprecated_by_postgis_%' )
+ THEN
+ fullver = fullver || ' (deprecated functions exist, upgrade is not complete)';
+ END IF;
+
RETURN fullver;
END
$$
@@ -3479,6 +3488,7 @@ CREATE OR REPLACE FUNCTION ST_IsValidTrajectory(geometry)
---------------------------------------------------------------
-- Changed: 3.1.0 to add gridSize default argument
+-- Replaces ST_Intersection(geometry, geometry) deprecated in 3.1.0
CREATE OR REPLACE FUNCTION ST_Intersection(geom1 geometry, geom2 geometry, gridSize float8 DEFAULT -1)
RETURNS geometry
AS 'MODULE_PATHNAME','ST_Intersection'
@@ -3622,6 +3632,7 @@ CREATE OR REPLACE FUNCTION ST_MaximumInscribedCircle(geometry, OUT center geomet
-- PostGIS equivalent function: ST_difference(geom1 geometry, geom2 geometry)
-- Changed: 3.1.0 to add gridSize default argument
+-- Replaces ST_Difference(geometry, geometry) deprecated in 3.1.0
CREATE OR REPLACE FUNCTION ST_Difference(geom1 geometry, geom2 geometry, gridSize float8 DEFAULT -1.0)
RETURNS geometry
AS 'MODULE_PATHNAME','ST_Difference'
@@ -3644,6 +3655,7 @@ CREATE OR REPLACE FUNCTION ST_Points(geometry)
-- PostGIS equivalent function: symdifference(geom1 geometry, geom2 geometry)
-- Changed: 3.1.0 to add gridSize default argument
+-- Replaces ST_SymDifference(geometry, geometry) deprecated in 3.1.0
CREATE OR REPLACE FUNCTION ST_SymDifference(geom1 geometry, geom2 geometry, gridSize float8 DEFAULT -1.0)
RETURNS geometry
AS 'MODULE_PATHNAME','ST_SymDifference'
@@ -3671,6 +3683,7 @@ CREATE OR REPLACE FUNCTION ST_Union(geom1 geometry, geom2 geometry, gridSize flo
-- Availability: 2.0.0
-- Changed: 3.1.0 to add gridSize default argument
+-- Replaces ST_UnaryUnion(geometry) deprecated in 3.1.0
CREATE OR REPLACE FUNCTION ST_UnaryUnion(geometry, gridSize float8 DEFAULT -1.0)
RETURNS geometry
AS 'MODULE_PATHNAME','ST_UnaryUnion'
@@ -3699,6 +3712,7 @@ CREATE OR REPLACE FUNCTION ST_ClipByBox2d(geom geometry, box box2d)
-- Availability: 2.2.0
-- Changed: 3.1.0 to add gridSize default argument
+-- Replaces ST_Subdivide(geometry, integer) deprecated in 3.1.0
CREATE OR REPLACE FUNCTION ST_Subdivide(geom geometry, maxvertices integer DEFAULT 256, gridSize float8 DEFAULT -1.0)
RETURNS setof geometry
AS 'MODULE_PATHNAME', 'ST_Subdivide'
@@ -4147,6 +4161,7 @@ CREATE AGGREGATE ST_MakeLine (geometry) (
-- Availability: 2.3.0
-- Changed: 3.2.0 added max_radius parameter
+-- Replaces ST_ClusterKMeans(geometry, integer) deprecated in 3.2.0
CREATE OR REPLACE FUNCTION ST_ClusterKMeans(geom geometry, k integer, max_radius float8 default null)
RETURNS integer
AS 'MODULE_PATHNAME', 'ST_ClusterKMeans'
diff --git a/postgis/postgis_before_upgrade.sql b/postgis/postgis_before_upgrade.sql
index 9c1a06829..fd104bc8e 100644
--- a/postgis/postgis_before_upgrade.sql
+++ b/postgis/postgis_before_upgrade.sql
@@ -217,13 +217,6 @@ DROP FUNCTION IF EXISTS st_buffer(geometry, double precision); -- Does not confl
DROP FUNCTION IF EXISTS ST_CurveToLine(geometry, integer); -- Does not conflict
DROP FUNCTION IF EXISTS ST_CurveToLine(geometry); -- Does not conflict
-DROP FUNCTION IF EXISTS st_intersection(geometry, geometry); -- replaced in 3.1.0 by 3 args version
-DROP FUNCTION IF EXISTS st_subdivide(geometry, integer); -- replaced in 3.1.0 by 3 args version
-DROP FUNCTION IF EXISTS st_difference(geometry, geometry); -- replaced in 3.1.0 by 3 args version
-DROP FUNCTION IF EXISTS st_symdifference(geometry, geometry); -- replaced in 3.1.0 by 3 args version
-DROP FUNCTION IF EXISTS st_unaryunion(geometry); -- replaced in 3.1.0 by 3 args version
-DROP FUNCTION IF EXISTS ST_ClusterKMeans(geometry, integer); -- replaced in 3.2.0 by 3 args version
-
-- geometry_columns changed parameter types so we verify if it needs to be dropped
-- We check the catalog to see if the view (geometry_columns) has a column
-- with name `f_table_schema` and type `character varying(256)` as it was
diff --git a/regress/hooks/hook-after-upgrade.sql b/regress/hooks/hook-after-upgrade.sql
index 459f80249..d7fb108a7 100644
--- a/regress/hooks/hook-after-upgrade.sql
+++ b/regress/hooks/hook-after-upgrade.sql
@@ -1,2 +1,5 @@
-DROP VIEW IF EXISTS upgrade_view_test;
+DROP VIEW IF EXISTS upgrade_view_test_overlay;
+DROP VIEW IF EXISTS upgrade_view_test_unaryunion;
+DROP VIEW IF EXISTS upgrade_view_test_subdivide;
+DROP VIEW IF EXISTS upgrade_view_test_union;
DROP TABLE upgrade_test;
diff --git a/regress/hooks/hook-before-upgrade.sql b/regress/hooks/hook-before-upgrade.sql
index d4bcb8cd6..de661643d 100644
--- a/regress/hooks/hook-before-upgrade.sql
+++ b/regress/hooks/hook-before-upgrade.sql
@@ -19,8 +19,31 @@ BEGIN
IF vernum >= 120000
THEN
RAISE DEBUG '12+ server (%)', vernum;
- CREATE VIEW upgrade_view_test AS
+ CREATE VIEW upgrade_view_test_union AS
SELECT ST_Union(g1) FROM upgrade_test;
END IF;
END;
$BODY$ LANGUAGE 'plpgsql';
+
+-- Add view using overlay functions
+CREATE VIEW upgrade_view_test_overlay AS
+SELECT
+ ST_Intersection(g1, g1) as geometry_intersection,
+ ST_Intersection(g2, g2) as geography_intersection,
+ ST_Difference(g1, g1) as geometry_difference,
+ ST_SymDifference(g1, g1) as geometry_symdifference
+FROM upgrade_test;
+
+-- Add view using unaryunion function
+-- NOTE: 2.0.0 introduced ST_UnaryUnion
+CREATE VIEW upgrade_view_test_unaryunion AS
+SELECT
+ ST_UnaryUnion(g1) as geometry_unaryunion
+FROM upgrade_test;
+
+-- Add view using unaryunion function
+-- NOTE: 2.2.0 introduced ST_Subdivide
+CREATE VIEW upgrade_view_test_subdivide AS
+SELECT
+ ST_Subdivide(g1, 256) as geometry_subdivide
+FROM upgrade_test;
diff --git a/utils/postgis_proc_upgrade.pl b/utils/postgis_proc_upgrade.pl
index 23cd81266..54071f2d3 100755
--- a/utils/postgis_proc_upgrade.pl
+++ b/utils/postgis_proc_upgrade.pl
@@ -4,7 +4,7 @@
# PostGIS - Spatial Types for PostgreSQL
# http://postgis.net
#
-# Copyright (C) 2014 Sandro Santilli <strk at kbt.io>
+# Copyright (C) 2014-2021 Sandro Santilli <strk at kbt.io>
# Copyright (C) 2009-2010 Paul Ramsey <pramsey at opengeo.org>
# Copyright (C) 2005 Refractions Research Inc.
#
@@ -49,6 +49,25 @@ sub parse_last_updated
return 0;
}
+sub parse_replaces
+{
+ my @replaces = ();
+ my $comment = shift;
+ my ($name, $args, $ver);
+ foreach my $line ( split /\n/, $comment )
+ {
+ if ( $line =~ m/.*Replaces\s\s*([^\(]*)\(([^\)]*)\)\s\s*deprecated in\s\s*([^\.]*)\.([^.]*)/ )
+ {
+ $name = $1;
+ $args = $2;
+ $ver = $3*100 + $4;
+ my @r = ($name, $args, $ver);
+ push @replaces, \@r;
+ }
+ }
+ return @replaces;
+}
+
sub parse_missing
{
my $comment = shift;
@@ -78,6 +97,8 @@ my $version_from_num = 0;
my $schema = "";
$schema = $ARGV[2] if @ARGV > 2;
+my @renamed_deprecated_functions = ();
+
die "Unable to open input SQL file $sql_file\n"
if ( !-f $sql_file );
@@ -168,14 +189,80 @@ while(<INPUT>)
if (/^create or replace function/i)
{
- print $_;
+ my $def .= $_;
+ my @replaced_array = parse_replaces($comment);
my $endfunc = 0;
while(<INPUT>)
{
- print $_;
+ $def .= $_;
$endfunc = 1 if /^\s*(\$\$\s*)?LANGUAGE /;
last if ( $endfunc && /\;/ );
}
+ foreach my $replaced (@replaced_array)
+ {
+ my ($name, $args, $ver) = @$replaced;
+ $name = lc($name); # lowercase the name
+ my $renamed = $name . '_deprecated_by_postgis_' . ${ver};
+ my $replacement = "${renamed}(${args})";
+ push @renamed_deprecated_functions, ${renamed};
+ print <<"EOF";
+-- Rename $name ( $args ) deprecated in PostGIS $ver
+DO LANGUAGE 'plpgsql'
+\$postgis_proc_upgrade\$
+DECLARE
+ replaced_proc regprocedure;
+ rec RECORD;
+ new_view_def TEXT;
+ sql TEXT;
+ detail TEXT;
+BEGIN
+ -- Check if the old function signature, exists
+ BEGIN
+ replaced_proc := '$name($args)'::regprocedure;
+ EXCEPTION
+ -- Catch the "function does not exist"
+ WHEN undefined_function THEN
+ RAISE DEBUG 'Function $name($args) does not exist';
+ WHEN OTHERS THEN
+ GET STACKED DIAGNOSTICS detail := PG_EXCEPTION_DETAIL;
+ RAISE EXCEPTION 'Got % (%)', SQLERRM, SQLSTATE
+ USING DETAIL = detail;
+ END;
+
+ IF replaced_proc IS NULL THEN
+ $def
+ RETURN;
+ END IF;
+
+ -- Old function signature exists
+
+ -- Rename old function, to avoid ambiguities and eventually drop
+ ALTER FUNCTION $name( $args ) RENAME TO ${renamed};
+
+ -- Drop the function from any extension it is part of
+ -- so dump/reloads still work
+ FOR rec IN
+ SELECT e.extname
+ FROM
+ pg_extension e,
+ pg_depend d
+ WHERE
+ d.refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND
+ d.refobjid = e.oid AND
+ d.classid = 'pg_proc'::regclass AND
+ d.objid = replaced_proc::oid
+ LOOP
+ RAISE DEBUG 'Unpackaging ${renamed} from extension %', rec.extname;
+ sql := format('ALTER EXTENSION %I DROP FUNCTION ${renamed}(${args})', rec.extname);
+ EXECUTE sql;
+ END LOOP;
+
+
+END;
+\$postgis_proc_upgrade\$;
+EOF
+ }
+ print $def;
}
if (/^create type (\w+)/i)
@@ -509,6 +596,91 @@ EOF
$comment = '';
}
+# If any deprecated function still exist, drop it now
+my $deprecated_names = '{' . ( join ',', @renamed_deprecated_functions) . '}';
+print <<"EOF";
+-- Drop deprecated functions if possible
+DO LANGUAGE 'plpgsql'
+\$postgis_proc_upgrade\$
+DECLARE
+ deprecated_functions regprocedure[];
+ rec RECORD;
+ sql TEXT;
+ detail TEXT;
+ hint TEXT;
+BEGIN
+ -- Fetch a list of deprecated functions
+
+ SELECT array_agg(oid::regprocedure)
+ FROM pg_catalog.pg_proc
+ WHERE proname = ANY ('${deprecated_names}'::name[])
+ INTO deprecated_functions;
+
+ -- Rewrite views using deprecated functions
+ -- to improve the odds of being able to drop them
+
+ FOR rec IN
+ SELECT n.nspname AS schemaname,
+ c.relname AS viewname,
+ pg_get_userbyid(c.relowner) AS viewowner,
+ pg_get_viewdef(c.oid) AS definition,
+ CASE
+ WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'WITH CASCADED CHECK OPTION'
+ WHEN 'check_option=local' = ANY (c.reloptions) THEN 'WITH LOCAL CHECK OPTION'
+ ELSE ''
+ END::text AS check_option
+ FROM pg_class c
+ LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
+ WHERE c.relkind = 'v'
+ AND pg_get_viewdef(c.oid) ~ 'deprecated_by_postgis'
+ LOOP
+ sql := format('CREATE OR REPLACE VIEW %I.%I AS %s %s',
+ rec.schemaname,
+ rec.viewname,
+ regexp_replace(rec.definition, '_deprecated_by_postgis_[^(]*', '', 'g'),
+ rec.check_option
+ );
+ RAISE NOTICE 'Updating view % to not use deprecated signatures', rec.viewname;
+ BEGIN
+ EXECUTE sql;
+ EXCEPTION
+ WHEN OTHERS THEN
+ GET STACKED DIAGNOSTICS detail := PG_EXCEPTION_DETAIL;
+ RAISE WARNING 'Could not rewrite view % using deprecated functions', rec.viewname
+ USING DETAIL = format('%s: %s', SQLERRM, detail);
+ END;
+ END LOOP;
+
+ -- Try to drop all deprecated functions, raising a warning
+ -- for each one which cannot be drop
+
+ FOR rec IN SELECT unnest(deprecated_functions) as proc
+ LOOP --{
+
+ sql := format('DROP FUNCTION %s', rec.proc);
+ --RAISE DEBUG 'SQL: %', sql;
+ BEGIN
+ EXECUTE sql;
+ EXCEPTION
+ WHEN OTHERS THEN
+ hint = 'Resolve the issue';
+ GET STACKED DIAGNOSTICS detail := PG_EXCEPTION_DETAIL;
+ IF detail LIKE '%view % depends%' THEN
+ hint = format(
+ 'Replace the view changing all occurrences of %s in its definition with %s',
+ rec.proc,
+ regexp_replace(rec.proc::text, '_deprecated_by_postgis[^(]*', '')
+ );
+ END IF;
+ hint = hint || ' and upgrade again';
+ RAISE WARNING 'Deprecated function % left behind: %', rec.proc, SQLERRM
+ USING DETAIL = detail, HINT = hint;
+ END;
+ END LOOP; --}
+END
+\$postgis_proc_upgrade\$;
+EOF
+
close(INPUT);
print "DROP FUNCTION _postgis_upgrade_info();\n";
-----------------------------------------------------------------------
Summary of changes:
NEWS | 7 ++
postgis/postgis.sql.in | 21 +++-
postgis/postgis_before_upgrade.sql | 7 --
regress/hooks/hook-after-upgrade.sql | 5 +-
regress/hooks/hook-before-upgrade.sql | 25 ++++-
utils/postgis_proc_upgrade.pl | 178 +++++++++++++++++++++++++++++++++-
6 files changed, 228 insertions(+), 15 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list