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

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

> Thanks,
>
>
> > It is not safe due views - that are saved in post analyze form.
>
>
> What is post analyze form? any link that you can give me to read about it?
>

The Query pipe line is: parsing, analyze, optimalization, execution

when you change a API, then the analyze stage should be processed again -
but views are stored as post analyzed serialized data. You cannot do this
process again without source code.

Regards

Pavel

>
> Thanks
>
> ------------------------------
> *De:* Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> *Enviado:* lunes, 3 de abril de 2017 11:21 p. m.
> *Para:* Vicky Vergara
> *Cc:* pgsql-hackers(at)postgresql(dot)org
> *Asunto:* Re: [HACKERS] 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,big
>> int,bigint,boolean);
>>
>> DROP FUNCTION IF EXISTS pgr_edgedisjointpaths(text,big
>> int,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,7
>> 01,701}',
>>
>> proargmodes = '{i,i,i,i,o,o,o,o,o,o,o}',
>>
>> proargnames = '{"","","","directed","seq","p
>> ath_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
>>
>>
>>
>>
>>
>>
>>
>>
>