pgtt
Extension to add Global Temporary Tables feature to PostgreSQL
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pgtt | 4.4 | SIM | ISC | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 9110 | pgtt | No | Yes | No | Yes | No | No | - |
| Related | oracle_fdw orafce session_variable pg_statement_rollback pg_dbms_metadata pg_dbms_lock pg_dbms_job periods |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 4.4 | 1817161514 | pgtt | - |
| RPM | PGDG | 4.4 | 1817161514 | pgtt_$v | - |
| DEB | PGDG | 4.4 | 1817161514 | postgresql-$v-pgtt | - |
Build
You can build the DEB packages for pgtt using pig build:
pig build pkg pgtt # build DEB packages
Install
You can install pgtt directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pgtt; # Install for current active PG version
pig ext install -y pgtt -v 18 # PG 18
pig ext install -y pgtt -v 17 # PG 17
pig ext install -y pgtt -v 16 # PG 16
pig ext install -y pgtt -v 15 # PG 15
pig ext install -y pgtt -v 14 # PG 14
dnf install -y pgtt_18 # PG 18
dnf install -y pgtt_17 # PG 17
dnf install -y pgtt_16 # PG 16
dnf install -y pgtt_15 # PG 15
dnf install -y pgtt_14 # PG 14
apt install -y postgresql-18-pgtt # PG 18
apt install -y postgresql-17-pgtt # PG 17
apt install -y postgresql-16-pgtt # PG 16
apt install -y postgresql-15-pgtt # PG 15
apt install -y postgresql-14-pgtt # PG 14
Create Extension:
CREATE EXTENSION pgtt;
Usage
pgtt: Extension to add Global Temporary Tables feature to PostgreSQL
Creating a Global Temporary Table
CREATE EXTENSION pgtt;
-- ON COMMIT PRESERVE ROWS: data persists across transactions within a session
CREATE GLOBAL TEMPORARY TABLE test_gtt (
id integer,
lbl text
) ON COMMIT PRESERVE ROWS;
-- ON COMMIT DELETE ROWS: data is deleted at transaction commit
CREATE GLOBAL TEMPORARY TABLE session_data (
id integer,
value text
) ON COMMIT DELETE ROWS;
The GLOBAL keyword can also be used as a comment to avoid warnings:
CREATE /*GLOBAL*/ TEMPORARY TABLE test_gtt (
LIKE other_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES
) ON COMMIT PRESERVE ROWS;
CREATE AS Form
CREATE /*GLOBAL*/ TEMPORARY TABLE gtt_copy
AS SELECT * FROM source_table WITH DATA;
Using Global Temporary Tables
INSERT INTO test_gtt VALUES (1, 'one'), (2, 'two');
SELECT * FROM test_gtt; -- visible only in current session
Creating Indexes
CREATE INDEX ON test_gtt (id);
Constraints
All constraints except FOREIGN KEYS are supported:
CREATE GLOBAL TEMPORARY TABLE t2 (
c1 serial PRIMARY KEY,
c2 VARCHAR(50) UNIQUE NOT NULL,
c3 boolean DEFAULT false
);
Dropping
DROP TABLE test_gtt; -- can be dropped even while used by other sessions
Configuration
SET pgtt.enabled TO off; -- disable GTT rerouting
SET pgtt.enabled TO on; -- re-enable GTT rerouting
Key Behaviors
- GTT content is session-local; other sessions cannot see your data
- The table structure is persistent (visible to all users), but data is per-session
- Requires loading via
session_preload_libraries = 'pgtt' - Partitioning is not supported on GTTs
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.