Skip to main content
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. A temp table can also be scoped to a transaction with ON COMMIT DELETE ROWS or ON COMMIT DROP. Refer to Control what happens at commit. 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 feature is in preview and subject to change. To share feedback and/or issues, contact Support.
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, unless they are created with ON COMMIT DELETE ROWS (all rows are removed whenever a transaction commits) or ON COMMIT DROP (the table is dropped when the transaction that created it commits).
  • Temp tables can reference persistent tables, but persistent tables cannot reference temp tables.
  • Temp tables cannot be converted to persistent tables.
  • Temp tables can be created and used within a PL/pgSQL stored procedure body when the sql.procedures.plpgsql.late_binding.enabled cluster setting is enabled. A temp table created in a procedure is added to the session’s temporary schema. Refer to Create temporary tables.
  • For PostgreSQL compatibility, CockroachDB supports the clauses ON COMMIT PRESERVE ROWS (the default behavior), ON COMMIT DELETE ROWS, and ON COMMIT DROP at the end of CREATE TEMP TABLE statements. Refer to Control what happens at commit.
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.

Control what happens at commit

By default, a temp table and its rows persist until the end of the session. To scope a temp table or its rows to a transaction, add one of the following clauses to the end of the CREATE TEMP TABLE or CREATE TEMP TABLE AS statement:
  • ON COMMIT PRESERVE ROWS: The default behavior. The table and its rows persist when a transaction commits.
  • ON COMMIT DELETE ROWS: All rows are removed from the table and its indexes whenever a transaction in the session commits, including transactions that did not access the table. This also applies to implicit transactions. An INSERT run as a standalone statement commits immediately and removes its own rows, so a SELECT run as the next statement returns no rows. To keep the rows between statements, run them in the same explicit transaction. Rows are removed without firing row-level DELETE triggers, and sequences owned by the table are not reset.
  • ON COMMIT DROP: The table is dropped when the transaction that created it commits. Objects that depend on the table, such as views and the sequences owned by the table, are dropped with it, as with DROP TABLE ... CASCADE. Outside of a transaction block, the table is dropped as soon as the CREATE TEMP TABLE statement commits, and CockroachDB returns a NOTICE.
The ON COMMIT clause is accepted only on temp tables. On a persistent table, the statement fails with error code 42P16. A rollback runs no ON COMMIT action. ROLLBACK TO SAVEPOINT does not cancel a pending action, unless it rolls back the CREATE TEMP TABLE statement itself. For examples, refer to Clear a temp table at each commit and Drop a temp table at commit.

Known limitations

  • When the autocommit_before_ddl session variable is on, CREATE TEMP TABLE ... ON COMMIT DROP does not commit the open transaction. CREATE TEMP TABLE ... ON COMMIT DELETE ROWS and any other schema change in the same transaction, such as CREATE INDEX on the new table, still commit the transaction before they run. This drops an ON COMMIT DROP table before later statements in the transaction can use it. To run other schema changes in the same transaction, set autocommit_before_ddl to off for the session.
  • Under READ COMMITTED or REPEATABLE READ isolation, CREATE TEMP TABLE ... ON COMMIT DROP must be the first statement in the transaction. The transaction is then upgraded to SERIALIZABLE. After other statements, it fails with error code 0A000.
  • A foreign key that references an ON COMMIT DELETE ROWS table from a table that is not also created with ON COMMIT DELETE ROWS is rejected with error code 0A000 when the transaction that adds the foreign key commits, and the transaction is rolled back.
  • When the copy_from_atomic_enabled session variable is off, a COPY statement still runs in a single transaction while the session has a temp table with a pending ON COMMIT DELETE ROWS or ON COMMIT DROP action, and CockroachDB returns a NOTICE.

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 common table expressions (CTEs).
  • 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 schema change GC jobs and degrade overall cluster performance.
  • Prefer CTEs for intermediate results where possible. If you do use temp tables instead of CTEs, consider reusing a small set of temp tables with TRUNCATE 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, 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

For intermediate results, consider using common table expressions (CTEs) instead of temp tables. For more information, see Performance considerations.
To use temp tables, you need to set experimental_enable_temp_tables to on:

Create a temp table

You can use SHOW CREATE to view temp tables:
To show the newly created pg_temp schema, use SHOW SCHEMAS:

Create a temp table that references another temp table

To create another temp table that references users:

Show all temp tables in a session

To show all temp tables in a session’s temporary schema, use SHOW TABLES FROM pg_temp:
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 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:

Clear a temp table at each commit

The following statements create a temp table whose rows are removed whenever a transaction in the session commits. Create the table outside of a transaction. With autocommit_before_ddl enabled, creating the table inside BEGIN ... COMMIT would commit the transaction first. Refer to Known limitations.
Insert rows in a transaction. Within the transaction, the rows are visible:
Commit the transaction. The commit removes all rows from the table:

Drop a temp table at commit

The following statements create a temp table that is dropped when the transaction that created it commits:
Within the transaction, the table can be used like any other temp table:
Commit the transaction. The commit drops the table:

Cancel a session

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

See also