Global temporary tables surprise - Mailing list pgsql-novice

I created a global temporary table, using the following syntax:

[mgogala@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@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