[postgis-tickets] [SCM] PostGIS branch stable-3.1 updated. 3.1.4-20-g3d947448b
git at osgeo.org
git at osgeo.org
Sun Dec 19 07:22:44 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.1 has been updated
via 3d947448b9c939b321bc22b85c719845722ecc3d (commit)
from 44deec454fabf94e93858234f12c43d98fc56942 (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 3d947448b9c939b321bc22b85c719845722ecc3d
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".
Closes #5033 in 3.1 branch (3.1.5dev)
diff --git a/NEWS b/NEWS
index 91254cd02..7ede9e599 100644
--- a/NEWS
+++ b/NEWS
@@ -7,6 +7,8 @@ PostGIS 3.1.5
- #5018, pgsql2shp basic support for WITH CTE clause (Regina Obe)
- #5026, fix DropTopology in presence of UPDATE triggers on
topology layers (Sandro Santilli)
+ - #5033, allow upgrades in presence of views using deprecated
+ functions (Sandro Santilli)
PostGIS 3.1.4
2021/09/04
diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in
index 9e49473d9..accda4fa7 100644
--- a/postgis/postgis.sql.in
+++ b/postgis/postgis.sql.in
@@ -2888,7 +2888,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
@@ -2902,7 +2905,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
@@ -2982,7 +2985,7 @@ BEGIN
EXECUTE sql;
END IF;
- END LOOP;
+ END LOOP; --}
RETURN 'Upgrade completed, run SELECT postgis_full_version(); for details';
@@ -3173,6 +3176,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
$$
@@ -3441,6 +3450,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'
@@ -3584,6 +3594,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'
@@ -3606,6 +3617,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'
@@ -3633,6 +3645,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'
@@ -3661,6 +3674,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'
diff --git a/postgis/postgis_before_upgrade.sql b/postgis/postgis_before_upgrade.sql
index dcd69aa09..fd104bc8e 100644
--- a/postgis/postgis_before_upgrade.sql
+++ b/postgis/postgis_before_upgrade.sql
@@ -217,12 +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
-
-- 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/utils/postgis_proc_upgrade.pl b/utils/postgis_proc_upgrade.pl
index 859641b71..0d804cfa9 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 TABLE _postgis_upgrade_info;\n";
-----------------------------------------------------------------------
Summary of changes:
NEWS | 2 +
postgis/postgis.sql.in | 20 ++++-
postgis/postgis_before_upgrade.sql | 6 --
utils/postgis_proc_upgrade.pl | 178 ++++++++++++++++++++++++++++++++++++-
4 files changed, 194 insertions(+), 12 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list