Re: removing functions - Mailing list pgsql-general

From Richard Huxton
Subject Re: removing functions
Date
Msg-id 006c01c0bda9$9c4ba620$1001a8c0@archonet.com
Whole thread Raw
In response to removing functions  (Soma Interesting <dfunct@telus.net>)
List pgsql-general
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


pgsql-general by date:

Previous
From: "Poul L. Christiansen"
Date:
Subject: Re: Relation XX does not exist
Next
From: Peter T Mount
Date:
Subject: Re: JBuilder4 JDBC Explorer