Thread: Cache Lookup failed Error - while using Triggers

Hi All,

I took the following example to create trigger

TABLE

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text);




Function emp_stamp() returns OPAQUE

DECLARE
current_user text;
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname ISNULL THEN
            RAISE EXCEPTION ''empname cannot be NULL value'';
        END IF;
        IF NEW.salary ISNULL THEN
            RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
        ELSE
            current_user := NEW.empname;
        END IF
        -- Who works for us when she must pay for?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION ''% cannot have a negative salary'',
NEW.empname;
        END IF;
        -- Remember who changed the payroll when
        NEW.last_date := ''now'';
        NEW.last_user := NEW.empname;
        RETURN NEW;
    END;

Trigger

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

If I use the following INSERT Statement

INSERT INTO  emp (empname,salary, last_date, last_user) VALUES ('sree',1000,
'2002-09-11','sreedhar');

I am getting the following Error

'ERROR : fmgr_info : function 1059155 : cahce lookup failed
PostgreSQL status : PGRES_FATAL_ERROR'

Can any body tell that what is this error. How can I solve this.


With Best Regards,
Sreedhar Bhaskararaju 1,2nd Main Road, KottuGardens, Chennai - 600 085 Ph :
4475111 Email : shreedhar@lucidindia.net

shreedhar wrote:
> I am getting the following Error
>
> 'ERROR : fmgr_info : function 1059155 : cahce lookup failed
> PostgreSQL status : PGRES_FATAL_ERROR'
>
> Can any body tell that what is this error. How can I solve this.
>

If you drop and recreate the function, you need to also drop and recreate the
trigger. Did you do the former without the latter?

Joe

Hi Joe,

Thanks Alot,

I followed how u said, then if I execute the same insert statement the
following error is occuring.

ERROR : Parse error at or near "empname"

As I Check I didnot find any parse error there. This is error is occuring if
and only if 'emp' table have trigger. Is there any problem in function
'aa_emp_stamp()'

Regards,
Sreedhar
----- Original Message -----
From: "Joe Conway" <mail@joeconway.com>
To: "shreedhar" <shreedhar@lucidindia.net>
Cc: "Postgres" <pgsql-admin@postgresql.org>
Sent: Wednesday, September 11, 2002 10:38 AM
Subject: Re: [ADMIN] Cache Lookup failed Error - while using Triggers


> shreedhar wrote:
> > I am getting the following Error
> >
> > 'ERROR : fmgr_info : function 1059155 : cahce lookup failed
> > PostgreSQL status : PGRES_FATAL_ERROR'
> >
> > Can any body tell that what is this error. How can I solve this.
> >
>
> If you drop and recreate the function, you need to also drop and recreate
the
> trigger. Did you do the former without the latter?
>
> Joe

Can anyone suggest a way to access near-real-time transaction per second
benchmark information from an active production installation of Postgres
7.1?

We have a 100+ user OLTP application and are about to implement some
hardware changes; moving log file to its own spindle, raid 1, etc.  What
I'm hoping for is a method by which to look into the functioning production
system under load and have a way of saying that on average, it's currently
processing X tps.  This would provide a benchmark for comparison for after
the hardware upgrades.  It would also provide a means of gauge increased
use/load over time as more users and/or increased functionality is
deployed.  This in turn could help us plan for (and justify to management)
the need to boost platform hardware IN ADVANCE of reported performance
problems.

Basically, I'd like to be able to know how many transactions per second
were processed between 8:45 and 9:00am, between 1:30 and 1:45pm and between
4:00 and 4:15pm and track these number historically for comparison
proposes.

I've done this in other environments where data from the logging system of
transaction starts and transaction ends could be captured, accumulated and
used to come up with these numbers.  With the sophistication of the
Postgres WAL subsystem, I'm sure the information exists.  It's just a
matter of knowing the questions to ask in retrieving/accumulating the
required data.

Anyone have any ideas?

Marc Mitchell - Senior Application Architect
Enterprise Information Solutions, Inc.
Downers Grove, IL 60515
marcm@eisolution.com

shreedhar wrote:
> Hi Joe,
>
> Thanks Alot,
>
> I followed how u said, then if I execute the same insert statement the
> following error is occuring.
>
> ERROR : Parse error at or near "empname"
>
> As I Check I didnot find any parse error there. This is error is occuring if
> and only if 'emp' table have trigger. Is there any problem in function
> 'aa_emp_stamp()'

I don't know what aa_emp_stamp() looks like. The function you sent in the
other day was called emp_stamp() -- is that the same? If so, you were missing
as ";":

DECLARE
     current_user text;
BEGIN
     -- Check that empname and salary are given
     IF NEW.empname ISNULL THEN
        RAISE EXCEPTION ''empname cannot be NULL value'';
     END IF;
     IF NEW.salary ISNULL THEN
        RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
     ELSE
        current_user := NEW.empname;
     END IF
          ^^^^
          missing a ;


Joe