pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTemporary tables
12+ messages / 11 participants
[nested] [flat]
* Temporary tables
@ 1999-01-11 22:39 Pawel Pierscionek <pawel@astercity.net>
0 siblings, 0 replies; 12+ messages in thread
From: Pawel Pierscionek @ 1999-01-11 22:39 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have to play a lot with temp tables.
Could you pls, give me a hint on emulating them under Postgres.
I have to use them quite often and with 10000 rows each.
I don't use indices cause I only cache a search result and don't need
any sorting, searching on temptables.
What's faster:
1)
once:
create tmptable;
many times:
delete from tmptable;
insert into tmptable select ... ;
or
2)
many times:
drop tmptable;
select ... into tmptable;
I prefer the second way since I have a lot of concurrent access and
create unique named tables under each transaction.
Unfortunately the DROP thing dumps a core under PLPGSQL function. :(
TIA,
Pawel
^ permalink raw reply [nested|flat] 12+ messages in thread
* Temporary tables
@ 1999-06-28 16:06 Tiberiu Craciun <tiberiu@akela.ro>
0 siblings, 1 reply; 12+ messages in thread
From: Tiberiu Craciun @ 1999-06-28 16:06 UTC (permalink / raw)
To: pgsql-sql@hub.org
How can I use temporary tables in PostgresSQL 6.5 ?
Thanks
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [SQL] Temporary tables
@ 1999-06-28 18:45 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Tiberiu Craciun <tiberiu@akela.ro>
0 siblings, 0 replies; 12+ messages in thread
From: Bruce Momjian @ 1999-06-28 18:45 UTC (permalink / raw)
To: Tiberiu Craciun <tiberiu@akela.ro>; +Cc: pgsql-sql@hub.org
[Charset iso-8859-1 unsupported, filtering to ASCII...]
> How can I use temporary tables in PostgresSQL 6.5 ?
>
> Thanks
>
CREATE TEMP TABLE xxx(x int);
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 12+ messages in thread
* Temporary tables
@ 2002-01-10 23:02 SHELTON,MICHAEL (Non-HP-Boise,ex1) <michael_shelton@non.hp.com>
0 siblings, 1 reply; 12+ messages in thread
From: SHELTON,MICHAEL (Non-HP-Boise,ex1) @ 2002-01-10 23:02 UTC (permalink / raw)
To: pgsql-sql
Hello,
I am attempting to optimize a number of queries that are dependant on a
first query (big join) that is expensive to run. Instead of having the join
be executed for each subsequent query I would like to put the results of the
first join into a temp table. I found syntax for creating a temp table in
the idocs (SELECT <foo> INTO <tmp_table> FROM <table>) and this works fine.
My problem is that the table isn't very "temporary". Is there a way to have
the table drop automagically when I'm done with it. I'm accessing it
through PHP 4.0 via apache on a 7.1.3 db. I would really like it to drop
when I close the connection.
A 2nd question is what about concurrency? Is there a way to hide this temp
table from other concurrent connections to the db (I thought I remember
reading an earlier post about this) so that you don't get name conflicts
with the temp table?
Thanks,
Mike Shelton
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Temporary tables
@ 2002-01-11 17:17 Peter T. Brown <peter@memeticsystems.com>
parent: SHELTON,MICHAEL (Non-HP-Boise,ex1) <michael_shelton@non.hp.com>
0 siblings, 0 replies; 12+ messages in thread
From: Peter T. Brown @ 2002-01-11 17:17 UTC (permalink / raw)
To: 'SHELTON,MICHAEL \(Non-HP-Boise,ex1\)' <michael_shelton@non.hp.com>; pgsql-sql
Look at the syntax for CREATE TABLE AS
You can CREATE TEMP TABLE <tablename> AS SELECT ...
This temp table will be removed automagically
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of SHELTON,MICHAEL
(Non-HP-Boise,ex1)
Sent: Thursday, January 10, 2002 3:03 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Temporary tables
Hello,
I am attempting to optimize a number of queries that are dependant on a
first query (big join) that is expensive to run. Instead of having the join
be executed for each subsequent query I would like to put the results of the
first join into a temp table. I found syntax for creating a temp table in
the idocs (SELECT <foo> INTO <tmp_table> FROM <table>) and this works fine.
My problem is that the table isn't very "temporary". Is there a way to have
the table drop automagically when I'm done with it. I'm accessing it
through PHP 4.0 via apache on a 7.1.3 db. I would really like it to drop
when I close the connection.
A 2nd question is what about concurrency? Is there a way to hide this temp
table from other concurrent connections to the db (I thought I remember
reading an earlier post about this) so that you don't get name conflicts
with the temp table?
Thanks,
Mike Shelton
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 12+ messages in thread
* temporary tables
@ 2002-06-11 23:07 Marie G. Tuite <marie.tuite@edisonaffiliates.com>
0 siblings, 1 reply; 12+ messages in thread
From: Marie G. Tuite @ 2002-06-11 23:07 UTC (permalink / raw)
To: pgsql-sql
Hey there,
Does anybody know where temporary table info is stored for the duration of
the temp table session?
Thanks.
Marie
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: temporary tables
@ 2002-06-11 23:33 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Marie G. Tuite <marie.tuite@edisonaffiliates.com>
0 siblings, 0 replies; 12+ messages in thread
From: Bruce Momjian @ 2002-06-11 23:33 UTC (permalink / raw)
To: marie.tuite@edisonaffiliates.com; +Cc: pgsql-sql
Marie G. Tuite wrote:
> Hey there,
>
> Does anybody know where temporary table info is stored for the duration of
> the temp table session?
The mapping is kept in a per-backend memory area. You can't really
access the mapping, but in 7.3, it will be in a temp schema which will
be visible, I think.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 12+ messages in thread
* Temporary tables
@ 2003-09-27 13:31 George A.J <jinujosein@yahoo.com>
0 siblings, 2 replies; 12+ messages in thread
From: George A.J @ 2003-09-27 13:31 UTC (permalink / raw)
To: pgsql-sql
hi,
I am using postgresql 7.3.2. Is there any function to determine
whether a table exists in the database.Or is there any function
that returns the current temp schema.
I am using a pl/pgsql function that create and drop a temporary table.
The procedure run correctly for the first time for each database connection.
If I run the same procedure second time in the same connection it produces the error
"ERROR: pg_class_aclcheck: relation 219389 not found
WARNING: Error occurred while executing PL/pgSQL function testFun
WARNING: line 20 at SQL statement "
Here is the function ....
---------------------------------------------------------
CREATE OR REPLACE FUNCTION testFun( varchar(10) ) RETURNS setof int
AS
'
DECLARE
--Aliases for parameters
vSBAcNo ALIAS FOR $1;
--local variables
vRow RECORD;
BEGIN
-- create a tempory table to hold the numbers
CREATE TABLE tempTable
(
testNo int
) ;
for vRow IN select Entryno from EntryTable LOOP
return next vRow.Entryno;
insert into tempTable values( vRow.Entryno);
end loop;
drop table tempTable;
return;
END;'
LANGUAGE 'plpgsql';
-------------------------------------------------------------
If i commented the "insert into tempTable values( vRow.Entryno);" line
the function works correctly. The problem is the oid of tempTable is kept when
the function is first executed. the next execution creates another table with
different oid. So the insert fails.
I want to check whether the temporary table exist. If exist do not create the
temporary table in subsequent calls and do not dorp it. This will solve the problem.
When i searched the pg_class i found the temp table name more than once.
ie, a temporary table is created for each connection.I cannot distingush
the temp tables. But the tables are in different schema.
Is there a method to get the current temporary schema? How postgres distinguish
this temp tables?.Is there a way to distinguish temporary tables.
The entries in pg_class table is same except the schema.
When i used the current_schema() function it returns public.
There is a lot of functions that uses temporary tables. I think that there is
an option when creating temp tables in postgres 7.4 . But no way to use 7.4
now it is a working database.
can i write a function to check the existance of the temporary table...
please help...
jinujose
---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Temporary tables
@ 2003-09-27 16:21 Richard Huxton <dev@archonet.com>
parent: George A.J <jinujosein@yahoo.com>
1 sibling, 0 replies; 12+ messages in thread
From: Richard Huxton @ 2003-09-27 16:21 UTC (permalink / raw)
To: George A.J <jinujosein@yahoo.com>; pgsql-sql
On Saturday 27 September 2003 14:31, George A.J wrote:
> hi,
>
> I am using postgresql 7.3.2. Is there any function to determine
> whether a table exists in the database.Or is there any function
> that returns the current temp schema.
> I am using a pl/pgsql function that create and drop a temporary table.
> The procedure run correctly for the first time for each database
> connection. If I run the same procedure second time in the same connection
> it produces the error
>
> "ERROR: pg_class_aclcheck: relation 219389 not found
> WARNING: Error occurred while executing PL/pgSQL function testFun
> WARNING: line 20 at SQL statement "
This is because plpgsql is "compiled" and so the reference to tempTable gets
fixed the first time it is called. In your case, the oid was 219389.
Now, the second time you call the function, the temp table gets re-created,
gets a new OID and the old reference is no longer valid your insert line.
There are two solutions:
1. Use pltcl/plperl or some other interpreted language that doesn't compile in
table references.
2. Build your insert statement using EXECUTE ''INSERT INTO tempTable ''...
This second passes the query string into the parser, so it works just fine for
your example. I think some of this is covered in the manuals, you can
certainly find plenty on it in the archives.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Temporary tables
@ 2003-09-27 17:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: George A.J <jinujosein@yahoo.com>
1 sibling, 0 replies; 12+ messages in thread
From: Tom Lane @ 2003-09-27 17:09 UTC (permalink / raw)
To: George A.J <jinujosein@yahoo.com>; +Cc: pgsql-sql
"George A.J" <jinujosein@yahoo.com> writes:
> When i searched the pg_class i found the temp table name more than once.
> ie, a temporary table is created for each connection.I cannot distingush
> the temp tables. But the tables are in different schema.
> Is there a method to get the current temporary schema?
Not directly, but you could try something like
perform * from pg_class where
relname = 'mytable' and pg_table_is_visible(oid);
if not found then ... -- create the table
The visibility test would not succeed for temp tables belonging to other
backends. (If 'mytable' is also used as the name of a regular table
then this isn't quite good enough, but I think just avoiding such a name
collision is easier than extending the check to reject non-temp tables.)
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Temporary tables
@ 2003-09-29 05:16 vijaykumar M <m_vijaykumar@hotmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: vijaykumar M @ 2003-09-29 05:16 UTC (permalink / raw)
To: jinujosein@yahoo.com; pgsql-sql
Hi,
Try the below steps ..
1. Write one procedure to create tempory table (generic purpose)
**********************************************************************
CREATE OR REPLACE FUNCTION SP_CREATE_TEMP_TABLE () RETURNS VARCHAR AS '
DECLARE
L_SchemaName name;
BEGIN
EXECUTE ''CREATE TEMPORARY TABLE temp_table_gen (X VARCHAR);'';
SELECT schemaname INTO L_SchemaName FROM pg_stat_user_tables where relname
=''temp_table_gen'';
RETURN L_SchemaName;
END;
' LANGUAGE 'plpgsql';
**********************************************************************
2. Call the above (generic) procedure to get the temporary table schema
name.. by using that schema name ..you can check whether the (real)
temporary table is exists or not.
**************************************************************************
select into L_SchemaName * from SP_CREATE_TEMP_TABLE(); -- get the
schemaname
execute ''drop table temp_table_gen;''; -- drop the temptable
select schemaname into L_Schema from pg_stat_user_tables where
relname=''temp_total_count'' and schemaname =''''||L_SchemaName||'''';
if (L_Schema is null) then
EXECUTE ''CREATE TEMPORARY TABLE temp_total_count (TOTAL_COUNT
NUMERIC);'';
ELSE
EXECUTE ''DELETE FROM temp_total_count;'';
END IF;
**************************************************************************
I hope this will help u to solve these temporary table issues..
With Regards
Vijay
>From: "George A.J" <jinujosein@yahoo.com>
>To: pgsql-sql@postgresql.org
>Subject: [SQL] Temporary tables
>Date: Sat, 27 Sep 2003 06:31:39 -0700 (PDT)
>
>
>hi,
>
>I am using postgresql 7.3.2. Is there any function to determine
>whether a table exists in the database.Or is there any function
>that returns the current temp schema.
>I am using a pl/pgsql function that create and drop a temporary table.
>The procedure run correctly for the first time for each database
>connection.
>If I run the same procedure second time in the same connection it produces
>the error
>
>"ERROR: pg_class_aclcheck: relation 219389 not found
>WARNING: Error occurred while executing PL/pgSQL function testFun
>WARNING: line 20 at SQL statement "
>
>Here is the function ....
>
>---------------------------------------------------------
>CREATE OR REPLACE FUNCTION testFun( varchar(10) ) RETURNS setof int
>AS
>'
>DECLARE
> --Aliases for parameters
> vSBAcNo ALIAS FOR $1;
> --local variables
> vRow RECORD;
>
>BEGIN
> -- create a tempory table to hold the numbers
> CREATE TABLE tempTable
> (
> testNo int
> ) ;
>
> for vRow IN select Entryno from EntryTable LOOP
>
> return next vRow.Entryno;
>
> insert into tempTable values( vRow.Entryno);
>
> end loop;
>
> drop table tempTable;
>
> return;
>
>END;'
>
>LANGUAGE 'plpgsql';
>
>-------------------------------------------------------------
>
>If i commented the "insert into tempTable values( vRow.Entryno);" line
>the function works correctly. The problem is the oid of tempTable is kept
>when
>the function is first executed. the next execution creates another table
>with
>different oid. So the insert fails.
>
>I want to check whether the temporary table exist. If exist do not create
>the
>temporary table in subsequent calls and do not dorp it. This will solve the
>problem.
>
>When i searched the pg_class i found the temp table name more than once.
>ie, a temporary table is created for each connection.I cannot distingush
>the temp tables. But the tables are in different schema.
>Is there a method to get the current temporary schema? How postgres
>distinguish
>this temp tables?.Is there a way to distinguish temporary tables.
>The entries in pg_class table is same except the schema.
>When i used the current_schema() function it returns public.
>
>There is a lot of functions that uses temporary tables. I think that there
>is
>an option when creating temp tables in postgres 7.4 . But no way to use 7.4
>now it is a working database.
>
>can i write a function to check the existance of the temporary table...
>please help...
>
>jinujose
>
>
>---------------------------------
>Do you Yahoo!?
>The New Yahoo! Shopping - with improved product search
_________________________________________________________________
Keep up with the pace of change. Register for My Tech Ed.
http://server1.msn.co.in/sp03/teched/index.asp Realise your potential!
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Temporary tables
@ 2003-10-01 03:17 George A.J <jinujosein@yahoo.com>
parent: vijaykumar M <m_vijaykumar@hotmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: George A.J @ 2003-10-01 03:17 UTC (permalink / raw)
To: pgsql-sql
Thanks to all of you for your suggestions. the problem is solved by creating a function
istableexist() that returns whether a table exist or not. the function is bellow.
CREATE FUNCTION istableexist(varchar) RETURNS bool AS '
DECLARE
BEGIN
/* check the table exist in database and is visible*/
PERFORM relname,relnamespace FROM pg_class
WHERE relkind = ''r''
AND Upper(relname) = Upper($1)
AND pg_table_is_visible(oid);
IF FOUND THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;'
LANGUAGE 'plpgsql';
thanks again
jinujose
---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2003-10-01 03:17 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-11 22:39 Temporary tables Pawel Pierscionek <pawel@astercity.net>
1999-06-28 16:06 Temporary tables Tiberiu Craciun <tiberiu@akela.ro>
1999-06-28 18:45 ` Bruce Momjian <maillist@candle.pha.pa.us>
2002-01-10 23:02 Temporary tables SHELTON,MICHAEL (Non-HP-Boise,ex1) <michael_shelton@non.hp.com>
2002-01-11 17:17 ` Peter T. Brown <peter@memeticsystems.com>
2002-06-11 23:07 temporary tables Marie G. Tuite <marie.tuite@edisonaffiliates.com>
2002-06-11 23:33 ` Re: temporary tables Bruce Momjian <pgman@candle.pha.pa.us>
2003-09-27 13:31 Temporary tables George A.J <jinujosein@yahoo.com>
2003-09-27 16:21 ` Richard Huxton <dev@archonet.com>
2003-09-27 17:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-29 05:16 Re: Temporary tables vijaykumar M <m_vijaykumar@hotmail.com>
2003-10-01 03:17 ` George A.J <jinujosein@yahoo.com>
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