pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTEMP tables
4+ messages / 4 participants
[nested] [flat]
* TEMP tables
@ 2003-02-03 01:25 Lex Berezhny <LBerezhny@DevIS.com>
0 siblings, 2 replies; 4+ messages in thread
From: Lex Berezhny @ 2003-02-03 01:25 UTC (permalink / raw)
To: pgsql-sql
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>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: TEMP tables
@ 2003-02-03 01:58 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Lex Berezhny <LBerezhny@DevIS.com>
1 sibling, 0 replies; 4+ messages in thread
From: Bruce Momjian @ 2003-02-03 01:58 UTC (permalink / raw)
To: Lex Berezhny <LBerezhny@DevIS.com>; +Cc: pgsql-sql
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: TEMP tables
@ 2003-02-03 09:55 Tomasz Myrta <jasiek@klaster.net>
parent: Lex Berezhny <LBerezhny@DevIS.com>
1 sibling, 0 replies; 4+ messages in thread
From: Tomasz Myrta @ 2003-02-03 09:55 UTC (permalink / raw)
To: Lex Berezhny <LBerezhny@DevIS.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Temp Tables
@ 2004-09-27 13:12 sad <sad@bankir.ru>
0 siblings, 0 replies; 4+ messages in thread
From: sad @ 2004-09-27 13:12 UTC (permalink / raw)
To: pgsql-sql
Hi
I propose you again a topic for discuss.
Do you find useful the clause "ON COMMIT DELETE ROWS" ?
like Oracle allows to specify for a temporery table
And is it necessary to delete temp table from The Dictionary wherever
disconnect and recrete it each session it needed ? (I mean that some problems
better to solve with so-called GLOBAL TEMP TABLEs which are created once the
DB created and DBMS keeps them empty (by ON COMMIT DELETE ROWS) and only own
rows each user sees in a transaction)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-09-27 13:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-03 01:25 TEMP tables Lex Berezhny <LBerezhny@DevIS.com>
2003-02-03 01:58 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-02-03 09:55 ` Tomasz Myrta <jasiek@klaster.net>
2004-09-27 13:12 Temp Tables sad <sad@bankir.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox