Re: Question Regarding a Temporary Table - Mailing list pgsql-general

Thanks for the reponse Jeff. See comments below.

On Wednesday 20 September 2006 05:09 pm, Jeff Davis <pgsql@j-davis.com> 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@esc1.com
www.turbocorp.com