Re: UPDATE pg_catalog.pg_proc.prosrc OK?

I'm working on a tool to simplify updating the source code of database
functions.
To do a "revert" my plan is to store the values of pg_proc.* before
updating, and then to restore pg_proc for the given oid if a revert is
necessary.
This raises the question,

Is it "safe" to do,
UPDATE pg_proc SET <column> = <new source code> WHERE oid = <function's
oid>;
instead of using the "CREATE OR REPLACE FUNCTION" command?

(Using pg_dump -s + pg_restore isn't feasible since it operates on the
entire schema, and not just a subset of functions, important in my case.)

Thanks for any advise.

--
Best regards,

Joel Jacobson
Glue Finance

n Mon, Dec 27, 2010 at 1:23 PM, Joel Jacobson <joel(at)gluefinance(dot)com> wrote:
> I'm working on a tool to simplify updating the source code of database
> functions.
> To do a "revert" my plan is to store the values of pg_proc.* before
> updating, and then to restore pg_proc for the given oid if a revert is
> necessary.
> This raises the question,
> Is it "safe" to do,
> UPDATE pg_proc SET <column> = <new source code> WHERE oid = <function's
> oid>;
> instead of using the "CREATE OR REPLACE FUNCTION" command?

I'm not immediately sure whether it's safe, but it seems like playing
with fire, and I don't see any particular advantage to doing it this
way over using CREATE OR REPLACE FUNCTION.

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

2010/12/28 Robert Haas <robertmhaas(at)gmail(dot)com>

> I'm not immediately sure whether it's safe, but it seems like playing
> with fire, and I don't see any particular advantage to doing it this
> way over using CREATE OR REPLACE FUNCTION.
>

While I understand some of the SQL commands affecting pg_catalog have side
effects, such as CREATE DATABASE, others seems to lack side effects.

To use CREATE OR REPLACE FUNCTION, I would have to assemble SQL from the
data in pg_proc.* for each function, which is quite a complex task (e.g.,
pg_dump does this, and it's far from trivial, due to differences between
different versions etc).

I understand it's not a good idea to read/write pg_proc between different
databases, but this is not my case.
My plan:
1. Take snapshot of pg_catalog.pg_proc.*
2. Update existing/install new source code of functions
3. Monitor how the live system behaves (might take 30 minutes or something
like that)
4. If problems occurr, revent to the old state by removing the new pg_proc
entries and restoring the modified existing ones.

Problems are not expected since the new code has been tested locally in a
database with identical schema, but I've learned you can never be one
hundred percent sure everything always works.

Until now, I've been creating a "revent .sql-file" manually, which drops the
new functions and restores the replaced functions with their old source
code.
This is quite time consuming and of course prone to human errors.

Alternative approach: It would be good if pg_dump could split a plaintext
schema dump into separate files. That would allow you to only restore the
functions, which would solve part of my problem, but would still cause
problems for functions where you alter the arguments, in which case the
existing function with the same name needs to be dropped first, before
creating the new function with different arguments. For such scenarios, I
would need to drop the new functions first, before restoring the old
functions from the dump.

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

--
Best regards,

Joel Jacobson
Glue Finance

E: jj(at)gluefinance(dot)com
T: +46 70 360 38 01

Postal address:
Glue Finance AB
Box 549
114 11 Stockholm
Sweden

Visiting address:
Glue Finance AB
Birger Jarlsgatan 14
114 34 Stockholm
Sweden

On 28.12.2010 15:19, Joel Jacobson wrote:
> 2010/12/28 Robert Haas<robertmhaas(at)gmail(dot)com>
>
>> I'm not immediately sure whether it's safe, but it seems like playing
>> with fire, and I don't see any particular advantage to doing it this
>> way over using CREATE OR REPLACE FUNCTION.
>
> While I understand some of the SQL commands affecting pg_catalog have side
> effects, such as CREATE DATABASE, others seems to lack side effects.
>
> To use CREATE OR REPLACE FUNCTION, I would have to assemble SQL from the
> data in pg_proc.* for each function, which is quite a complex task (e.g.,
> pg_dump does this, and it's far from trivial, due to differences between
> different versions etc).

Since 8.4, there is a function called pg_get_functiondef() which does
all the hard work. Or you could use pg_dump.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Tue, Dec 28, 2010 at 8:19 AM, Joel Jacobson <joel(at)gluefinance(dot)com> wrote:
> My plan:
> 1. Take snapshot of pg_catalog.pg_proc.*
> 2. Update existing/install new source code of functions
> 3. Monitor how the live system behaves (might take 30 minutes or something
> like that)
> 4. If problems occurr, revent to the old state by removing the new pg_proc
> entries and restoring the modified existing ones.
> Problems are not expected since the new code has been tested locally in a
> database with identical schema, but I've learned you can never be one
> hundred percent sure everything always works.
> Until now, I've been creating a "revent .sql-file" manually, which drops the
> new functions and restores the replaced functions with their old source
> code.

I think there's not much getting around the fact that you will have to
grovel through pg_proc to get information about the current
definitions. All I'm saying is, once you've done that, generate
CREATE/DROP FUNCTION commands rather than UPDATE statements. That
way, if there ARE relevant side effects of CREATE OR REPLACE FUNCTION,
you'll get them.

IOW, reading pg_proc is fine. Writing it is probably better avoided
(and not that hard to avoid).

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