Thread: 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