Обсуждение: plpgsql - cont'd

Hmm, this is the third time this has happened.. I am using 7.1 Bert 3, so I
expected some things like this... When deleting and adding functions back,
when I run them I get :

ERROR:  plpgsql: cache lookup for proc 49237 failed

-- What's happening there and is there anyway to fix this without having to
dump/restore (which is what I've had to do thus far.. ) ?

Thanks!

-Mitch

"Mitch Vincent" <mitch@venux.net> writes:
> Hmm, this is the third time this has happened.. I am using 7.1 Bert 3, so I
> expected some things like this... When deleting and adding functions back,
> when I run them I get :

> ERROR:  plpgsql: cache lookup for proc 49237 failed

> -- What's happening there and is there anyway to fix this without having to
> dump/restore (which is what I've had to do thus far.. ) ?

dump/restore is the hard way.  If you delete and recreate a function,
the new incarnation has a new OID, so anything that referred to the old
OID is now broken, and has to be deleted/recreated itself.  Triggers
are one such reference.  Cached query plans are another, although I
don't think that's the issue here.  How are you invoking the functions,
anyway?

            regards, tom lane

Ok, after some more playing, this works.

CREATE FUNCTION invoice_payment() RETURNS OPAQUE AS '
BEGIN

   UPDATE invoice_master SET total = total - NEW.amount,updated = now(),
is_paid=(CASE WHEN total::numeric = NEW.amount::numeric THEN TRUE::bool ELSE
FALSE::bool END) WHERE invoice_id = NEW.invoice_id;

   RETURN NEW;

END;
' LANGUAGE 'plpgsql';


Thanks again for answering my stupid little questions, Tom :-)

-Mitch

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Mitch Vincent" <mitch@venux.net>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, January 23, 2001 12:01 PM
Subject: Re: plpgsql - cont'd


> "Mitch Vincent" <mitch@venux.net> writes:
> > Hmm, this is the third time this has happened.. I am using 7.1 Bert 3,
so I
> > expected some things like this... When deleting and adding functions
back,
> > when I run them I get :
>
> > ERROR:  plpgsql: cache lookup for proc 49237 failed
>
> > -- What's happening there and is there anyway to fix this without having
to
> > dump/restore (which is what I've had to do thus far.. ) ?
>
> dump/restore is the hard way.  If you delete and recreate a function,
> the new incarnation has a new OID, so anything that referred to the old
> OID is now broken, and has to be deleted/recreated itself.  Triggers
> are one such reference.  Cached query plans are another, although I
> don't think that's the issue here.  How are you invoking the functions,
> anyway?
>
> regards, tom lane
>