Re: Instead of DROP function use UPDATE pg_proc in an upgrade extension script

2017-04-04 6:17 GMT+02:00 Vicky Vergara <vicky_vergara(at)hotmail(dot)com>:

>
> Hello,
>
>
> When creating an extension upgrade sql script, because the function does
> not have the same parameter names and/or parameters type and/or the result
> types changes, there is the need to drop the function because otherwise the
> CREATE OR REPLACE of the new signature will fail.
>
>
> So for example:
>
> having the following function:
>
>
> SELECT proallargtypes, proargmodes, proargnames FROM pg_proc WHERE
> proallargtypes = '{25,20,20,16,23,23,20,20}' AND proname =
> 'pgr_edgedisjointpaths';
> -[ RECORD 1 ]--+--------------------------------------------------------
> ---------------------
> proallargtypes | {25,20,20,16,23,23,20,20}
> proargmodes | {i,i,i,i,o,o,o,o}
> proargnames | {"","","",directed,seq,path_seq,node,edge}
>
>
> When adding extra OUT parameters, because the result types (&names)
> change, the function needs a DROP:
>
> -- Row type defined by OUT parameters is different
>
> ALTER EXTENSION pgrouting DROP FUNCTION pgr_edgedisjointpaths(text,
> bigint,bigint,boolean);
>
> DROP FUNCTION IF EXISTS pgr_edgedisjointpaths(text,
> bigint,bigint,boolean);
>
>
> but doing that, objects that depend on the function. like a view, get
> dropped when using CASCADE in the ALTER extension, and functions that use
> the pgr_edgedisjointpaths internally don't get dropped.
>
>
> So, I must say that I experimented: instead of doing the drop, I made:
>
>
> UPDATE pg_proc SET
>
> proallargtypes = '{25,20,20,16,23,23,23,20,20,
> 701,701}',
>
> proargmodes = '{i,i,i,i,o,o,o,o,o,o,o}',
>
> proargnames = '{"","","","directed","seq","
> path_id","path_seq","node","edge","cost","agg_cost"}'
>
> WHERE proallargtypes = '{25,20,20,16,23,23,20,20}' AND proname =
> 'pgr_edgedisjointpaths';
>
>
> And CASCADE was not needed, and the view remained intact.
>
>
> So, I want to know how "safe" can you consider the second method, and what
> kind of other objects do I need to test besides views.
>

It is not safe due views - that are saved in post analyze form.

Regards

Pavel

> My plan, is to use the second method:
>
> - when the current names of the OUT parameters don't change, and there is
> an additional OUT parameter
>
> - when the current names of the IN parameters don't change, and there is
> an additional IN parameter with a default value
>
>
> Thanks
>
>
> Vicky Vergara
>
>
>
>
>
>
>
>