From beba6d2c75a99fa924dc2ce2e2e1d1fffed138e4 Mon Sep 17 00:00:00 2001 From: Sandro Santilli Date: Sat, 22 Feb 2014 15:31:22 +0000 Subject: [PATCH] Soft upgrade: avoid drop/recreate of aggregates that hadn't changed Generate raster upgrade script using postgis_proc_upgrade Add 2.2.0 availability for new raster aggregates See #2560 git-svn-id: http://svn.osgeo.org/postgis/trunk@12243 b70326c6-7e19-0410-871a-916f4a2858ee --- raster/rt_pg/Makefile.in | 9 +- raster/rt_pg/rtpostgis.sql.in | 29 +++++-- .../sql/topoelement/topoelement_agg.sql.in | 1 + utils/postgis_proc_upgrade.pl | 84 ++++++++++++++++--- 4 files changed, 97 insertions(+), 26 deletions(-) diff --git a/raster/rt_pg/Makefile.in b/raster/rt_pg/Makefile.in index 46eae94b6..f542213f7 100644 --- a/raster/rt_pg/Makefile.in +++ b/raster/rt_pg/Makefile.in @@ -129,15 +129,16 @@ $(OBJS): ../../liblwgeom/.libs/liblwgeom.a ../../libpgcommon/libpgcommon.a ../.. $(SQL_OBJS): ../../postgis/sqldefines.h ../../postgis_svn_revision.h #remove all create object types since these can't be done cleanly in an upgrade -rtpostgis_upgrade.sql: rtpostgis.sql - $(PERL) -0777 -ne 's/^(CREATE|ALTER) (CAST|OPERATOR|TYPE|TABLE|SCHEMA|DOMAIN|TRIGGER).*?;//msg;print;' $< > $@ +rtpostgis_upgrade.sql: rtpostgis.sql ../../utils/postgis_proc_upgrade.pl + $(PERL) ../../utils/postgis_proc_upgrade.pl $< 2.0 > $@ + #$(PERL) -0777 -ne 's/^(CREATE|ALTER) (CAST|OPERATOR|TYPE|TABLE|SCHEMA|DOMAIN|TRIGGER).*?;//msg;print;' $< > $@ rtpostgis_upgrade_$(PREV_big)_$(CURV_big).sql: rtpostgis_upgrade_cleanup.sql rtpostgis_drop.sql rtpostgis_upgrade.sql cat $^ > $@ # same as $(PREV_big)_$(CURV_big) until next .0 is released -rtpostgis_upgrade_$(CURV_big)_minor.sql: rtpostgis_upgrade_cleanup.sql rtpostgis_drop.sql rtpostgis_upgrade.sql - cat $^ > $@ +rtpostgis_upgrade_$(CURV_big)_minor.sql: rtpostgis_upgrade_$(PREV_big)_$(CURV_big).sql + cp $< $@ uninstall_rtpostgis.sql: rtpostgis.sql ../../utils/create_undef.pl $(PERL) ../../utils/create_undef.pl $< $(POSTGIS_PGSQL_VERSION) > $@ diff --git a/raster/rt_pg/rtpostgis.sql.in b/raster/rt_pg/rtpostgis.sql.in index b4e0b4e32..1a9d3b7ce 100644 --- a/raster/rt_pg/rtpostgis.sql.in +++ b/raster/rt_pg/rtpostgis.sql.in @@ -37,6 +37,8 @@ SET client_min_messages TO warning; +-- INSTALL VERSION: POSTGIS_LIB_VERSION + BEGIN; ------------------------------------------------------------------------------ @@ -500,6 +502,7 @@ CREATE OR REPLACE FUNCTION _st_summarystats_transfn( AS 'MODULE_PATHNAME', 'RASTER_summaryStats_transfn' LANGUAGE 'c' IMMUTABLE; +-- Availability: 2.2.0 CREATE AGGREGATE st_summarystatsagg(raster, integer, boolean, double precision) ( SFUNC = _st_summarystats_transfn, STYPE = internal, @@ -514,6 +517,7 @@ CREATE OR REPLACE FUNCTION _st_summarystats_transfn( AS 'MODULE_PATHNAME', 'RASTER_summaryStats_transfn' LANGUAGE 'c' IMMUTABLE; +-- Availability: 2.2.0 CREATE AGGREGATE st_summarystatsagg(raster, boolean, double precision) ( SFUNC = _st_summarystats_transfn, STYPE = internal, @@ -528,6 +532,7 @@ CREATE OR REPLACE FUNCTION _st_summarystats_transfn( AS 'MODULE_PATHNAME', 'RASTER_summaryStats_transfn' LANGUAGE 'c' IMMUTABLE; +-- Availability: 2.2.0 CREATE AGGREGATE st_summarystatsagg(raster, int, boolean) ( SFUNC = _st_summarystats_transfn, STYPE = internal, @@ -769,11 +774,8 @@ CREATE OR REPLACE FUNCTION _st_countagg_transfn( END; $$ LANGUAGE 'plpgsql' IMMUTABLE; -CREATE AGGREGATE st_countagg( - raster, - integer, boolean, - double precision -) ( +-- Availability: 2.2.0 +CREATE AGGREGATE st_countagg(raster, integer, boolean, double precision) ( SFUNC = _st_countagg_transfn, STYPE = agg_count, FINALFUNC = _st_countagg_finalfn @@ -799,10 +801,8 @@ CREATE OR REPLACE FUNCTION _st_countagg_transfn( END; $$ LANGUAGE 'plpgsql' IMMUTABLE; -CREATE AGGREGATE st_countagg( - raster, - integer, boolean -) ( +-- Availability: 2.2.0 +CREATE AGGREGATE st_countagg(raster, integer, boolean) ( SFUNC = _st_countagg_transfn, STYPE = agg_count, FINALFUNC = _st_countagg_finalfn @@ -828,6 +828,7 @@ CREATE OR REPLACE FUNCTION _st_countagg_transfn( END; $$ LANGUAGE 'plpgsql' IMMUTABLE; +-- Availability: 2.2.0 CREATE AGGREGATE st_countagg(raster, boolean) ( SFUNC = _st_countagg_transfn, STYPE = agg_count, @@ -5911,6 +5912,7 @@ CREATE OR REPLACE FUNCTION _st_samealignment_finalfn(agg agg_samealignment) AS $$ SELECT $1.aligned $$ LANGUAGE 'sql' IMMUTABLE STRICT; +-- Availability: 2.1.0 CREATE AGGREGATE st_samealignment(raster) ( SFUNC = _st_samealignment_transfn, STYPE = agg_samealignment, @@ -6511,6 +6513,7 @@ CREATE OR REPLACE FUNCTION _st_union_transfn(internal, raster, unionarg[]) AS 'MODULE_PATHNAME', 'RASTER_union_transfn' LANGUAGE 'c' IMMUTABLE; +-- Availability: 2.1.0 CREATE AGGREGATE st_union(raster, unionarg[]) ( SFUNC = _st_union_transfn, STYPE = internal, @@ -6522,6 +6525,8 @@ CREATE OR REPLACE FUNCTION _st_union_transfn(internal, raster, integer, text) AS 'MODULE_PATHNAME', 'RASTER_union_transfn' LANGUAGE 'c' IMMUTABLE; +-- Availability: 2.0.0 +-- Changed: 2.1.0 changed definition CREATE AGGREGATE st_union(raster, integer, text) ( SFUNC = _st_union_transfn, STYPE = internal, @@ -6533,6 +6538,8 @@ CREATE OR REPLACE FUNCTION _st_union_transfn(internal, raster, integer) AS 'MODULE_PATHNAME', 'RASTER_union_transfn' LANGUAGE 'c' IMMUTABLE; +-- Availability: 2.0.0 +-- Changed: 2.1.0 changed definition CREATE AGGREGATE st_union(raster, integer) ( SFUNC = _st_union_transfn, STYPE = internal, @@ -6544,6 +6551,8 @@ CREATE OR REPLACE FUNCTION _st_union_transfn(internal, raster) AS 'MODULE_PATHNAME', 'RASTER_union_transfn' LANGUAGE 'c' IMMUTABLE; +-- Availability: 2.0.0 +-- Changed: 2.1.0 changed definition CREATE AGGREGATE st_union(raster) ( SFUNC = _st_union_transfn, STYPE = internal, @@ -6555,6 +6564,8 @@ CREATE OR REPLACE FUNCTION _st_union_transfn(internal, raster, text) AS 'MODULE_PATHNAME', 'RASTER_union_transfn' LANGUAGE 'c' IMMUTABLE; +-- Availability: 2.0.0 +-- Changed: 2.1.0 changed definition CREATE AGGREGATE st_union(raster, text) ( SFUNC = _st_union_transfn, STYPE = internal, diff --git a/topology/sql/topoelement/topoelement_agg.sql.in b/topology/sql/topoelement/topoelement_agg.sql.in index e03af359b..597ba2286 100644 --- a/topology/sql/topoelement/topoelement_agg.sql.in +++ b/topology/sql/topoelement/topoelement_agg.sql.in @@ -49,6 +49,7 @@ LANGUAGE 'sql' IMMUTABLE; -- -- Aggregates a set of TopoElement values into a TopoElementArray -- +-- Availability: 2.0.0 DROP AGGREGATE IF EXISTS topology.TopoElementArray_agg(topology.TopoElement); CREATE AGGREGATE topology.TopoElementArray_agg( sfunc = topology.TopoElementArray_append, diff --git a/utils/postgis_proc_upgrade.pl b/utils/postgis_proc_upgrade.pl index 587a82c8a..4a49862e8 100755 --- a/utils/postgis_proc_upgrade.pl +++ b/utils/postgis_proc_upgrade.pl @@ -37,7 +37,21 @@ use warnings; # if the major numbers in version_from are less than the version_to # number. # +# TODO: move configuration outside of code +# my $objs = { + "102" => { + "aggregates" => { + "st_extent(geometry)" => 1, + "st_memcollect(geometry)" => 1, + "st_memunion(geometry)" => 1, + "st_accum(geometry)" => 1, + "st_union(geometry)" => 1, + "st_collect(geometry)" => 1, + "st_polygonize(geometry)" => 1, + "st_makeline(geometry)" => 1 + } + }, "104" => { "types" => { "box3d_extent" => 1, @@ -64,6 +78,32 @@ my $objs = { "geography" => 1, "gidx" => 1 } + }, + "200" => { + "aggregates" => { + "st_3dextent(geometry)" => 1, + "topology.topoelementarray_agg(topology.topoelement)" => 1 + } + }, + "201" => { + "aggregates" => { + "st_samealignment(raster)" => 1, + "st_union(raster,unionarg[])" => 1, + "st_union(raster,integer,text)" => 1, + "st_union(raster,integer)" => 1, + "st_union(raster)" => 1, + "st_union(raster,text)" => 1 + } + }, + "202" => { + "aggregates" => { + "st_summarystatsagg(raster,integer,boolean,doubleprecision)" => 1, + "st_summarystatsagg(raster,boolean,doubleprecision)" => 1, + "st_summarystatsagg(raster,integer)" => 1, + "st_countagg(raster,integer,boolean,doubleprecision)" => 1, + "st_countagg(raster,integer,boolean)" => 1, + "st_countagg(raster,boolean)" => 1 + } } }; @@ -75,6 +115,7 @@ die "Usage: perl postgis_proc_upgrade.pl [] if ( @ARGV < 1 || @ARGV > 3 ); my $sql_file = $ARGV[0]; +my $module = 'postgis'; my $version_to = ""; my $version_to_num = 0; my $version_from = $ARGV[1]; @@ -109,6 +150,10 @@ while() $version_to = $1; last; } + elsif (/TYPE raster/) + { + $module = 'postgis_raster'; + } } close(INPUT); @@ -142,6 +187,7 @@ print "SET search_path TO $schema;\n" if $schema; while() { s/NEWVERSION/$version_to/g; + s/MODULE/$module/g; print; } @@ -211,33 +257,45 @@ while() my $type1 = $1; my $type2 = $2; my $def = $_; + unless (/;$/) { + while() { + $def .= $_; + last if /;$/; + } + } print "DROP CAST IF EXISTS ($type1 AS $type2);\n"; print $def; } # This code handles aggregates by dropping and recreating them. - if ( /^create aggregate\s+(\S+)\s*\(/i ) + if ( /^create aggregate\s+([^(]+)\s*\(/i ) { my $aggname = $1; + #print "-- Aggname ${aggname}\n"; my $aggtype = 'unknown'; my $def = $_; + if ( /^create aggregate\s+\S+\s*\(([^)]*)\)/i ) { + $aggtype = $1; + $aggtype =~ s/\s*//g; + } while() { $def .= $_; $aggtype = $1 if ( /basetype\s*=\s*([^,]*)\s*,/i ); last if /\);/; } - if ($aggtype eq "unknown") - { - #For the new aggregate syntax where the parameters is defined like a common function - print "DROP AGGREGATE IF EXISTS $aggname;\n"; - } - else + my $aggsig = "$aggname($aggtype)"; + my $ver = $version_from_num + 1; + #print "-- Checking ${aggsig} -- From: ${version_from_num} -- To: ${version_to_num}\n"; + while( $version_from_num < $version_to_num && $ver <= $version_to_num ) { - #For the old syntax when the parameter is defined through "basetype" - print "DROP AGGREGATE IF EXISTS $aggname($aggtype);\n"; + if( $objs->{$ver}->{"aggregates"}->{$aggsig} ) + { + print "DROP AGGREGATE IF EXISTS $aggsig;\n"; + print $def; + } + $ver++; } - print $def; } # This code handles operators by creating them if we are doing a major upgrade @@ -346,17 +404,17 @@ BEGIN -- would both return actual PostGIS release number. -- BEGIN - SELECT into old_scripts postgis_lib_version(); + SELECT into old_scripts MODULE_lib_version(); EXCEPTION WHEN OTHERS THEN RAISE DEBUG ''Got %'', SQLERRM; - SELECT into old_scripts postgis_scripts_installed(); + SELECT into old_scripts MODULE_scripts_installed(); END; SELECT into new_scripts ''NEWVERSION''; SELECT into old_maj substring(old_scripts from 1 for 2); SELECT into new_maj substring(new_scripts from 1 for 2); IF old_maj != new_maj THEN - RAISE EXCEPTION ''Upgrade from version % to version % requires a dump/reload. See PostGIS manual for instructions'', old_scripts, new_scripts; + RAISE EXCEPTION ''Upgrade of MODULE from version % to version % requires a dump/reload. See PostGIS manual for instructions'', old_scripts, new_scripts; ELSE RETURN ''Scripts versions checked for upgrade: ok''; END IF; -- 2.50.1