Temporary tables exist, storing data in memory, for the duration of a client session. This means they are scoped to the connection that opened them, cannot be queried by other users, and are dropped once the connection has ended. They can also be dropped manually without removing the connection.

SingleStore DB does not write logs or take snapshots of temporary tables. Temporary tables are designed for temporary, intermediate computations and can only be created in rowstores. Since temporary tables are neither persisted nor replicated in SingleStore DB, they have high availability disabled. This means that if a node in a cluster goes down and a failover occurs, all the temporary tables on the cluster lose data. Whenever a query references a temporary table after a node failover, it returns an error. For example,

"Temporary table <table> is missing on leaf <host> due to failover. The table will need to be dropped and recreated."

Views cannot reference temporary tables because temporary tables only exist for the duration of a client session. Although SingleStore DB does not materialize views, views are available as a service for all clients, and so cannot depend on client session-specific temporary tables.

Unlike CREATE TABLE without the TEMPORARY option, CREATE TEMPORARY TABLE can be run on any aggregator, not just the master aggregator. Temporary tables are sharded tables, and can be modified and queried like any “permanent” table, including distributed joins.

Global Temporary Tables

Another type of temporary table is the global temporary table. Like temporary tables, these can only be created in rowstores, and are not persisted.

Unlike temporary tables, global temporary tables are shared across sessions, i.e. they exist beyond the duration of a session. They are never automatically dropped and must always be dropped manually. They can also be queried by other users since they are not session dependent.

Global temporary tables can be used as a temporary space for processing data that can be accessed from multiple connections, for example, performing ETL (Extract, Transform, and Load) operations on modern dynamic databases.

If failover occurs, global temporary tables lose data and enter an errored state; they need to be dropped and recreated. However, dropping a global or non-global temporary table does not drop its plancache from the disk and retains the cache if the table is recreated with the same schema.

The CREATE GLOBAL TEMPORARY TABLE command can be run only on the master aggregator. However, the DML commands for global temporary tables can be run from both the master aggregator and child aggregator.

Note: Both global temporary tables and non-global temporary tables cannot be altered.

Was this Article Helpful?

Thanks For Your Feedback!