Re: UPDATE pg_catalog.pg_proc.prosrc OK?

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