New in v20.1: CockroachDB supports session-scoped temporary tables (also called "temp tables"). Unlike persistent tables, 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 CREATE TABLE or CREATE TABLE AS statement. For full syntax details, see the CREATE TABLE and CREATE TABLE AS pages. For example usage, see Examples.
This is an experimental feature. The interface and output are subject to change. For details, see the tracking issue cockroachdb/cockroach#46260.
By default, temp tables are disabled in CockroachDB. To enable temp tables, set the experimental_enable_temp_tables session variable to on.
CockroachDB also supports temporary views and temporary sequences.
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 ROWSat the end ofCREATE TEMP TABLEstatements. CockroachDB only supports session-scoped temp tables, and does not support the clausesON COMMIT DELETE ROWSandON 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 cluster setting.
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, temporary views, and temporary sequences in the current session for a database. In a session, you can reference the session's temporary schema as pg_temp.
Because the SHOW TABLES 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
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 SHOW CREATE 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:
> SHOW SCHEMAS;
schema_name
---------------------------------
crdb_internal
information_schema
pg_catalog
pg_temp_1584540651942455000_1
public
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;
table_name
--------------
users
vehicles
(2 rows)
You can also use the full name of the temporary schema in the SHOW statement (e.g., SHOW TABLES FROM pg_temp_1584540651942455000_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 information_schema and pg_catalog schemas.
For example, the information_schema.tables 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_1584540651942455000_1';
table_catalog | table_schema | table_name | table_type | is_insertable_into | version
----------------+-------------------------------+------------+-----------------+--------------------+----------
defaultdb | pg_temp_1584540651942455000_1 | users | LOCAL TEMPORARY | YES | 1
defaultdb | pg_temp_1584540651942455000_1 | vehicles | LOCAL TEMPORARY | YES | 1
(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
See also
Was this helpful?
