From: | Jeff Davis <pgsql(at)j-davis(dot)com> |
---|---|
To: | Terry Lee Tucker <terry(at)leetuckert(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Question Regarding a Temporary Table |
Date: | 2006-09-20 21:09:40 |
Message-ID: | 1158786580.30652.136.camel@dogma.v10.wvs |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | stevethames | 2006-09-20 21:18:37 | IF EXISTS |
Previous Message | Terry Lee Tucker | 2006-09-20 20:51:52 | Question Regarding a Temporary Table |