[GENERAL] temp table question

[Kevin Kempter]

Hi list;

If I create a temp table (i.e. create temp table xyz as select from ...) is 
the scope of this table limited to a session. Meaning,  can several sessions 
all run the above create temp table statement all referencing the same temp 
table name at the same time?


Thanks in advance

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] temp table question

[brian]

Kevin Kempter wrote:

Hi list;

If I create a temp table (i.e. create temp table xyz as select from 
...) is the scope of this table limited to a session. Meaning,  can 
several sessions all run the above create temp table statement all 
referencing the same temp table name at the same time?


Yes, temp tables exist outside of the normal schema and are limited to
the session they are created in. I'm not sure quite how it works
internally but my understanding is that it would be something akin to:

your_session.your_temp_table

rather than:

your_schema.your_temp_table

so collisions won't occur.

See:

http://www.postgresql.org/docs/8.2/static/sql-createtable.html


Although the syntax of CREATE TEMPORARY TABLE resembles that of the
SQL standard, the effect is not the same. In the standard, temporary
tables are defined just once and automatically exist (starting with
empty contents) in every session that needs them. PostgreSQL instead
requires each session to issue its own CREATE TEMPORARY TABLE command
for each temporary table to be used. This allows different sessions
to use the same temporary table name for different purposes, whereas
the standard's approach constrains all instances of a given temporary
table name to have the same table structure.


b

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] temp table question

[Scott Marlowe]

On Feb 1, 2008 9:06 PM, Kevin Kempter [EMAIL PROTECTED] wrote:
 Hi list;

 If I create a temp table (i.e. create temp table xyz as select from ...) is
 the scope of this table limited to a session. Meaning,  can several sessions
 all run the above create temp table statement all referencing the same temp
 table name at the same time?

Temp tables are only visible to the session that created them.  See

http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html

---(end of broadcast)---
TIP 6: explain analyze is your friend