Обсуждение: Question Regarding a Temporary Table
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 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? Also, this function does not perform any updates to a permanent database table. Anyone have any insight into this issue? sev=# select version(); version -------------------------------------------------------------------------------------------------------------- PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-49) (1 row) Thanks... -- 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
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
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
On Wed, 2006-09-20 at 17:29 -0400, Terry Lee Tucker wrote: > 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? > Well, the problem is not with temporary tables so much as the cached plans. PL/pgSQL decides when the function is first run that the temporary table you're using has OID 123654 (or whatever), and rather than using the table name on the function call, it assumes that the OID has not changed. So, a temporary table is still useful for any situation where it doesn't cache the query plan (like a normal query, or an EXECUTE inside PL/pgSQL). But yes, it is frustrating, and will hopefully be fixed in later versions. Regards, Jeff Davis