Postgres Pro
Downloads
Home   >   mailing lists

Thread: UPDATE pg_catalog.pg_proc.prosrc OK?

UPDATE pg_catalog.pg_proc.prosrc OK?

From
Joel Jacobson
Date:
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

Re: UPDATE pg_catalog.pg_proc.prosrc OK?

From
Robert Haas
Date:
n Mon, Dec 27, 2010 at 1:23 PM, Joel Jacobson <joel@gluefinance.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

Re: UPDATE pg_catalog.pg_proc.prosrc OK?

From
Joel Jacobson
Date:
2010/12/28 Robert Haas <robertmhaas@gmail.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@gluefinance.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

Re: UPDATE pg_catalog.pg_proc.prosrc OK?

From
Heikki Linnakangas
Date:
On 28.12.2010 15:19, Joel Jacobson wrote:
> 2010/12/28 Robert Haas<robertmhaas@gmail.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

Re: UPDATE pg_catalog.pg_proc.prosrc OK?

From
Robert Haas
Date:
On Tue, Dec 28, 2010 at 8:19 AM, Joel Jacobson <joel@gluefinance.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