Thread: FAQ -- 'cache lookup failed' still a puzzle

FAQ -- 'cache lookup failed' still a puzzle

From
will trillich
Date:
i have been dropping and creating things left and right, so
something's referring to an old version of something that's been
redefined. how can i find it? [and if there's a better paradigm
to use in p7.1 i'd love to hear it. :) ]

here's what i've tried:

after looking over the info at

    http://techdocs.postgresql.org/errors.php#plpgsqlinit_fcache

i figured i'd solve my problem:

    db> update prop_ set year=1961 where id=1;
    ERROR:  fmgr_info: function 1528806: cache lookup failed

i've not done any upgrading for about a year--

    $ psql -V
    psql (PostgreSQL) 7.1
    contains readline, history, multibyte support

but i have been hammering away at my schema, dropping and
creating functions, tables, views, and rules (no triggers aside
from the behind-the-scenes ones like 'references' in table
definitions).

i've got a table _prop with a view prop_ which i use to
intercept inserts and updates via the postgres rule system.

so, taking the lead from the techdocs above, i looked for
functions used in the rule -- there are two:

    create rule prop_edit as
    on update to prop_
    do instead (
        update _prop set
            props_id      = NEW.props_id,

            -- FUNCTION CALL NUMBER ONE:
            vin_rev       = store_vin( NEW.vin ),

            year          = NEW.year,
            make          = NEW.make,
            model         = NEW.model,
            stages_id     = NEW.stages_id,
            license       = NEW.license,
            license_st    = NEW.license_st,
            exterior      = NEW.exterior,
            interior      = NEW.interior,
            usage         = NEW.usage,
            conditions_id = NEW.conditions_id,
            at            = current_timestamp,
            who           = NEW.who -- $USER->{id}
        where
            id        = OLD.id;

        -- FUNCTION CALL NUMBER TWO:
        select stage_change(OLD.stages_id,NEW.stages_id,OLD.id);
        -- percolate charges up to ACCT record

        insert into _changes (
            tbl,
            ref,
            what,
            at,
            who
        ) values (
            'prop',
            OLD.id,
            'u',
            current_timestamp,
            NEW.who
        );
    );

the first function, store_vin(varchar), calls no other
user-defined functions:

    DECLARE
        sz INTEGER;
        ix INTEGER;
        s VARCHAR;
        c CHAR;
    BEGIN
        IF $1 IS NULL THEN
            RETURN NULL;
        END IF;
        sz := CHAR_LENGTH($1);
        s := '';
        FOR ix IN 1 .. sz LOOP
            c := SUBSTR( $1, ix, 1 );
            IF NOT ( c = ' ' ) THEN
                c := UPPER( c );
                IF ( c = 'O' ) THEN -- oh
                    c := '0'; -- zero
                END IF;
                IF ( c = 'I' ) THEN -- eye
                    c := '1'; -- one
                END IF;
                s := c || s ; -- reverse it
            END IF;
        END LOOP;
        RETURN SUBSTR( s, 1, 17 );
    END;

it calls builtins char_length, substr and upper. no user-defined
functions are called.

and the same is true for the second function,
stage_change(int,int,int):

    DECLARE
        old_stg ALIAS FOR $1;
        new_stg ALIAS FOR $2;
        p_id    ALIAS FOR $3;
    BEGIN
        IF old_stg < 1 OR new_stg < 1 THEN
            RETURN 0;
        END IF;
        IF old_stg IS null AND new_stg = 1 THEN
            update _prop set ASSIGNED = current_date where id = p_id;
            return 1;
        ELSE
            IF old_stg < 2 AND new_stg = 2 THEN
                update _prop set RESOLVED = current_date where id = p_id;
                RETURN 2;
            ELSE
                if old_stg < 3 and new_stg = 3 then
                    UPDATE _prop SET DONE = current_date WHERE id = p_id;
                    RETURN 3;
                END IF;
            END IF;
        END IF;
        RETURN 0;
    END;

the rule that intercepts 'insert' works like a charm -- and the
primary difference is that the 'update' rule (above) calls
stage_change() where the create rule doesn't need to -- so i
thought that might be the culprit:

    db> select stage_change(0,1,1);
     stage_change
    --------------
                0
    (1 row)

so it's not. or at least it's got *me* convinced, anyhow.

so what's the next thing to investigate? i have been dropping and
creating things left and right, so something's referring to an
old version of something that's been redefined. how can i find
it? [and if there's a better paradigm to use in p7.1 i'd love to
hear it. :) ]

--
"We will fight them on the beaches, we will fight them on the
sons of beaches" -- Miguel Churchill, Winston's bastard Mexican
brother.
--lifted from http://www.astray.com/acmemail/stable/documentation.xml


will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Re: FAQ -- 'cache lookup failed' still a puzzle

From
Stephan Szabo
Date:
> the rule that intercepts 'insert' works like a charm -- and the
> primary difference is that the 'update' rule (above) calls
> stage_change() where the create rule doesn't need to -- so i
> thought that might be the culprit:
>
>     db> select stage_change(0,1,1);
>      stage_change
>     --------------
>                 0
>     (1 row)
>
> so it's not. or at least it's got *me* convinced, anyhow.

Well, it's not going to be referenced by name any more, it'll be
referenced by oid which is changed when you drop and create
it again.  You'd need to drop and create the rule again as well.


Re: FAQ -- 'cache lookup failed' still a puzzle

From
will trillich
Date:
On Wed, Mar 13, 2002 at 10:35:55AM -0800, Stephan Szabo wrote:
>
> > the rule that intercepts 'insert' works like a charm -- and the
> > primary difference is that the 'update' rule (above) calls
> > stage_change() where the create rule doesn't need to -- so i
> > thought that might be the culprit:
> >
> >     db> select stage_change(0,1,1);
> >      stage_change
> >     --------------
> >                 0
> >     (1 row)
> >
> > so it's not. or at least it's got *me* convinced, anyhow.
>
> Well, it's not going to be referenced by name any more, it'll be
> referenced by oid which is changed when you drop and create
> it again.  You'd need to drop and create the rule again as well.

duh! i'd done one too many cut-and-paste's. the function
definition wound up AFTER its first invocation in my source sql,
so naturally it got clobbered in the redefinition...

    create view ... select fn() as ... ;
    drop function fn(); -- hello? is this thing on?
    create function fn() ...;

sometimes it just takes a public post to bring the humility back
home. thanks!

--
DEBIAN NEWBIE TIP #114 from D & E Radel <radel@inet.net.nz>
:
Installing gnome-apt (although a little buggy) is A GREAT WAY TO
FIND OUT WHAT PACKAGES ARE AVAILABLE and install/remove them
with ease!
  Newbie tip: Also I discovered that
    apt-get remove [packagename]
works much better when there are dependancy problems than
    dpkg -r [packagename]

Also see http://newbieDoc.sourceForge.net/ ...