Re: Temporary Tables - Mailing list pgsql-general

-----Original Message-----
From: Patrick.FICHE@AQSACOM.COM [mailto:Patrick.FICHE@AQSACOM.COM]
Sent: Thursday, March 31, 2005 10:06 AM
To: jday@gisolutions.us; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Temporary Tables

 

You can find this in the FAQ

4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL functions?

PL/PgSQL caches function contents, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that table is later dropped and recreated, and the function called again, the function will fail because the cached function contents still point to the old temporary table. The solution is to use EXECUTE for temporary table access in PL/PgSQL. This will cause the query to be reparsed every time.

So as written, the best solution is to use EXECUTE for all queries using temporary tables....

-------------------------------------------------------------------------------------------
Patrick Fiche
email : patrick.fiche@aqsacom.com
tél : 01 69 29 36 18
-------------------------------------------------------------------------------------------

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Joseph M. Day
Sent: jeudi 31 mars 2005 17:50
To: pgsql-general@postgresql.org
Subject: [GENERAL] Temporary Tables

I am having some problems understanding how the temp tables work in PG. I have a relatively lengthy function I am creating that makes frequent use of temporary tables.

 

I am dropping and recreating the temp tables on each run. If I run the procedure the first time via psql it seems to run fine. If I try to immediately run it again I get the following message:

 

Error: relation with OID 22938 does not exist

 

The query it is running is the following:

 

Select Into sTableName TableName From tmp_tblJoin Where ID = 1;

 

Nothing special about it other than "tmp_tblJoin" is defined as a temporary table.

 

I do understand what is happening (I think). There is a stale pointer to the previous instance of the temp table (that no longer exists) which is causing the function to blow up. My question is how to I stop it from storing the OID of the old reference.

 

I am relatively new to PG, but have years of experience with MSSQL and never had to deal with these type of issues. I am using plpqsql and explicitly setting it to volatile.

 

Any help may save the last couple strands of hair on my head :-)

 

Joe,

 

--------------------------------------------
Joseph M. Day
Global Innovative Solutions