Temporary Tables

Started by Joseph M. Dayabout 21 years ago10 messagesgeneral

jday@gisolutions.us

about 21 years ago

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

Richard Huxton

dev@archonet.com

about 21 years ago

In reply to: Joseph M. Day (#1)

Re: Temporary Tables

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

Patrick Fiche

patrick.fiche@aqsacom.com

about 21 years ago

In reply to: Richard Huxton (#2)

Re: Temporary Tables

You can find this in the FAQ

BM_4_264.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
tel : 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

Import Notes

Resolved by subject fallback

Joseph M. Day

jday@gisolutions.us

about 21 years ago

In reply to: Patrick Fiche (#3)

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

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

Patrick Fiche

patrick.fiche@aqsacom.com

about 21 years ago

In reply to: Joseph M. Day (#4)

Re: 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.ht
ml#PLPGSQL-RECORDS-ITERATING
<http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.h
tml#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,

You can find this in the FAQ

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

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

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

Import Notes

Resolved by subject fallback

Joseph M. Day

jday@gisolutions.us

about 21 years ago

In reply to: Patrick Fiche (#5)

Re: Temporary Tables

Great this is exactly what I was looking for. I read this but was not
completely sure that you could EXECUTE on it.

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.

I am starting to feel comfortable with this. Thanks for all the help!!

Joe,

-----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-structure
s.html#PLPGSQL-RECORDS-ITERATING

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

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

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,

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

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

Tony Caduto

tony_caduto@amsoftwaredesign.com

about 21 years ago

In reply to: Joseph M. Day (#6)

Re: Temporary Tables

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. This means 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 exists I 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 temp table. 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

Show quoted text

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

Import Notes

Resolved by subject fallback

Tony Caduto

tony_caduto@amsoftwaredesign.com

about 21 years ago

In reply to: Tony Caduto (#7)

Re: Temporary Tables

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

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 choice is PG Lighting admin (http://www.amsoftwaredesign.com)
Lightning admin refreshes the connection each time you run your query,hence refreshing the cached objects.

Show quoted text

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

Import Notes

Resolved by subject fallback

Alban Hertroys

alban@magproductions.nl

about 21 years ago

In reply to: Joseph M. Day (#6)

Re: Temporary Tables

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

#10

Scott Marlowe

smarlowe@g2switchworks.com

about 21 years ago

In reply to: Alban Hertroys (#9)

Re: Temporary Tables

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.