Version 2.8

Version 2.8 - Jun 02 2022

This is a maintenance release to add support to PostgreSQL 15 and fix an error when trying to drop a regular table.

  - Add support to PostgreSQL 15.
  - Fix impossibility to drop a regular table when the extension is loaded.
    Thanks to basildba for the report.

Version 2.7

Version 2.7 - Nov 23 2021

This is a maintenance release to fix an issue with parallelism and improve performances.

• Prevent code to be executed in parallel processes. Thanks to Dmitry Ukolov for the report.
• Improve performances by not looking for an existing GTT table if the table is a temporary table or part of the pg_catalog.
• Update ChangeLog to acknowledge patch on PG14 support to Dmitry Ukolov.
• Update copyright year.

Version 2.6

Version 2.6 - Sep 22 2021

This is a maintenance release to add support for upcoming PostgreSQL 14.

• Add support to PostgreSQL 14. Thanks to Devrim Gunduz for the report and Dmitry Ukolov for the patch.
• Remove support to PG 9.5 which obviously was not working. Minimal PostgreSQL version for this extension is 9.6.
• Fix documentation menu.
• Fix creation of GTT when there is a CHECK constraint with string constant.

Version 2.5

Version 2.5 - Jun 08 2021

This is a maintenance release to hot fix port on PostgreSQL 9.6.

• Fix port to PostgreSQL 9.6. Thanks to Devrim Gunduz for the report.

Version 2.4

This version allow use of the extension by non superuser and especially
the creation and maintenance of GTT. It also allow compatibility with
PostgreSQL v14.

Here is the full list of changes:

• Fix FailedAssertion "flags & HASH_STRINGS" with PG14. Thanks to MigOps for the patch.
• Check for minimum pg version in the C code instead of Makefile. Thanks to MigOps for the patch.
• Fixed compiling for PostgreSQL 14. Thanks to Dmitry Ukolov for the patch.
• Fix documentation about privilege to set on pgtt_schema for a non superuser role.
• Allow creation and maintenance of Global Temporary Tables by non superuser. This require that the user can use schema pgtt_schema and can write to table pg_schema.pg_global_temp_tables.
• The library can now be loaded by the user using LOAD '$libdir/plugins/pgtt.so'; Thanks to Dmitry Ukolov for the feature request.
• Fix two crashes when --enable-cassert is used. Thanks to hanson69 for the report.
• Fix comment and index on PGTT table. Thanks to Dmitry Ukolov for the report.
• Fix unexpected error "attempt to create referential integrity constraint on global temporary table" when creating a regular table and fix detection of FK and throw an error on create global temporary table statement. Thanks to Dmitry Ukolov for the report.
• Fix impossibility to recreate GTT if it was dropped in another session. Thanks to Dmitry Ukolov for the report.
• Remove useless extension's downgrade files. Thanks to MigOps for the patch.

Version 2.3

This version fix the compatibility with PostgreSQL 10 and 11.

When use on PostgreSQL 10 and 11 the following was raised ERROR: unrecognized node type: 375

Add upgrade/downgrade SQL files.

Version 2.2

Version 2.2 - Nov 08 2020

This release is a port of the extension for PostgreSQL v12 and v13.
Works now on all PostgreSQL version from v9.5 to current. It also
fixes automatic creation of the underlying temporary table after a
rollback.

Other fixes:

• Fix regression test for all supported PG version.
• Replace call to \d in regression tests, they do not report the same information following PG version.
• Remove test about partitioning as it returns a failure for PG < 10.
• Update regression tests to avoid failure related to temp table id.
• Add regression test for error on rollback issue.

Version 2.1

Version 2.1 - May 11 2020

This is a maintenance release to complete the work on the extension
and fix some issues.

• Prevent use of foreign keys with GTT, not that PostgreSQL do not
  allow it but just to mimic the behavior of Oracle and other RDBMS
  like DB2, SQL Server and MySQL for example.
• Raise an error on an attempt to partition a Global Temporary Table.
  This is not supported, again not because PostgreSQL do not allow
  partition on temporary table but because other RDBMS like Oracle,
  DB2 and MySQL do not support it.
• Add support to comments, constraints and identity columns clauses
  when creating the GTT.

Other fixes:

• Add regression tests on partitioning and FK.
• Exclude regression.* files from git scope.
• Improve documentation and add information about constraints.
• Add documentation about unsupported FK and partition on GTT.
• Fix missing files for expected test results.
• Fix exclusion of .out and results directory.
• Update regression tests about changes on CREATE TABLE ... LIKE.
• Fix some typo in documentation and markdown titles.

Version 2.0

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