Create and manage global temporary tables for PostgreSQL - PolarDB

PolarDB for PostgreSQL (Compatible with Oracle) supports global temporary tables and native PostgreSQL local temporary tables. Unlike native PostgreSQL temporary tables—where each session must create its own copy—a global temporary table has a shared definition that all sessions can use, while the data each session writes remains private to that session.

How it works

Global temporary tables have a split identity: the table definition is shared, but the data is not.

Aspect	Behavior
Table definition	Shared across all sessions. When one session creates a global temporary table, other sessions can use it immediately.
Data	Private to each session. A session can only read and write its own data.
Session cleanup	When a session exits, its data and underlying storage are cleared automatically.
Statistics	Table-level and column-level statistics are private to each session, so the query planner generates accurate plans for each session's workload.

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

Supported operations include JOIN with other tables, B-tree index creation, and index scans. Only B-tree indexes are supported.

Manual VACUUM and ANALYZE operations are supported to remove dead tuples and collect statistics.

Syntax

CREATE GLOBAL TEMPORARY|TEMP TABLE table-name
    { column-definition [ , column-definition ] * }
[ ON COMMIT {DELETE | PRESERVE} ROWS ]

ON COMMIT behavior

The ON COMMIT clause controls what happens to session data when a transaction ends.

ON COMMIT DELETE ROWS (default)

Creates a transaction-scoped temporary table. Data is deleted at the end of each transaction. If you omit the ON COMMIT clause, this is the default behavior.

ON COMMIT PRESERVE ROWS

Creates a session-scoped temporary table. Data persists across transactions within the same session and is cleared when the session ends.

ON COMMIT DROP is not supported.

Examples

Create a global temporary table with the default ON COMMIT DELETE ROWS behavior:

create global temp table gtt1(a int primary key, b text);

Explicitly specify ON COMMIT DELETE ROWS:

create global temporary table gtt2(a int primary key, b text) on commit delete rows;

Use ON COMMIT PRESERVE ROWS to retain data across transactions within a session:

create global temp table gtt3(a int primary key, b text) on commit PRESERVE rows;

Utility functions

These functions are packaged as an extension. Install it before use:

create extension polar_gtt;

Function	Description
`polar_gtt_attached_pid`	Returns the sessions currently using a global temporary table. Use this during operations and maintenance.
`polar_gtt_att_statistic`	Returns column-level statistics for a global temporary table.
`polar_gtt_relstats`	Returns table-level statistics for a global temporary table.

Drop a global temporary table

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

To drop a global temporary table:

Run polar_gtt_attached_pid to list all sessions using the table.
Run pg_backend_pid() to get the process ID (PID) of the current session.
For each session that is not the current session, run pg_terminate_backend(pid) to terminate it.
Run DROP TABLE to drop the global temporary table.