[postgis-tickets] [PostGIS] #4334: Can't upgrade with a view based on ST_AsMVTGeom

[PostGIS]

#4334: Can't upgrade with a view based on ST_AsMVTGeom
------------------------------------+---------------------------
  Reporter:  Algunenano             |      Owner:  strk
      Type:  defect                 |     Status:  new
  Priority:  high                   |  Milestone:  PostGIS 2.4.7
 Component:  build/upgrade/install  |    Version:  2.4.x
Resolution:                         |   Keywords:
------------------------------------+---------------------------

Comment (by Algunenano):

 @strk Thanks a lot for the comments.


 I think deleting and recreating views automagically in the extension is
 excessive (since then other stuff might depend on that view and the cycle
 goes on); as you say, we should try to keep around the old signature, but
 when that's impossible or I'd be fine forcing a manual user intervention
 **once**.


 I've come up with a sql function that I think could handle this.For
 example, let's say we have a function with this signature:
 {{{
 CREATE OR REPLACE FUNCTION ST_AsMVTGeom(geom geometry, bounds box2d,
 extent int4, buffer int4, clip_geom bool)
 }}}

 And we added defaults:
 {{{
 CREATE OR REPLACE FUNCTION ST_AsMVTGeom(geom geometry, bounds box2d,
 extent int4 default 4096, buffer int4 default 256, clip_geom bool default
 true)
 }}}


 Adding defaults (same for renaming a parameter) means you can't `CREATE OR
 REPLACE` as you've already mentioned, and this means that manual
 intervention is forced; but using the following SQL function we could
 require the intervention only once and not with every upgrade:

 {{{
 CREATE OR REPLACE FUNCTION postgis_cond_drop_function(
     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
                 n.nspname ~* function_schema AND
                 p.proname ~* 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;
 }}}


 Instead of calling DROP, you'd call:
 {{{
 SELECT postgis_cond_drop_function('public', 'ST_AsMVTGeom', 'geom
 geometry, bounds box2d, extent integer, buffer integer, clip_geom boolean
 default true');
 }}}

 The function would only drop ST_AsMVTGeom if the passing arguments (3rd
 argument) matches exactly the ones from the installed function.
 It has a caveat though, you need to pass the function arguments as stored
 by Postgres, not as declared in the CREATE FUNCTION (eg. integer and not
 int4), since that's what I could use to match them. Since we are doing it
 ourselves I don't think that's too big of an issue.

 What do you think about this approach? Does it makes sense to you?

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4334#comment:3>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.