Обсуждение: Help! Error when calling a function

"Ben Schneider" <bschneider@microdynamics.cc> wrote:


> I am receiving the following error when I call a simple function:

> Warning: pg_exec() [function.pg-exec <http://www.php.net/function.pg-exec>
> ]: Query failed: ERROR: fmgr_info: function 3036754: cache lookup failed .
> in /var/www/html/cmwareben/db_pgsql.php on line 93
> Database error: Invalid SQL: SELECT note_num,
> fn_concat(rtrim(substring(note,0,40)), '...') AS note FROM feedback_notes
> ORDER BY note_num
> PostgreSQL Error: 1 (ERROR: fmgr_info: function 3036754: cache lookup
failed
)
> Session halted.

> Here is the fn_concat function that I am calling


> CREATE FUNCTION "fn_concat"(text, text) RETURNS text AS 'begin
> return $1 || $2;
> end;' LANGUAGE 'plpgsql'

>I have the same function in another DB on the same server and it is working
> fine. The only change I made to the DB was to drop the feedback_notes
table
> and recreate it with an additional column (New column = 'status'). The
> select was working before I made this change and now it doesn't. No
changes
> were made to my PHP scripts at all! I hadn't don't even call the new
column
> in my select.

> What the heck happened?


Hi, I guess that you are using a prior the 7.3.X, this error happen each
time
that a DB object ( view table function ) use another DB object and the
object used
was deleted and recreated. Using the version 7.3.X you can specify for the
view or
eather for function the RECREATE  ability ( the internal object ID does not
change ).

Upgrading to version 7.3. X ( I suggest you the 7.3.3 ) you avoid the
deletetion of an
object if other object are depending on it and also you can specify the
"REPLACE"
when you modify a view or a function.


Regards
Gaetano Mendola

Ben Schneider wrote:
> fine. The only change I made to the DB was to drop the feedback_notes table
> and recreate it with an additional column (New column = 'status'). The
> select was working before I made this change and now it doesn't.

I can't explain the exact cause in this case, because I don't see right
off how it fits, but cache lookup failure is typically caused by
dropping and recreating some object that is referenced somewhere else.
Are there views referencing that table somewhere in this? BTW, you could
have done ALTER TABLE ADD COLUMN.

In any case, this might fix it:

CREATE OR REPLACE FUNCTION "fn_concat"(text, text) RETURNS text AS
'begin return $1 || $2; end;' LANGUAGE 'plpgsql';

But I'm not sure why you'd want to use plpgsql for that; I'd use:

DROP FUNCTION "fn_concat"(text, text);
CREATE OR REPLACE FUNCTION "fn_concat"(text, text) RETURNS text AS
'SELECT $1 || $2' LANGUAGE 'sql';

HTH,

Joe