From 6082ff04f375424b94d1081f2023a47381878599 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Ra=C3=BAl=20Mar=C3=ADn=20Rodr=C3=ADguez?= Date: Wed, 12 Jun 2019 09:49:20 +0000 Subject: [PATCH] Fix upgrade issues related to renamed function parameters References #4334 Closes https://github.com/postgis/postgis/pull/415 git-svn-id: http://svn.osgeo.org/postgis/trunk@17507 b70326c6-7e19-0410-871a-916f4a2858ee --- NEWS | 3 + postgis/postgis_drop_before.sql | 214 ++++++++++++++++++++++++++------ 2 files changed, 182 insertions(+), 35 deletions(-) diff --git a/NEWS b/NEWS index 532bcecc6..f12f2cf21 100644 --- a/NEWS +++ b/NEWS @@ -19,6 +19,7 @@ Additional features enabled if you are running Proj6+ and PostgreSQL 12 Praliaskouski) - #4414, Include version number in address_standardizer lib (Raúl Marín) - #4352, Use CREATE OR REPLACE AGGREGATE for PG12+ (Raúl Marín) + - #4334, Fix upgrade issues related to renamed parameters (Raúl Marín) PostGIS 3.0.0alpha1 2019/05/26 @@ -74,6 +75,7 @@ PostGIS 3.0.0 - #4342, Removed "versioned" variants of ST_AsGeoJSON and ST_AsKML (Paul Ramsey) - #4356, ST_Accum removed. Use array_agg instead. (Darafei Praliaskouski) - #4414, Include version number in address_standardizer lib (Raúl Marín) + - #4334, Fix upgrade issues related to renamed function parameters (Raúl Marín) * New Features * - #2902, postgis_geos_noop (Sandro Santilli) @@ -161,6 +163,7 @@ PostGIS 3.0.0 - #4422, Modernize Python 2 code to get ready for Python 3 (Christian Clauss) - #4383, Fix undefined behaviour in implicit conversions (Raúl Marín) - #4352, Use CREATE OR REPLACE AGGREGATE for PG12+ (Raúl Marín) + - #4334, Fix upgrade issues related to renamed function parameters (Raúl Marín) PostGIS 2.5.0 diff --git a/postgis/postgis_drop_before.sql b/postgis/postgis_drop_before.sql index 18905093c..7deb9a50a 100644 --- a/postgis/postgis_drop_before.sql +++ b/postgis/postgis_drop_before.sql @@ -19,12 +19,77 @@ -- -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -DROP FUNCTION IF EXISTS AddGeometryColumn(varchar,varchar,varchar,varchar,integer,varchar,integer,boolean); -DROP FUNCTION IF EXISTS ST_MakeEnvelope(float8, float8, float8, float8); ---changed name of prec arg to be consistent with ST_AsGML/KML -DROP FUNCTION IF EXISTS ST_AsX3D(geometry, integer, integer); ---changed name of arg: http://trac.osgeo.org/postgis/ticket/1606 -DROP FUNCTION IF EXISTS UpdateGeometrySRID(varchar,varchar,varchar,varchar,integer); + +-- Helper function to drop functions when they match the full signature +-- Requires schema, name and __identity_arguments__ as extracted from pg_catalog +CREATE OR REPLACE FUNCTION _postgis_drop_function_if_needed( + function_schema text, + function_name text, + function_arguments text) RETURNS void AS $$ +DECLARE + frec RECORD; + sql_drop text; +BEGIN + FOR frec IN + SELECT p.oid as oid, + n.nspname as schema, + p.proname as name, + pg_catalog.pg_get_function_arguments(p.oid) as arguments, + pg_catalog.pg_get_function_identity_arguments(p.oid) as identity_arguments + FROM pg_catalog.pg_proc p + LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace + WHERE + LOWER(n.nspname) = LOWER(function_schema) AND + LOWER(p.proname) = LOWER(function_name) AND + LOWER(pg_catalog.pg_get_function_arguments(p.oid)) ~ LOWER(function_arguments) AND + pg_catalog.pg_function_is_visible(p.oid) + ORDER BY 1, 2, 4 + LOOP + sql_drop := 'DROP FUNCTION ' || quote_ident(frec.schema) || '.' || quote_ident(frec.name) || ' ( ' || frec.identity_arguments || ' ) '; + RAISE DEBUG 'Name (%): %', frec.oid, frec.name; + RAISE DEBUG 'Arguments: %', frec.arguments; + RAISE DEBUG 'Identity arguments: %', frec.identity_arguments; + RAISE DEBUG 'SQL query: %', sql_drop; + BEGIN + EXECUTE sql_drop; + EXCEPTION + WHEN OTHERS THEN + RAISE EXCEPTION 'Could not drop function %.%. You might need to drop dependant objects. Postgres error: %', function_schema, function_name, SQLERRM; + END; + END LOOP; +END; +$$ LANGUAGE plpgsql; + + +-- FUNCTION AddGeometryColumn signature dropped +-- (catalog_name character varying, schema_name character varying, table_name character varying, column_name character varying, new_srid integer, new_type character varying, new_dim integer, use_typmod boolean) +SELECT _postgis_drop_function_if_needed + ( + '@extschema@', + 'AddGeometryColumn', + 'catalog_name character varying, schema_name character varying, table_name character varying, column_name character varying, new_srid integer, new_type character varying, new_dim integer, use_typmod boolean' + ); + +-- FUNCTION ST_AsX3D was changed to add versioning for 2.0 +-- (geom geometry, prec integer, options integer) +SELECT _postgis_drop_function_if_needed + ( + '@extschema@', + 'ST_AsX3D', + 'geom geometry, prec integer, options integer' + ); + +-- FUNCTION UpdateGeometrySRID changed the name of the args (http://trac.osgeo.org/postgis/ticket/1606) for 2.0 +-- It changed the paramenter `new_srid` to `new_srid_in` +-- (catalogn_name character varying, schema_name character varying, table_name character varying, column_name character varying, new_srid integer) +-- Dropping it conditionally since the same signature still exists. +SELECT _postgis_drop_function_if_needed + ( + '@extschema@', + 'UpdateGeometrySRID', + 'catalogn_name character varying, schema_name character varying, table_name character varying, column_name character varying, new_srid integer' + ); + --deprecated and removed in 2.1 -- Hack to fix 2.0 naming @@ -59,32 +124,111 @@ BEGIN END; $$ ; --- Going from multiple functions to default args --- Need to drop old multiple variants to not get in trouble. -DROP FUNCTION IF EXISTS ST_AsLatLonText(geometry); -DROP FUNCTION IF EXISTS ST_AsLatLonText(geometry, text); -DROP FUNCTION IF EXISTS ST_AsTWKB(geometry,int4); -DROP FUNCTION IF EXISTS ST_AsTWKB(geometry,int4,int8); -DROP FUNCTION IF EXISTS ST_AsTWKB(geometry,int4,int8,boolean); - --- Going from un-named to named arguments -DROP FUNCTION IF EXISTS _st_linecrossingdirection(geometry,geometry); -DROP FUNCTION IF EXISTS ST_LineCrossingDirection(geometry, geometry); -DROP FUNCTION IF EXISTS _st_orderingequals(geometry,geometry); -DROP FUNCTION IF EXISTS st_orderingequals(geometry,geometry); -DROP FUNCTION IF EXISTS st_askml(geometry, integer); -- changed to use default args in PostGIS 3.0 (r17357) -DROP FUNCTION IF EXISTS st_askml(geography, integer); -- changed to use default args in PostGIS 3.0 (r17357) -DROP FUNCTION IF EXISTS st_buffer(geometry, double precision); - --- Old signatures for protobuf related functions improved in 2.4.0 RC/final -DROP AGGREGATE IF EXISTS ST_AsMVT(text, int4, text, anyelement); -DROP FUNCTION IF EXISTS ST_AsMVTGeom(geom geometry, bounds box2d, extent int4, buffer int4, clip_geom bool); -DROP AGGREGATE IF EXISTS ST_AsGeobuf(text, anyelement); -DROP FUNCTION IF EXISTS pgis_asgeobuf_transfn(internal, text, anyelement); -DROP FUNCTION IF EXISTS pgis_asmvt_transfn(internal, text, int4, text, anyelement); --- Going from multiple functions to default args --- Need to drop old multiple variants to not get in trouble. -DROP FUNCTION IF EXISTS ST_CurveToLine(geometry, integer); -DROP FUNCTION IF EXISTS ST_CurveToLine(geometry); - -DROP VIEW IF EXISTS geometry_columns; -- removed cast 2.2.0 so need to recreate + +-- FUNCTION ST_AsLatLonText went from multiple signatures to a single one with defaults for 2.2.0 +DROP FUNCTION IF EXISTS ST_AsLatLonText(geometry); -- Does not conflict +SELECT _postgis_drop_function_if_needed + ( + '@extschema@', + 'ST_AsLatLonText', + 'geometry, text' + ); + + +-- FUNCTION _st_linecrossingdirection changed argument names in 3.0 +-- Was (geom1 geometry, geom2 geometry) and now (line1 geometry, line2 geometry) +SELECT _postgis_drop_function_if_needed + ( + '@extschema@', + '_st_linecrossingdirection', + 'geom1 geometry, geom2 geometry' + ); + + +-- FUNCTION ST_LineCrossingDirection changed argument names in 3.0 +-- Was (geom1 geometry, geom2 geometry) and now (line1 geometry, line2 geometry) +SELECT _postgis_drop_function_if_needed + ( + '@extschema@', + 'ST_LineCrossingDirection', + 'geom1 geometry, geom2 geometry' + ); + + +-- FUNCTION _st_orderingequals changed argument names in 3.0 +-- Was (GeometryA geometry, GeometryB geometry) and now (geom1 geometry, geom2 geometry) +SELECT _postgis_drop_function_if_needed + ( + '@extschema@', + '_st_orderingequals', + 'GeometryA geometry, GeometryB geometry' + ); + +-- FUNCTION st_orderingequals changed argument names in 3.0 +-- Was (GeometryA geometry, GeometryB geometry) and now (geom1 geometry, geom2 geometry) +SELECT _postgis_drop_function_if_needed + ( + '@extschema@', + 'st_orderingequals', + 'GeometryA geometry, GeometryB geometry' + ); + + +-- FUNCTION st_askml changed to add defaults in 3.0 / r17357 +-- These signatures were superseeded +DROP FUNCTION IF EXISTS st_askml(geometry, integer); -- Does not conflict +DROP FUNCTION IF EXISTS st_askml(geography, integer); -- Does not conflict + + +-- FUNCTION st_buffer changed to add defaults in 3.0 +-- This signature was superseeded +DROP FUNCTION IF EXISTS st_buffer(geometry, double precision); -- Does not conflict + + +-- FUNCTION ST_CurveToLine changed to add defaults in 2.5 +-- These signatures were superseeded +DROP FUNCTION IF EXISTS ST_CurveToLine(geometry, integer); -- Does not conflict +DROP FUNCTION IF EXISTS ST_CurveToLine(geometry); -- Does not conflict + +-- 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 +-- changed to type `name` in 2.2 +DO language 'plpgsql' $$ +BEGIN + IF EXISTS + ( + WITH oids AS + ( + SELECT c.oid as oid, + n.nspname, + c.relname + FROM pg_catalog.pg_class c + LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace + WHERE c.relname = 'geometry_columns' AND + n.nspname = 'public' + AND pg_catalog.pg_table_is_visible(c.oid) + ORDER BY 2, 3 + + ), + name_attribute AS + ( + SELECT a.attname as attname, + pg_catalog.format_type(a.atttypid, a.atttypmod) as format_type + FROM pg_catalog.pg_attribute a, oids + WHERE a.attrelid = oids.oid AND a.attnum > 0 AND NOT a.attisdropped + ORDER BY a.attnum + ) + SELECT attname, format_type + FROM name_attribute + WHERE attname = 'f_table_schema' AND format_type = 'character varying(256)' + ) + THEN + DROP VIEW geometry_columns; + END IF; +END; +$$; + + +-- DROP auxiliar function (created above) +DROP FUNCTION _postgis_drop_function_if_needed(text, text, text); -- 2.50.0