Postgres Pro
Downloads
Home   >   mailing lists

Thread: TEMP tables

TEMP tables

From
Lex Berezhny
Date:
hi,

I have a plpgsql procedure that needs to create a temporary table, use
it as a stack internally, and then disgard it when the procedure exits.

This works great if I only call this procedure ONCE per session. Calling
it a second time within the same session produces:

WARNING:  Error occurred while executing PL/pgSQL function render
WARNING:  line 8 at SQL statement
ERROR:  Relation 'stack' already exists

(line 8 is the CREATE TEMP TABLE statement)

But when I add DROP TABLE stack at the end (but within) the procedure,
while it works okay the first time around, it produces the following
error after first execution:

WARNING:  Error occurred while executing PL/pgSQL function render
WARNING:  line 9 at SQL statement
ERROR:  pg_class_aclcheck: relation 20900 not found

(line 9 is a SELECT * FROM stack statement)

It seems that after a table is created, dropped and then created again
it's not recognised.

When i do this outside of a procedure it works fine (i can create/drop a
temp table as many times as i want). Is there something magical that
procedures do when a table is created inside of them? (like cache the
relation id, and when you swap it underneath them, they panic?) Just
thoughts. Honestly, I have no clue :-) Which is why I am asking :-)

What are the recommendations or solutions on using temporary tables
inside functions on a per call basis?

thanks a lot,
- lex

-- 
Lex Berezhny <LBerezhny@DevIS.com>

Re: TEMP tables

From
Bruce Momjian
Date:
You need to use EXECUTE.  See the FAQ, item 4.26.

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

Lex Berezhny wrote:
> hi,
> 
> I have a plpgsql procedure that needs to create a temporary table, use
> it as a stack internally, and then disgard it when the procedure exits.
> 
> This works great if I only call this procedure ONCE per session. Calling
> it a second time within the same session produces:
> 
> WARNING:  Error occurred while executing PL/pgSQL function render
> WARNING:  line 8 at SQL statement
> ERROR:  Relation 'stack' already exists
> 
> (line 8 is the CREATE TEMP TABLE statement)
> 
> But when I add DROP TABLE stack at the end (but within) the procedure,
> while it works okay the first time around, it produces the following
> error after first execution:
> 
> WARNING:  Error occurred while executing PL/pgSQL function render
> WARNING:  line 9 at SQL statement
> ERROR:  pg_class_aclcheck: relation 20900 not found
> 
> (line 9 is a SELECT * FROM stack statement)
> 
> It seems that after a table is created, dropped and then created again
> it's not recognised.
> 
> When i do this outside of a procedure it works fine (i can create/drop a
> temp table as many times as i want). Is there something magical that
> procedures do when a table is created inside of them? (like cache the
> relation id, and when you swap it underneath them, they panic?) Just
> thoughts. Honestly, I have no clue :-) Which is why I am asking :-)
> 
> What are the recommendations or solutions on using temporary tables
> inside functions on a per call basis?
> 
> thanks a lot,
> 
>  - lex
> 
> -- 
> Lex Berezhny <LBerezhny@DevIS.com>
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073

Re: TEMP tables

From
Tomasz Myrta
Date:
Lex Berezhny wrote:
> hi,
> 
> I have a plpgsql procedure that needs to create a temporary table, use
> it as a stack internally, and then disgard it when the procedure exits.
> 

<cut>

> What are the recommendations or solutions on using temporary tables
> inside functions on a per call basis?
> 
> thanks a lot,

Instead of Bruce solution you can:
1. Create your temporary table only once after you login into database.
Before each execution of your function just delete/truncate this temporary table.
You don't need to drop this table, because it is 
automatically dropped when the session is finished.

2. Use global table as a stack. Use some sessionid to indetify stack for
each procedure execution. Create index on sessionid. There is one more
advantage on this solution - you can use views without recreating them.


Regards,
Tomasz Myrta