Keep Temp Tables in New Sessions

[janice]

Hello!

Is there a way to keep temporary tables from being dropped after logging out?

If I create a temp table and then log off and back on again, the table is gone.

Code: Select all

dbadmin=> create local temporary table loc_temp (my_col int) on commit preserve rows;
CREATE TABLE
dbadmin=> insert into loc_temp values (10);
 OUTPUT 
--------
      1
(1 row)

dbadmin=> commit;
COMMIT
dbadmin=> select * from loc_temp;
 my_col 
--------
     10
(1 row)

dbadmin=> \q
[dbadmin@Vertica1 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> select * from loc_temp;
ERROR 4566:  Relation "loc_temp" does not exist

Is it possible too keep the temp table?

[scutter]

Hi Janice,

Use a GLOBAL temporary table - or leave off the LOCAL keyword.

"Global temporary table definitions persist in the database catalogs until they are removed explicitly through a DROP TABLE statement."

Local tempoary tables:

"Specifies that the table definition is visible only to the session in which it is created."

--Sharon

[nnani]

Hello Janice,

You can use Global temporary tables for achieving this.

Global temporary table definitions are accessible to all users and sessions, so that two (or more) users can access the same global table concurrently. However, whenever a user commits or rolls back a transaction, or ends the session, Vertica removes the global temporary table data automatically, so users see only data specific to their own transactions or session.
Global temporary table definitions persist in the database catalogs until they are removed explicitly through a DROP TABLE statement.

If you are expecting the data to be present after opening a new session, then this will no suffice your requirement.
Temporary tables cannot hold data after a session is closed. they are session specific.

Code: Select all

nnani=>   CREATE GLOBAL TEMPORARY GT_test
nnani->   (
nnani(>   test_id int
nnani(>   ,test_tab_name varchar(30)
nnani(>   ,test_date date
nnani(>   );
CREATE TABLE

nnani=> select * from GT_test;
 test_id | test_tab_name | test_date
---------+---------------+-----------
(0 rows)


nnani=> insert into GT_test values(2,'global_temp','2013-10-01');
 OUTPUT
--------
      1
(1 row)

nnani=> commit;
COMMIT
nnani=> select * from GT_test;
 test_id | test_tab_name | test_date
---------+---------------+-----------
(0 rows)

\q

Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

Bad terminal type: "xterm". Will assume vt100.
nnani=> select * from GT_test;
 test_id | test_tab_name | test_date
---------+---------------+-----------
(0 rows)
nnani=> \d+ GT_test
                                              List of Fields by Tables
       Schema       |  Table  |    Column     |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------------------+---------+---------------+-------------+------+---------+----------+-------------+-------------
 public| GT_test | test_id       | int         |    8 |         | f        | f           |
 public| GT_test | test_tab_name | varchar(30) |   30 |         | f        | f           |
 public| GT_test | test_date     | date        |    8 |         | f        | f           |
(3 rows)

Using Global temporary tables you can have table structure persistent but data will be lost everytime.
Workaround - You can think of Views

Hope this helps..

[janice]

Thanks, scutter and nnani! I missed that info. about global tables in the docs. It works!

[rajasekhart]

Hi Janice,

How you are using Temp Tables in your Project.

In which scenario , it is useful to you??

As we dont have stored procedures, how can we use them?

PS: Are you using them with External Stored Procedures??

Regards,
Raj!!

[janice]

Raj,

We are using the global temp tables to hold data "temporarily" as a staging area prior to actually loading the data into fact tables. We have a bunch of processes loading data from different sources. These processes can all use the same global temp table and it's nice that the tables automatically clean up the temp data once the users log off.