Re: Question Regarding a Temporary Table

On Wed, 2006-09-20 at 16:51 -0400, Terry Lee Tucker wrote:
> Greetings:
>
> I have have a plpgsql function that creates a temporary table to facilitate
> some processing. Here is the code:
> CREATE TEMP TABLE tmp (code VARCHAR,
> booked INTEGER,
> avail INTEGER,
> covered INTEGER,
> profit NUMERIC (10,2),
> billed NUMERIC (10,2))
> WITHOUT OIDS ON COMMIT DROP;
>
> Note the "ON COMMIT DROP". I would expect this table to disapear after the
> function completes, but it does not. Also, if I execute the the function

In 8.1 it does drop for me on COMMIT, as you would expect. I don't know
about 7.4.

> twice in a row from the psql interface, on the second try, I get the
> following error:
> sev=# select * from custSprtRpt('04/01/06', current_date);
> NOTICE: custSprtRpt ()
> ERROR: relation with OID 123654 does not exist
> CONTEXT: PL/pgSQL function "custsprtrpt" line 39 at SQL statement
>
> If have to reload the function with \i sqlfunc/custSprtRpt.plsql so that it
> will work again. Why is this?

PL/pgSQL caches query plans. Unfortunately, there is currently no good
mechanism to invalidate the plans, and the function is using a stale
plan with an OID that no longer exists.

The workaround is to use "EXECUTE" in the function, and build the query
from a string. That prevents PL/pgSQL from caching the plan.

What confuses me is, if it didn't drop your table, why would it say the
oid doesn't exist?

Regards,
Jeff Davis