Re: UPDATE pg_catalog.pg_proc.prosrc OK?

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