Thread: 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?? 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. 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. Regards fisher
> 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