> 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?


De: Pavel Stehule <>
Enviado: lunes, 3 de abril de 2017 11:21 p. m.
Para: Vicky Vergara
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 


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 = 
-[ 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


 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 = 

                          proargmodes = '{i,i,i,i,o,o,o,o,o,o,o}',

                           proargnames = 

 WHERE proallargtypes = '{25,20,20,16,23,23,20,20}'   AND proname = 

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.



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


Vicky Vergara

Reply via email to