Temporary Tables - CockroachDB

CockroachDB supports session-scoped temporary tables (also called “temp tables”). Unlike , temp tables can only be accessed from the session in which they were created, and they are dropped at the end of the session. To create a temp table, add TEMP/TEMPORARY to a or statement. For full syntax details, see the and pages. For example usage, see Examples.

By default, temp tables are disabled in CockroachDB. To enable temp tables, set the experimental_enable_temp_tables to on.

CockroachDB also supports and .

Details

Temp tables are automatically dropped at the end of the session.
A temp table can only be accessed from the session in which it was created.
Temp tables persist across transactions in the same session.
Temp tables can reference persistent tables, but persistent tables cannot reference temp tables.
Temp tables cannot be converted to persistent tables.
For PostgreSQL compatibility, CockroachDB supports the clause ON COMMIT PRESERVE ROWS at the end of CREATE TEMP TABLE statements. CockroachDB only supports session-scoped temp tables, and does not support the clauses ON COMMIT DELETE ROWS and ON COMMIT DROP, which are used to define transaction-scoped temp tables in PostgreSQL.

By default, every 30 minutes CockroachDB cleans up all temporary objects that are not tied to an active session. You can change how often the cleanup job runs with the sql.temp_object_cleaner.cleanup_interval .

Performance considerations

Temporary tables are not optimized for performance. They use the same underlying mechanisms as “regular” tables, and may be slower than expected compared to alternatives such as .
Avoid patterns that create and drop very large numbers of temp tables in rapid succession. Creating and dropping large numbers of temp tables can enqueue many and degrade overall cluster performance.
Prefer for intermediate results where possible. If you do use temp tables instead of CTEs, consider reusing a small set of temp tables with instead of repeatedly creating and dropping new ones. Always test both approaches for your workload.

Temporary schemas

Temp tables are not part of the public schema. Instead, when you create the first temp table for a session, CockroachDB generates a single temporary schema (pg_temp_<id) for all of the temp tables, , and in the current session for a database. In a session, you can reference the session’s temporary schema as pg_temp.

Because the statement defaults to the public schema (which doesn’t include temp tables), using SHOW TABLES without specifying a schema will not return any temp tables.

Examples

For intermediate results, consider using instead of temp tables. For more information, see Performance considerations.

To use temp tables, you need to set experimental_enable_temp_tables to on:

> SET experimental_enable_temp_tables=on;

Create a temp table

> CREATE TEMP TABLE users (
        id UUID,
        city STRING,
        name STRING,
        address STRING,
        CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
);

You can use to view temp tables:

> SHOW CREATE TABLE users;

  table_name |                create_statement
-------------+-------------------------------------------------
  users      | CREATE TEMP TABLE users (
             |     id UUID NOT NULL,
             |     city STRING NULL,
             |     name STRING NULL,
             |     address STRING NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     FAMILY "primary" (id, city, name, address)
             | )
(1 row)

To show the newly created pg_temp schema, use :

> SHOW SCHEMAS;

           schema_name
---------------------------------
  crdb_internal
  information_schema
  pg_catalog
  pg_extension
  pg_temp_1602087923187609000_1
  public
(6 rows)

Create a temp table that references another temp table

To create another temp table that references users:

> CREATE TEMP TABLE vehicles (
        id UUID NOT NULL,
        city STRING NOT NULL,
        type STRING,
        owner_id UUID,
        creation_time TIMESTAMP,
        CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
        CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES users(city, id)
);

> SHOW CREATE TABLE vehicles;

  table_name |                                     create_statement
-------------+--------------------------------------------------------------------------------------------
  vehicles   | CREATE TEMP TABLE vehicles (
             |     id UUID NOT NULL,
             |     city STRING NOT NULL,
             |     type STRING NULL,
             |     owner_id UUID NULL,
             |     creation_time TIMESTAMP NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES users(city, id),
             |     INDEX vehicles_auto_index_fk_city_ref_users (city ASC, owner_id ASC),
             |     FAMILY "primary" (id, city, type, owner_id, creation_time)
             | )
(1 row)

Show all temp tables in a session

To show all temp tables in a session’s temporary schema, use SHOW TABLES FROM pg_temp:

> SHOW TABLES FROM pg_temp;

           schema_name          | table_name | type  | estimated_row_count
--------------------------------+------------+-------+----------------------
  pg_temp_1602087923187609000_1 | users      | table |                   0
  pg_temp_1602087923187609000_1 | vehicles   | table |                   0
(2 rows)

You can also use the full name of the temporary schema in the SHOW statement (e.g., SHOW TABLES FROM pg_temp_1602087923187609000_1).

Show temp tables in `information_schema`

Although temp tables are not included in the public schema, metadata for temp tables is included in the and pg_catalog schemas. For example, the table includes information about all tables in all schemas in all databases, including temp tables:

> SELECT * FROM information_schema.tables WHERE table_schema='pg_temp_1602087923187609000_1';

   table_catalog |         table_schema          | table_name |   table_type    | is_insertable_into | version
-----------------+-------------------------------+------------+-----------------+--------------------+----------
  defaultdb      | pg_temp_1602087923187609000_1 | users      | LOCAL TEMPORARY | YES                |       2
  defaultdb      | pg_temp_1602087923187609000_1 | vehicles   | LOCAL TEMPORARY | YES                |       2
(2 rows)

Cancel a session

If you end the session, all temp tables are lost.

> SHOW session_id;

             session_id
------------------------------------
  15fd69f9831c1ed00000000000000001
(1 row)

> CANCEL SESSION '15fd69f9831c1ed00000000000000001';

ERROR: driver: bad connection
warning: connection lost!
opening new connection: all session settings will be lost

> SHOW CREATE TABLE users;

ERROR: relation "users" does not exist
SQLSTATE: 42P01

​Details

​Performance considerations

​Temporary schemas

​Examples

​Create a temp table

​Create a temp table that references another temp table

​Show all temp tables in a session

​Show temp tables in information_schema

​Cancel a session

​See also

Details

Performance considerations

Temporary schemas

Examples

Create a temp table

Create a temp table that references another temp table

Show all temp tables in a session

Show temp tables in `information_schema`

Cancel a session

See also