Skip site navigation (1) Skip section navigation (2)

Global temporary tables surprise

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Global temporary tables surprise
Date: 2010-06-13 08:54:22
Message-ID: 4C149CBE.5080609@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-novice
I created a global temporary table, using the following syntax:

[mgogala(at)medo tmp]$ psql scott
Timing is on.
psql (8.4.4)
Type "help" for help.

scott=# create global temporary table t_emp
scott-# on commit preserve rows
scott-# as select * from emp;
SELECT
Time: 127.086 ms
scott=# commit;
WARNING:  there is no transaction in progress
COMMIT
Time: 0.353 ms
scott=# select * from t_emp;
 empno | ename  |    job    | mgr  |      hiredate       | sal  | comm | 
deptno
-------+--------+-----------+------+---------------------+------+------+--------
  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800 |      
|     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600 |  300 
|     30
  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250 |  500 
|     30
  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975 |      
|     20
  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 
|     30
  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850 |      
|     30
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450 |      
|     10
  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000 |      
|     20
  7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000 |      
|     10
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500 |    0 
|     30
  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100 |      
|     20
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950 |      
|     30
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000 |      
|     20
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300 |      
|     10
(14 rows)

Time: 0.595 ms
scott=# \q

So far, everything is kosher and expected. Now, I re-entered the psql
and got the surprise of my life:

[mgogala(at)medo tmp]$ psql scott
Timing is on.
psql (8.4.4)
Type "help" for help.

scott=# select * from t_emp;
ERROR:  relation "t_emp" does not exist
LINE 1: select * from t_emp;
                      ^
scott=#


Whaddaf...? I did the wrong thing, namely went to the documentation and, 
sure enough, there it was:
http://www.postgresql.org/docs/8.4/static/sql-createtable.html
*************************************************************************************************************
Parameters

TEMPORARY or TEMP

    If specified, the table is created as a temporary table. Temporary 
tables are automatically dropped at the end of a session, or optionally 
at the end of the current transaction (see ON COMMIT below). Existing 
permanent tables with the same name are not visible to the current 
session while the temporary table exists, unless they are referenced 
with schema-qualified names. Any indexes created on a temporary table 
are automatically temporary as well.

    Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. 
This makes no difference in PostgreSQL, but see Compatibility.
*************************************************************************************************************

So, Postgresql will simply ignore "GLOBAL" or "LOCAL" and will create a 
local temporary table anyway? Why is that? Don't get me wrong, local 
temporary tables are a great replacement for cursors, but global 
temporary tables have their uses too. Is there any hope that we will 
have global temporary tables in the foreseeable future?  Another popular 
variety of databases supports global temporary tables but not local 
temporary tables. It would be very nice to have a standard terminology, 
wouldn't it?


-- 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com 


Responses

pgsql-novice by date

Next:From: Devrim GUNDUZDate: 2010-06-13 09:24:15
Subject: Re: Global temporary tables surprise
Previous:From: Jean-Yves F. BarbierDate: 2010-06-12 20:56:34
Subject: layout question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group