From: "Soma Interesting" <dfunct@telus.net>
> When I drop a function, then add it again (for example making changes to
> the function code) it doesn't appear to break anything even though there
> are tables with triggers that reference that function. At least not
> immediately, but eventually If I do it enough times I will get this error:
>
> PostgreSQL query failed: ERROR: fmgr_info: function 128137: cache lookup
> failed
Afraid so - AFAIK triggers and other functions refer directly to the
function OID.
> Is there an explanation for why it seems intermittent? More importantly,
is
> there a way to resolve this without actually redefining the trigger
> referencing the function by updating a table value in one of the system
tables?
If foo_a() is called by foo_b() and foo_b() hasn't been used yet, then the
function hasn't been "compiled" so it will use whatever foo_a() is defined
at the moment of its first call.
Redefining the trigger references is an interesting idea - don't know enough
to say how practical it is.
I've taken to keeping groups of function/trigger definitions in text-files
as a sort of library - I edit the text file and reimport the whole lot when
I make changes. I try to think of defined functions as equivalent to
object-code.
Actually, if you're situation is complex enough you could separate
functions/triggers/table definitions into separate files and use make to
drop/reimport dependent code as required.
- Richard Huxton