Thanks for the reponse Jeff. See comments below.
On Wednesday 20 September 2006 05:09 pm, Jeff Davis <pgsql@xxxxxxxxxxx> thus
communicated:
--> 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?
Well, I was assuming that that the table wasn't being dropped and that was
what was causing the error. I can see from your comments, that I was wrong on
that asssumption. I can do this with and execute, but it's going to be a pain
to acomplish. I wonder what good a temporary table is if you can't use the
code which creates it twice in a row with reloading the function?
Anyway, thanks for the response...
-->
--> Regards,
--> Jeff Davis
-->
-->
-->
-->
--> ---------------------------(end of broadcast)---------------------------
--> TIP 6: explain analyze is your friend
-->
--
Terry Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 363-4719
terry@xxxxxxxx
www.turbocorp.com
