Re: removing functions

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