BEGIN; -- PostGIS - Spatial Types for PostgreSQL -- http://postgis.net -- -- Copyright (C) 2020 Regina Obe -- This is free software; you can redistribute and/or modify it under -- the terms of the GNU General Public Licence. See the COPYING file. -- -- -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- These are functions that need to be dropped beforehand -- where the argument names may have changed -- -- so have to be dropped before upgrade can happen -- -- argument names changed -- -- -- UPGRADE SCRIPT TO PostGIS 3.3.2 -- LOAD '$libdir/postgis_sfcgal-3'; DO $$ DECLARE old_scripts text; new_scripts text; old_ver_int int[]; new_ver_int int[]; old_maj text; new_maj text; postgis_upgrade_info RECORD; postgis_upgrade_info_func_code TEXT; BEGIN -- -- This uses postgis_lib_version() rather then -- postgis_scripts_installed() as in 1.0 because -- in the 1.0 => 1.1 transition that would result -- in an impossible upgrade: -- -- from 0.3.0 to 1.1.0 -- -- Next releases will still be ok as -- postgis_lib_version() and postgis_scripts_installed() -- would both return actual PostGIS release number. -- BEGIN SELECT into old_scripts postgis_lib_version(); EXCEPTION WHEN OTHERS THEN RAISE DEBUG 'Got %', SQLERRM; SELECT into old_scripts postgis_scripts_installed(); END; SELECT into new_scripts '3.3.2'; BEGIN new_ver_int := pg_catalog.string_to_array( pg_catalog.regexp_replace( new_scripts, '[^\d.].*', '' ), '.' )::int[]; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Cannot parse new version % into integers', new_scripts; END; BEGIN old_ver_int := pg_catalog.string_to_array( pg_catalog.regexp_replace( old_scripts, '[^\d.].*', '' ), '.' )::int[]; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Cannot parse old version % into integers', old_scripts; END; -- Guard against downgrade IF new_ver_int < old_ver_int THEN RAISE EXCEPTION 'Downgrade of postgis from version % to version % is forbidden', old_scripts, new_scripts; END IF; -- Check for hard-upgrade being required SELECT into old_maj pg_catalog.substring(old_scripts, 1, 1); SELECT into new_maj pg_catalog.substring(new_scripts, 1, 1); -- 2.x to 3.x was upgrade-compatible, see -- https://trac.osgeo.org/postgis/ticket/4170#comment:1 IF new_maj = '3' AND old_maj = '2' THEN old_maj = '3'; -- let's pretend old major = new major END IF; IF old_maj != new_maj THEN RAISE EXCEPTION 'Upgrade of postgis from version % to version % requires a dump/reload. See PostGIS manual for instructions', old_scripts, new_scripts; END IF; WITH versions AS ( SELECT '3.3.2'::text as upgraded, postgis_scripts_installed() as installed ) SELECT upgraded as scripts_upgraded, installed as scripts_installed, pg_catalog.substring(upgraded, '([0-9]+)\.')::int * 100 + pg_catalog.substring(upgraded, '[0-9]+\.([0-9]+)(\.|$)')::int as version_to_num, pg_catalog.substring(installed, '([0-9]+)\.')::int * 100 + pg_catalog.substring(installed, '[0-9]+\.([0-9]+)(\.|$)')::int as version_from_num, installed ~ 'dev|alpha|beta' as version_from_isdev FROM versions INTO postgis_upgrade_info ; postgis_upgrade_info_func_code := pg_catalog.format($func_code$ CREATE FUNCTION _postgis_upgrade_info(OUT scripts_upgraded TEXT, OUT scripts_installed TEXT, OUT version_to_num INT, OUT version_from_num INT, OUT version_from_isdev BOOLEAN) AS $postgis_upgrade_info$ BEGIN scripts_upgraded := %L :: TEXT; scripts_installed := %L :: TEXT; version_to_num := %L :: INT; version_from_num := %L :: INT; version_from_isdev := %L :: BOOLEAN; RETURN; END $postgis_upgrade_info$ LANGUAGE 'plpgsql' IMMUTABLE; $func_code$, postgis_upgrade_info.scripts_upgraded, postgis_upgrade_info.scripts_installed, postgis_upgrade_info.version_to_num, postgis_upgrade_info.version_from_num, postgis_upgrade_info.version_from_isdev); RAISE DEBUG 'Creating function %', postgis_upgrade_info_func_code; EXECUTE postgis_upgrade_info_func_code; END $$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION postgis_sfcgal_scripts_installed() RETURNS text AS $$ SELECT trim('3.3.2'::text || $rev$ 4975da8 $rev$) AS version $$ LANGUAGE 'sql' IMMUTABLE; CREATE OR REPLACE FUNCTION postgis_sfcgal_version() RETURNS text AS '$libdir/postgis_sfcgal-3' LANGUAGE 'c' IMMUTABLE; CREATE OR REPLACE FUNCTION postgis_sfcgal_full_version() RETURNS text AS 'SELECT postgis_sfcgal_version();' LANGUAGE 'sql' IMMUTABLE; CREATE OR REPLACE FUNCTION postgis_sfcgal_noop(geometry) RETURNS geometry AS '$libdir/postgis_sfcgal-3', 'postgis_sfcgal_noop' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 1; CREATE OR REPLACE FUNCTION ST_3DIntersection(geom1 geometry, geom2 geometry) RETURNS geometry AS '$libdir/postgis_sfcgal-3','sfcgal_intersection3D' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_3DDifference(geom1 geometry, geom2 geometry) RETURNS geometry AS '$libdir/postgis_sfcgal-3','sfcgal_difference3D' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_3DUnion(geom1 geometry, geom2 geometry) RETURNS geometry AS '$libdir/postgis_sfcgal-3','sfcgal_union3D' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; -- Aggregate ST_3DUnion(geometry) -- LastUpdated: 303 DO LANGUAGE 'plpgsql' $postgis_proc_upgrade$ BEGIN IF pg_catalog.current_setting('server_version_num')::integer >= 120000 THEN EXECUTE $postgis_proc_upgrade_parsed_def$ CREATE OR REPLACE AGGREGATE ST_3DUnion(geometry) ( sfunc = ST_3DUnion, stype = geometry, parallel = safe ); $postgis_proc_upgrade_parsed_def$; ELSIF 303 > version_from_num OR ( 303 = version_from_num AND version_from_isdev ) FROM _postgis_upgrade_info() THEN EXECUTE 'DROP AGGREGATE IF EXISTS ST_3DUnion(geometry)'; EXECUTE $postgis_proc_upgrade_parsed_def$ CREATE AGGREGATE ST_3DUnion(geometry) ( sfunc = ST_3DUnion, stype = geometry, parallel = safe ); $postgis_proc_upgrade_parsed_def$; END IF; END $postgis_proc_upgrade$; CREATE OR REPLACE FUNCTION ST_Tesselate(geometry) RETURNS geometry AS '$libdir/postgis_sfcgal-3','sfcgal_tesselate' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_3DArea(geometry) RETURNS FLOAT8 AS '$libdir/postgis_sfcgal-3','sfcgal_area3D' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_Extrude(geometry, float8, float8, float8) RETURNS geometry AS '$libdir/postgis_sfcgal-3','sfcgal_extrude' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_ForceLHR(geometry) RETURNS geometry AS '$libdir/postgis_sfcgal-3','sfcgal_force_lhr' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_Orientation(geometry) RETURNS INT4 AS '$libdir/postgis_sfcgal-3','sfcgal_orientation' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_MinkowskiSum(geometry, geometry) RETURNS geometry AS '$libdir/postgis_sfcgal-3','sfcgal_minkowski_sum' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_StraightSkeleton(geometry) RETURNS geometry AS '$libdir/postgis_sfcgal-3','sfcgal_straight_skeleton' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_ApproximateMedialAxis(geometry) RETURNS geometry AS '$libdir/postgis_sfcgal-3','sfcgal_approximate_medial_axis' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_IsPlanar(geometry) RETURNS boolean AS '$libdir/postgis_sfcgal-3','sfcgal_is_planar' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_Volume(geometry) RETURNS FLOAT8 AS '$libdir/postgis_sfcgal-3','sfcgal_volume' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_MakeSolid(geometry) RETURNS geometry AS '$libdir/postgis_sfcgal-3','sfcgal_make_solid' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_IsSolid(geometry) RETURNS boolean AS '$libdir/postgis_sfcgal-3','sfcgal_is_solid' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_ConstrainedDelaunayTriangles(geometry) RETURNS geometry AS '$libdir/postgis_sfcgal-3', 'ST_ConstrainedDelaunayTriangles' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_3DConvexHull(geometry) RETURNS geometry AS '$libdir/postgis_sfcgal-3', 'sfcgal_convexhull3D' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_AlphaShape(g1 geometry, alpha float8 DEFAULT 1.0, allow_holes boolean DEFAULT false) RETURNS geometry AS '$libdir/postgis_sfcgal-3', 'sfcgal_alphashape' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; CREATE OR REPLACE FUNCTION ST_OptimalAlphaShape(g1 geometry, allow_holes boolean DEFAULT false, nb_components int DEFAULT 1) RETURNS geometry AS '$libdir/postgis_sfcgal-3', 'sfcgal_optimalalphashape' LANGUAGE 'c' IMMUTABLE STRICT PARALLEL SAFE COST 100; -- Drop deprecated functions if possible DO LANGUAGE 'plpgsql' $postgis_proc_upgrade$ DECLARE deprecated_functions regprocedure[]; new_name TEXT; rewrote_as_wrapper BOOLEAN; rec RECORD; extrec RECORD; procrec 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 ('{}'::name[]) INTO deprecated_functions; RAISE DEBUG 'Handling deprecated functions: %', 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_catalog.pg_get_userbyid(c.relowner) AS viewowner, -- pg_catalog.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_catalog.pg_class c -- LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace -- WHERE c.relkind = 'v' -- AND pg_catalog.pg_get_viewdef(c.oid) ~ 'deprecated_by_postgis' -- LOOP -- sql := pg_catalog.format('CREATE OR REPLACE VIEW %I.%I AS %s %s', -- rec.schemaname, -- rec.viewname, -- pg_catalog.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 = pg_catalog.format('%s: %s', SQLERRM, detail); -- END; -- END LOOP; -- Try to drop all deprecated functions, or rewrite those -- who cannot be drop and rewrite them in SQL FOR rec IN SELECT pg_catalog.unnest(deprecated_functions) as proc LOOP --{ RAISE DEBUG 'Handling deprecated function %', rec.proc; new_name := pg_catalog.regexp_replace( rec.proc::text, '_deprecated_by_postgis[^(]*\(.*', '' ); sql := pg_catalog.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 = pg_catalog.format( 'Replace the view changing all occurrences of %s in its definition with %s', rec.proc, new_name ); END IF; hint = hint || ' and upgrade again'; RAISE WARNING 'Deprecated function % left behind: %', rec.proc, SQLERRM USING DETAIL = detail, HINT = hint; -- -- Try to rewrite the function as an SQL WRAPPER -- { SELECT pg_get_functiondef(oid) def, pronargs FROM pg_catalog.pg_proc WHERE oid = rec.proc INTO procrec; -- -- TODO: don't even try if it's an aggregate or windowing -- function (procrec.prokind) -- TODO: don't even try if it's a scripting language function -- function (procrec.prokind) -- -- Force LANGUAGE to be SQL sql := pg_catalog.regexp_replace(procrec.def, 'LANGUAGE [^ ]*', 'LANGUAGE sql'); --RAISE DEBUG 'SQL (LANGUAGE): %', sql; -- Change body to be a wrapper sql := pg_catalog.regexp_replace( sql, -- Find a stricted match here ? 'AS .*', pg_catalog.format( -- TODO: have the function raise a warning too ? 'AS $$ SELECT %s(%s) $$', new_name, ( SELECT array_to_string( array_agg('$' || x), ',' ) FROM generate_series(1, procrec.pronargs) x ) ) ); RAISE DEBUG 'SQL: %', sql; rewrote_as_wrapper := false; BEGIN EXECUTE sql; rewrote_as_wrapper := true; EXCEPTION WHEN OTHERS THEN RAISE WARNING 'Deprecated function % could not be rewritten as a wrapper: % (%)', rec.proc, SQLERRM, SQLSTATE; END; -- --} -- Drop the function from any extension it is part of -- so dump/reloads still work FOR extrec IN SELECT e.extname FROM pg_catalog.pg_extension e, pg_catalog.pg_depend d WHERE d.refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND d.refobjid = e.oid AND d.classid = 'pg_catalog.pg_proc'::pg_catalog.regclass AND d.objid = rec.proc::oid LOOP RAISE DEBUG 'Unpackaging % from extension %', rec.proc, extrec.extname; sql := pg_catalog.format('ALTER EXTENSION %I DROP FUNCTION %s', extrec.extname, rec.proc); EXECUTE sql; END LOOP; END; END LOOP; --} END $postgis_proc_upgrade$; DROP FUNCTION _postgis_upgrade_info(); -- PostGIS - Spatial Types for PostgreSQL -- http://postgis.net -- -- Copyright (C) 2020 Regina Obe -- This is free software; you can redistribute and/or modify it under -- the terms of the GNU General Public Licence. See the COPYING file. -- -- -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- These are reserved for functions that are changed to use default args -- This is installed after the new functions are installed -- We don't have any of these yet for sfcgal -- The reason we put these after install is -- you can't drop a function that is used by sql functions -- without forcing a drop on those as well which may cause issues with user functions. -- This allows us to CREATE OR REPLACE those in general sfcgal.sql.in -- without dropping them. COMMIT;