Creating Temporary Tables
CREATE TEMPORARY TABLE
creates a table whose data persists only during the current session. Temporary table data is never visible to other sessions.
By default, all temporary table data is transaction-scoped—that is, the data is discarded when a COMMIT
statement ends the current transaction. If CREATE TEMPORARY TABLE
includes the parameter ON COMMIT PRESERVE ROWS
, table data is retained until the current session ends.
Temporary tables can be used to divide complex query processing into multiple steps. Typically, a reporting tool holds intermediate results while reports are generated—for example, the tool first gets a result set, then queries the result set, and so on.
When you create a temporary table, Vertica automatically generates a default projection for it. For more information, see Auto-Projections.
Global versus Local Tables
CREATE TEMPORARY TABLE
can create tables at two scopes, global and local, through the keywords GLOBAL
and LOCAL
, respectively:
Global temporary tables |
Vertica creates global temporary tables in the public schema. Definitions of these tables are visible to all sessions, and persist across sessions until they are explicitly dropped. Multiple users can access the table concurrently. Table data is session-scoped, so it is visible only to the session user, and is discarded when the session ends. |
Local temporary tables |
Vertica creates local temporary tables in the |
Data Retention
You can specify whether temporary table data is transaction- or session-scoped:
ON COMMIT DELETE ROWS
(default): Vertica automatically removes all table data when each transaction ends.ON COMMIT PRESERVE ROWS
: Vertica preserves table data across transactions in the current session. Vertica automatically truncates the table when the session ends.
If you create a temporary table with ON COMMIT PRESERVE ROWS
, you cannot add projections for that table if it contains data. You must first remove all data from that table with TRUNCATE TABLE
.
You can create projections for temporary tables created with ON COMMIT DELETE ROWS
, whether populated with data or not. However, CREATE PROJECTION
ends any transaction where you might have added data, so projections are always empty.
ON COMMIT DELETE ROWS
By default, Vertica removes all data from a temporary table, whether global or local, when the current transaction ends.
For example:
=> CREATE TEMPORARY TABLE tempDelete (a int, b int); CREATE TABLE => INSERT INTO tempDelete VALUES(1,2); OUTPUT -------- 1 (1 row) => SELECT * FROM tempDelete; a | b ---+--- 1 | 2 (1 row) => COMMIT; COMMIT => SELECT * FROM tempDelete; a | b ---+--- (0 rows)
If desired, you can use DELETE
within the same transaction multiple times, in order to refresh table data repeatedly.
ON COMMIT PRESERVE ROWS
You can specify that a temporary table retain data across transactions in the current session, by defining the table with the keywords ON COMMIT PRESERVE ROWS
. Vertica automatically removes all data from the table only when the current session ends.
For example:
=> CREATE TEMPORARY TABLE tempPreserve (a int, b int) ON COMMIT PRESERVE ROWS;
CREATE TABLE
=> INSERT INTO tempPreserve VALUES (1,2); OUTPUT -------- 1 (1 row) => COMMIT; COMMIT => SELECT * FROM tempPreserve; a | b ---+--- 1 | 2 (1 row) => INSERT INTO tempPreserve VALUES (3,4); OUTPUT -------- 1 (1 row) => COMMIT; COMMIT => SELECT * FROM tempPreserve; a | b ---+--- 1 | 2 3 | 4 (2 rows)
Eon Restrictions
The following Eon Mode restrictions apply to temporary tables:
- K‑safety of temporary tables is always set to 0, regardless of system K‑safety. If a
CREATE TEMPORARY TABLE
statement sets k‑num greater than 0, Vertica returns an warning. -
If subscriptions to the current session change, temporary tables in that session becomes inaccessible. Causes for session subscription changes include:
- A node left the list of participating nodes.
- A new node appeared in the list of participating nodes.
- An active node changed for one or more shards.
-
A mergeout operation in the same session that is triggered by a user explicitly invoking
DO_TM_TASK('mergeout')
, or changing a column data type withALTER TABLE…ALTER COLUMN
.Background mergeout operations have no effect on session subscriptions.