Reparse PL/pgSQL Function?

[John Sidney-Woollett]

Is it possible to (automatically) force the reparse of a stored PL/pgSQL
function following the deletion and recreation of an object that was
referenced within the function (for example a table or another function)?

The need to re-create (create or replace ...) the function is a pain as
you have to follow a potential chain of other objects recreating them too.

I think that it would be better if Postgres could (optionally) try
re-parsing the function if it failed to locate a referenced object's OID.
The reparse would allow it to locate the newly created OID (assuming the
object had been recreated), and then operate as before.

I know that someone is going to say that it is safer not to do this
because the error flags the DB in an inconsistant state, but...

Oracle has the concept of a validity flag for stored procs/functions. When
you delete an object, all procs/functions referencing that object are
marked invalid. When you try to execute a function marked invalid, Oracle
reparses and compiles the proc/function the next time it is executed. If
the referenced objects have been recreated, then operation proceeds
normally, and if not, an error is generated.

Could Postgres provide the same behaviour? And if so, how likely is a fix? :)

John Sidney-Woollett

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[scott.marlowe]

On Wed, 7 Jan 2004, John Sidney-Woollett wrote:

Is it possible to (automatically) force the reparse of a stored PL/pgSQL
function following the deletion and recreation of an object that was
referenced within the function (for example a table or another function)?

Would

CREATE OR REPLACE function

work?


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Rich Hall]

My take on the originmal question:
"If function Foo calls functioin Bar and you chanage Bar "sufficiently",
change the prameter list, return type. etc., what would be necessary
to recompile Foo?"

Rick

scott.marlowe wrote:

On Wed, 7 Jan 2004, John Sidney-Woollett wrote:

Is it possible to (automatically) force the reparse of a stored PL/pgSQL
function following the deletion and recreation of an object that was
referenced within the function (for example a table or another function)?

Would

CREATE OR REPLACE function

work?

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[John Sidney-Woollett]

It would, but you have to locate all the functions that referenced your
deleted and recreated table... That's the pain.

If you miss one, you find out later when your app dies with an unexpected
error thrown by postgres.

John

scott.marlowe said:

On Wed, 7 Jan 2004, John Sidney-Woollett wrote:

Is it possible to (automatically) force the reparse of a stored PL/pgSQL
function following the deletion and recreation of an object that was
referenced within the function (for example a table or another
function)?

Would

CREATE OR REPLACE function

work?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly