PostgreSQL Global Temporary Tables
Description
pgtt is a PostgreSQL extension to create, manage and use Oracle-style
Global Temporary Tables and the others RDBMS.
The objective of this extension it to propose an extension to provide
the Global Temporary Table feature waiting for an in core
implementation. The main interest of this extension is to mimic the
Oracle behavior with GTT when you can not or don't want to rewrite the
application code when migrating to PostgreSQL. In all other case best
is to rewrite the code to use standard PostgreSQL temporary tables.
This version of the GTT extension use a regular unlogged table as
"template" table and an internal rerouting to a temporary table. See
documentation about "How the extension really works" for more details.
Use of the extension
In all database where you want to use Global Temporary Tables you
will have to create the extension using:
CREATE EXTENSION pgtt;
As a superuser you can load the extension using:
LOAD 'pgtt';
non-superuser must load the library using the plugins/ directory
as follow:
LOAD '$libdir/plugins/pgtt';
Take care to follow installation instruction above to create the
symlink from the plugins/ directory to the extension library file.
The pgtt extension use a dedicated schema to store related objects,
by default: pgtt_schema. The extension take care that this schema
is always at end of the search_path.
The pgtt schema is automatically added to the search_path when you
load the extension and if you change the search_path later.
To create a GTT table named "test_table" use the following statement:
CREATE GLOBAL TEMPORARY TABLE test_gtt_table (
id integer,
lbl text
) ON COMMIT { PRESERVE | DELETE } ROWS;
The GLOBAL keyword is obsolete but can be used safely, the only thing
is that it will generate a warning:
WARNING: GLOBAL is deprecated in temporary table creation
If you don't want to be annoyed by this warning message you can use
it like a comment instead:
CREATE /*GLOBAL*/ TEMPORARY TABLE test_gtt_table (
LIKE other_table LIKE
INCLUDING DEFAULTS
INCLUDING CONSTRAINTS
INCLUDING INDEXES
) ON COMMIT { PRESERVE | DELETE } ROWS;
the extension will detect the GLOBAL keyword.
As you can see in the example above the LIKE clause is supported,
as well as the AS clause WITH DATA or WITH NO DATA (default):
CREATE /*GLOBAL*/ TEMPORARY TABLE test_gtt_table
AS SELECT * FROM source_table WITH DATA;
In case of WITH DATA, the extension will fill the GTT with data
returned from the SELECT statement for the current session only.
Temporary table rows are deleted or preserved at transactions commit
following the clause:
ON COMMIT { PRESERVE | DELETE } ROWS
To drop a Global Temporary Table you just proceed as for a normal
table:
DROP TABLE test_gtt_table;
.
You can create indexes on the global temporary table:
CREATE INDEX ON test_gtt_table (id);
just like with any other tables.
See README.md for more information about the extension