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

"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)

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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)

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?

Thanks

________________________________
De: Pavel Stehule <pavel.stehule@gmail.com>
Enviado: lunes, 3 de abril de 2017 11:21 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

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

On Tue, Apr 4, 2017 at 9:07 AM, Vicky Vergara <vicky_vergara@hotmail.com> wrote:

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?

I can't comment on that, but in general I don't think there's an issue
if (1) your UPDATE statement contains no bugs and (2) the DROP
statement would have succeeded without CASCADE. The problem is when
there's stuff depending on the existing function definition - such as
views. Even then it may work if the dependencies are such that the
new definition is compatible enough for purposes of the dependent
objects, but if not then you've got trouble.

To put this another way, if it were safe for CREATE OR REPLACE to
succeed here, we would have made it succeed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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