From: | Vicky Vergara <vicky_vergara(at)hotmail(dot)com> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Instead of DROP function use UPDATE pg_proc in an upgrade extension script |
Date: | 2017-04-04 04:17:55 |
Message-ID: | MWHPR11MB178988BBE11E52BF9FB0E9788A0B0@MWHPR11MB1789.namprd11.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2017-04-04 04:21:45 | Re: Instead of DROP function use UPDATE pg_proc in an upgrade extension script |
Previous Message | Amit Langote | 2017-04-04 03:57:31 | Re: wait event documentation |