PolarDB for PostgreSQL(Compatible with Oracle) supports global temporary tables and native PostgreSQL local temporary tables.

Syntax

CREATE GLOBAL TEMPORARY|TEMP TABLE table-name
    { column-definition [ , column-definition ] * }
[ ON COMMIT {DELETE | PRESERVE} ROWS ]
  • The ON COMMIT DELETE ROWS clause is used to delete data from temporary tables after the current transaction is committed.
  • The ON COMMIT PRESERVE ROWS clause is used to retain data in global temporary tables after the current transaction is committed.
  • The ON COMMIT DROP clause is not supported.
  • By default, the ON COMMIT DELETE ROWS clause is used if you do not use the ON COMMIT clause.

Description

  • All database sessions share the table definition of a global temporary table. When a session creates a global temporary table, other sessions can also use this global temporary table.
  • The data stored in a global temporary table is private to the session that generates the data. Each session can only access its own data in the global temporary table.
  • When a session exits, the data and underlying storage in the global temporary table that the session uses are cleared.
  • You can join a global temporary table to other tables, create indexes on a global temporary table, and scan indexes on a global temporary table. The current version supports only B-tree indexes. The table-level and column-level statistics of the global temporary table is also private to the session that generates the data used in statistics. This optimizes the query plan of a query in the global temporary table.
  • Global temporary tables support manual vacuum and analyze operations to clear junk data and collect statistics.

Examples

create global temp table gtt1(a int primary key, b text); #Creates a global temporary table named gtt1. By default, the global temporary table supports ON COMMIT DELETE ROWS. You can use ON COMMIT DELETE ROWS to delete all data from the global temporary table after the current transaction is committed. 
create global temporary table gtt2(a int primary key, b text) on commit delete rows; #Creates a global temporary table named gtt2 and specifies ON COMMIT DELETE ROWS to delete all data from the global temporary table after the current transaction is committed. 
create global temp table gtt3(a int primary key, b text) on commit PRESERVE rows; #Creates a global temporary table named gtt3 and specifies ON COMMIT PRESERVE ROWS to retain all data from the global temporary table after the current transaction is committed. 

Operations

PolarDB for Oracle provides a group of functions that can be used for global temporary tables.

  • polar_gtt_attached_pid is used to view the sessions that are using a global temporary table. You can combine this function with other functions during operations and maintenance.
  • polar_gtt_att_statistic is used to view the column-level statistics of a global temporary table.
  • polar_gtt_relstats is used to view the table-level statistics of a global temporary table.

These functions work as plug-ins. You must create the plug-ins before you use these functions.

create extension polar_gtt;

To delete a global temporary table, you must ensure that the global temporary table is used only in the current session.

Perform the following steps:
  1. Use polar_gtt_attached_pid to query the sessions that are using the global temporary table.
  2. Use pg_backend_pid() to retrieve the process ID (pid) of the current session.
  3. Use pg_terminate_backend(pid) to terminate non-current sessions.
  4. Execute the DROP TABLE statement to delete the global temporary table.