FAQ -- 'cache lookup failed' still a puzzle - Mailing list pgsql-general

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!