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
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
Comment