PostgreSQL Global Temporary Table v2.0 - Mailing list pgsql-announce

Grenoble - April 19th, 2020

## 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 C 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';

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. If your Oracle code use the
"SESSION" schema qualifier, you can relocate the extension to use
this schema, see documentation for more information.

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 ALL
        ) 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.

PostgreSQL temporary table clause `ON COMMIT DROP` is not supported by
the extension, GTT are persistent over transactions. If the clause is
used an error will be raised.

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.


===== Links & Credits =====

pgtt is an open project. Any contribution to build a better tool is
welcome. You just have to send your ideas, features requests or patches
using the GitHub tools or directly to gilles@darold.net.

Links :

* Download:  https://github.com/darold/pgtt/releases/
* Support: use GitHub report tool at https://github.com/darold/pgtt/issues
  or contact gilles@darold.net.

--------------

**About pgtt** :

Docs & Download at https://github.com/darold/pgtt/

--
Gilles Darold
http://www.darold.net/