Обсуждение: Instead of DROP function use UPDATE pg_proc in an upgrade extensionscript

Поиск
Список
Период
Сортировка

Instead of DROP function use UPDATE pg_proc in an upgrade extensionscript

От
Vicky Vergara
Дата:


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.

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







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

От
Pavel Stehule
Дата:


2017-04-04 6:17 GMT+02:00 Vicky Vergara <vicky_vergara@hotmail.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








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

От
Andrew Gierth
Дата:
>>>>> "Vicky" == Vicky Vergara <vicky_vergara@hotmail.com> writes:
Vicky> UPDATE pg_proc SET [...]
Vicky> So, I want to know how "safe" can you consider the secondVicky> method, and what kind of other objects do I need
totest besidesVicky> views.
 

Speaking from personal experience (I did this in the upgrade script for
ip4r, in a much simpler case than yours, and broke it badly), it's not
at all safe.

-- 
Andrew (irc:RhodiumToad)



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

От
Vicky Vergara
Дата:

Thanks,

you answered so fast that I know I am stepping into dangerous grounds.

But I would like to know more about your experience.

Any links that you can give me to read about the code and/or issues regarding the ip4r experience?


Vicky





De: Andrew Gierth <andrew@tao11.riddles.org.uk>
Enviado: lunes, 3 de abril de 2017 11:28 p. m.
Para: Vicky Vergara
Cc: pgsql-hackers@postgresql.org
Asunto: Re: [HACKERS] Instead of DROP function use UPDATE pg_proc in an upgrade extension script
 
>>>>> "Vicky" == Vicky Vergara <vicky_vergara@hotmail.com> writes:

 Vicky> UPDATE pg_proc SET [...]

 Vicky> So, I want to know how "safe" can you consider the second
 Vicky> method, and what kind of other objects do I need to test besides
 Vicky> views.

Speaking from personal experience (I did this in the upgrade script for
ip4r, in a much simpler case than yours, and broke it badly), it's not
at all safe.

--
Andrew (irc:RhodiumToad)