Thread: Temporary Tables

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

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

Joseph M. Day wrote:
> 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.

You don't - it caches plans for queries at compile-time (which is the
first time the function is run). You have to refer to temporary tables
using EXECUTE. Always. Alternatively, you could use a language that
doesn't cache query-plans, e.g. pltcl.

> 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.

Volatile? What setting is that?

--
   Richard Huxton
   Archonet Ltd

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

-----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 18:25
To: Patrick.FICHE@AQSACOM.COM; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Temporary Tables

Thanks, I thought there might be a way to force it not to do this.

 

So I guess for my example I am going to need to create another temporary table to retrieve the results of my query, which of course I will also have to be created via EXECUTE, since EXECUTE will not work in this situation to store data in sTableName.

 

Any other more elegant ideas to retrieve the data from this?

-------------------

Select Into sTableName TableName From tmp_tblJoin Where ID = 1;

 

Thanks for the help!

 

Joe,

 

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

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Patrick.FICHE@aqsacom.com
Sent: Thursday, March 31, 2005 10:45 AM
To: jday@gisolutions.us; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Temporary Tables

Depending on your need, I think you could use the structure : FOR-IN-EXECUTE

 

 

http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

 

Tell us what you exactly want to do if this doesn't match your needs...

 

 

-------------------------------------------------------------------------------------------
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 18:25
To: Patrick.FICHE@AQSACOM.COM; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Temporary Tables

Thanks, I thought there might be a way to force it not to do this.

 

So I guess for my example I am going to need to create another temporary table to retrieve the results of my query, which of course I will also have to be created via EXECUTE, since EXECUTE will not work in this situation to store data in sTableName.

 

Any other more elegant ideas to retrieve the data from this?

-------------------

Select Into sTableName TableName From tmp_tblJoin Where ID = 1;

 

Thanks for the help!

 

Joe,

 

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

Create your temp tables like this:

CREATE TEMP TABLE mytest
(

)WITHOUT OIDS ON COMMIT DELETE ROWS

PG holds onto the temp table for the duration of the connection, when the connection ends all temp tables are dropped.
Thismeans you can simply reuse the same tables for the duration of the connection. 

In my functions before I create the temp table I check to see if it exists with function below, if the table already
existsI don't try and create it. 
You can also run into issues where you have to do your SQL in execute statements so it can use the proper OID for the
temptable.  We have been using this method with temp tables and it works great. 


(author unknown)

CREATE or REPLACE FUNCTION public.iftableexists( varchar)
RETURNS pg_catalog.bool AS
$BODY$
DECLARE

 BEGIN

     /* check the table exist in database and is visible*/
 perform n.nspname ,c.relname
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid
= c.relnamespace
where n.nspname like 'pg_temp_%' AND pg_catalog.pg_table_is_visible(c.oid)
AND Upper(relname) = Upper($1);

     IF FOUND THEN
        RETURN TRUE;
     ELSE
        RETURN FALSE;
     END IF;

 END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE


>
> From: "Joseph M. Day" <jday@gisolutions.us>
> Date: 2005/03/31 Thu AM 10:49:37 EST
> 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
>
>
>
>

You don't need to use execute if you create your temp tables  like this:

CREATE TEMP TABLE mytest
(

)WITHOUT OIDS ON COMMIT DELETE ROWS

Then use the follwoing function(author unknown) to see if the temp table already exists:

CREATE or REPLACE FUNCTION public.iftableexists( varchar)
RETURNS pg_catalog.bool AS
$BODY$
DECLARE

 BEGIN

     /* check the table exist in database and is visible*/
 perform n.nspname ,c.relname
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid
= c.relnamespace
where n.nspname like 'pg_temp_%' AND pg_catalog.pg_table_is_visible(c.oid)
AND Upper(relname) = Upper($1);

     IF FOUND THEN
        RETURN TRUE;
     ELSE
        RETURN FALSE;
     END IF;

 END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE


Here is a example using the iftableexists function:

CREATE or REPLACE FUNCTION annual.spann_get_status_list()
RETURNS pg_catalog.refcursor AS
$BODY$
DECLARE
return_cursor refcursor;

BEGIN
--give the cursor a name, this is the name we will use to ref the cursor on the client
return_cursor = 'return_cursor';
--create temp table,but first check if it exists.
--If it already exists for this session we will not recreate
IF iftableexists('temp_get_status_list_an') THEN
         RAISE NOTICE 'temp table already exists';
ELSE
CREATE TEMP TABLE temp_get_status_list_an
(
    STATUS_ID SMALLINT,
    DESCRIPTION VARCHAR(50))WITHOUT OIDS ON COMMIT DELETE ROWS;
END IF;
INSERT INTO temp_get_status_list_an
(
STATUS_ID,DESCRIPTION
)
SELECT    status_id, description
     FROM annual.annual_status;

-- Open the cursor on the temp table
OPEN return_cursor FOR SELECT * FROM temp_get_status_list_an ORDER BY 1;
-- Return the pointer back to the caller
RETURN return_cursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Another key is not to use PG ADMIN III, it uses the same connection to the database for every query you run, a better
choiceis PG Lighting admin (http://www.amsoftwaredesign.com) 
Lightning admin refreshes the connection each time you run your query,hence refreshing the cached objects.


>
> From: "Joseph M. Day" <jday@gisolutions.us>
> Date: 2005/03/31 Thu AM 11:25:02 EST
> To: <Patrick.FICHE@AQSACOM.COM>,  <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Temporary Tables
>
> Thanks, I thought there might be a way to force it not to do this.
>
> So I guess for my example I am going to need to create another temporary
> table to retrieve the results of my query, which of course I will also
> have to be created via EXECUTE, since EXECUTE will not work in this
> situation to store data in sTableName.
>
> Any other more elegant ideas to retrieve the data from this?
> -------------------
> Select Into sTableName TableName From tmp_tblJoin Where ID = 1;
>
> Thanks for the help!
>
> Joe,
>
>
> -----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
>

Joseph M. Day wrote:
> Just out of curiosity, what is the performance of this? In MSSQL the
> only way to do something equivalent to this was to use a cursor. Cursors
> are painfully slow, so they are to be used as a last resort.

On what do you base that cursors are slow?

--
Alban Hertroys

On Fri, 2005-04-01 at 04:32, Alban Hertroys wrote:
> Joseph M. Day wrote:
> > Just out of curiosity, what is the performance of this? In MSSQL the
> > only way to do something equivalent to this was to use a cursor. Cursors
> > are painfully slow, so they are to be used as a last resort.
>
> On what do you base that cursors are slow?

I'm guessing that in MSSQL, cursors are slow, so the original poster
simply assumed that on all other databases, cursors are slow.

I can assure him that in PostgreSQL they are not any slower than any
other method of accessing data i've used, and usually faster.