Thread: Global temporary tables surprise
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
"global" keyword has no effect in PostgreSQL. See the compatibility section in the doc that you linked. -- Devrim GÜNDÜZ PostgreSQL DBA @ Akinon/Markafoni, Red Hat Certified Engineer devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz 13.Haz.2010 tarihinde 11:54 saatinde, Mladen Gogala <mladen.gogala@vmsinfo.com > şunları yazdı: > 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 > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice
On Sun, Jun 13, 2010 at 04:54:22AM -0400, Mladen Gogala wrote: > 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. Because it hasn't been implemented yet. > 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? Yes, in fact there has been talk about working on it for 9.1. http://rhaas.blogspot.com/2010/05/global-temporary-and-unlogged-tables.html -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com