temp tables ORACLE/PGSQL

Hi
I used to work with Oracle and now tryin' PostgreSQL I'm a bit
confused.
I found that creating temp table in one session does not make it
available for other sessions for the same user? Is this intended??

Yes, it's natural behave of temp. tables in PostgreSQL. The life cycle of
temp tables is related with session. When session ends then all temp
tables are destroyed. When you wont to use temp tables again, you have to
create it again.

I was tryin to use because of lack of session and package variables in
PGSQL (thats what I know). But I'm not sure if I have to create a
temp table at the beginning of each session? Or just like in Oracle
create temp table definition and all whats temporar is data.

You will lost table definition.

If creating a temp table for each connection is a must then maybe You
can tell me if there is any triger /event that is being called during
connecting to db. I guess that would be the best place to create temp
tables and feed it up with session parameters.

No there are not table or session triggers. You have to create temp tables
from application.

Regards
Pavel Stehule

NO-fisher-SPAM_PLEASE wrote:

Hi
I used to work with Oracle and now tryin' PostgreSQL I'm a bit
confused.
I found that creating temp table in one session does not make it
available for other sessions for the same user? Is this intended??

PostgreSQL does not support global temporary tables. This is one of the
most painful features missing as far as porting from Oracle goes from my
standpoint.

Yes, you need to create the temporary table at the beginning of each
session. Also, stored procedures that use temporary tables are more
painful to write - you need to use EXECUTE for any SQL that references a
temporary table - read the Porting From Oracle section of the PostgreSQL
manual. I'd recommend rereading it several times.

The other option with temporary tables is to emulate a global temporary
table using a normal table and adding a column like this:

session_id INTEGER DEFAULT pg_backend_pid() NOT NULL

and then modifying your select/update/delete statements to include
"where session_id = pg_backend_pid()" so that you only deal with the
data from your current session.

The pg_backend_pid() guaranteed to be unique while connected. You'll
just want to make sure you have a process for deleting rows from the
table so if you get a pg_backend_pid() again you won't have problems.

This has the advantage of not having to create a temporary table at the
beginning of every session, plus your stored procedures don't need to
use EXECUTE. The disadvantage is, you'll have to have some process for
deleting old data from the table, as it will stay around and it will
bite you when you get the same pg_backend_pid() again down the road.

Dennis

This is not entirely correct. We use temp tables all the time in PLpgsql functions and never have to use
execute. We have found that you have to use EXECUTE only in certain circumstances.

stored procedures that use temporary tables are more

painful to write - you need to use EXECUTE for any SQL that references a
temporary table - read the Porting From Oracle section of the PostgreSQL
manual. I'd recommend rereading it several times.

we use this in all our functions that use temp tables, and we use PG Lightning Admin, which refreshes the
connection each time you run a query which gets around a lot of issues with temp tables.

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;

--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com

On Thu, Apr 28, 2005 at 01:14:58PM -0500, Tony Caduto wrote:

This is not entirely correct. We use temp tables all the time in PLpgsql
functions and never have to use
execute. We have found that you have to use EXECUTE only in certain
circumstances.

we use this in all our functions that use temp tables, and we use PG
Lightning Admin, which refreshes the
connection each time you run a query which gets around a lot of issues with
temp tables.

I'm assuming that by "refresh" you mean that you close the connection
to the database and create a new one for every query?

If you do that each time you run a query, doesn't that make temporary
tables pretty much worthless for anything other than PL scratch space?

And it's obviously a ludicrous thing to do in almost all production
cases, so if you're using lightning admin to prototype queries for
production use aren't you going to get burned by the entirely
different behaviour?

Cheers,
Steve

Tony Caduto wrote:

This is not entirely correct. We use temp tables all the time in
PLpgsql functions and never have to use
execute. We have found that you have to use EXECUTE only in certain
circumstances.

we use this in all our functions that use temp tables, and we use PG
Lightning Admin, which refreshes the
connection each time you run a query which gets around a lot of issues
with temp tables.

If you refresh the connection each time you run a query, maybe you
don't need to use EXECUTE with temporary tables in stored procedures,
but who does that in a production database application? Most people want
to re-use connections for performance reasons.

Dennis

We only do the connection "refesh" in the Lightning Admin Query editorfor testing our SQL that uses temp tables.

refreshing the connection eliminates the OID does not exist problems.
We put everything into stored procs and use them from Delphi applications and still never use execute in
our procs.
I think the key is we use ON COMMIT DELETE ROWS when we create our temp tables, we don't ever drop them
until the client disconnects. using the ON COMMIT DELETE ROWS just re uses the the same temp table over
and over again. You only need to use select if you drop the temp table and recreate it multiple times in
the same session.

Here is a example of how we use temp tables:
(NOTE: I ADDED the temp table to this function, you don't actually need it in this case becuase
the cursor can just be refereneced against the select statement)

This can be called over and over again from the same connection because the temp table is not dropped, it's
just re-used. when the client does disconnect it gets dropped.
You could also truncate the temp table at the end of the function if you wanted, then you don't have
data sitting in the table until the next function call.

CREATE or REPLACE FUNCTION admin.spadm_get_status_list()
RETURNS pg_catalog.refcursor AS
$BODY$
DECLARE
return_cursor refcursor;
BEGIN
return_cursor = 'return_cursor';
IF iftableexists('temp_get_status_list') THEN
RAISE NOTICE 'temp table already exists';
ELSE
CREATE TEMP TABLE temp_get_status_list
(
STATUS_ID SMALLINT,
DESCRIPTION VARCHAR(50))WITHOUT OIDS ON COMMIT DELETE ROWS;
END IF;
INSERT INTO temp_get_status_list
(
STATUS_ID, DESCRIPTION
)
SELECT status_id, description
FROM admin.admin_status
ORDER BY 1;

OPEN return_cursor FOR SELECT * FROM temp_get_status_list;

RETURN return_cursor;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

If you refresh the connection each time you run a query, maybe you
don't need to use EXECUTE with temporary tables in stored procedures,
but who does that in a production database application? Most people want
to re-use connections for performance reasons.

Dennis

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com

Dennis Sacks wrote:

The disadvantage is, you'll have to have some process for deleting
old data from the table, as it will stay around and it will bite you
when you get the same pg_backend_pid() again down the road.

Rather than use pg_backend_id(), why not just assign session IDs from a
sequence? You would still get the problem of stale session data so you'd
probably still want a periodic cleaner process, but you won't need to
worry about session ID collision.

-Neil