Global Temporary Table in PostgreSQL

One of the common problems while migrating from Oracle to PostgreSQL is the need of Global Temporary Tables in PostgreSQL (GTT). There is no PostgreSQL equivalent for global temporary tables. Due to this reason, I have originally started the developed of an extension called : PGTT, to reduce the overall efforts involved while migrating from Oracle to PostgreSQL. In this article, I am going to discuss about the concept of Global temporary tables in PostgreSQL and how this extension : pgtt works followed by its limitations.

Concept of a Global Temporary Table (GTT)

The rows registered into a global temporary table are private to the session that inserts them, which means that they are only visible to that session but Postgres Global Temporary Tablesnot any other sessions. These rows can be preserved for the session time or only for the current transaction. This is defined at temporary table creation time using the clauses - ON COMMIT PRESERVE ROWS or ON COMMIT DELETE ROWS. If rows are removed at commit or session close time, the Global Temporary table persists among the sessions or DBMS shutdown. In this way, it is like any other standard table, just that it resides in the temporary tablespace and can be accessed by all sessions individually. You can see a short description of the Oracle implementation here

PostgreSQL has the concept of Local Temporary Table but not a Global temporary table. The temporary table must be created by each session that uses it and the lifetime of the rows are either commit or session based exactly like it is with the GTT. PostgreSQL has also an additional clause ON COMMIT DROP that automatically drops the temporary table at the end of a transaction. The temporary table is always dropped when the session ends. Oracle, since 18c, has something quite similar which is called Private Temporary Tables, see here for a short explanation. 

There is some work in progress to implement the Global Temporary Table in PostgreSQL core, see [1], [2] and [3].

How to deal with GTT in a migration ?

While waiting for a PostgreSQL core implementation of Global Temporary Table, today you have two solutions.

  1. The first one is to rewrite your application or PL/SQL code to use regular local temporary tables instead. Which means that you will need to create the temporary table in each session or transaction that uses it. Depending on the application, this may be tedious.
  2. The second solution is to use the PGTT extension. This extension emulates the Oracle implementation of Global Temporary Table using underlying regular local temporary tables. Its use allows no modification of the application or stored procedures code.
How does the PGTT extension work?

When a Global Temporary Table is created, the PGTT extension creates an unlogged table which will serve as a "template" table. It will also register the table in a registration table with the characteristics of the Global temporary table. When your application or plpgsql code will access it for the first time, a dedicated local temporary table will automatically be created and all the following accesses on the Global Temporary table will be internally rerouted to the underlying private temporary table.

The content of the temporary table is relative to the current session or transaction. Even if the table is persistent, a session or transaction can not see rows written by another session.

Installation of the extension

This is an extension that uses the PGXS architecture so it requires that pg_config is installed and in your PATH environment variable.

wget https://github.com/darold/pgtt/archive/refs/tags/v2.5.tar.gz

tar xzf v2.5.tar.gz

cd pgtt-2.5

make sudo make install

If you have an RPM based Linux distribution, you can install a pre-compiled version of PGTT from the PGDG RPM repository using yum. Here is the GitHub repository for PGTT.

sudo yum install pgtt_13-2.4-1
Examples for using PGTT

In order to use the pgtt extension, it must be created in the database using the following command.

CREATE EXTENSION pgtt;

To use it in your current session, you must load the library using the following syntax.

LOAD 'pgtt';

Create a Global Temporary Table where rows will persist after the end of a transaction

CREATE GLOBAL TEMPORARY TABLE a_gtt_table (
id integer PRIMARY KEY,
lbl text NOT NULL, 
creation_date timestamp CHECK (creation_date > '2021-01-01')
) ON COMMIT PRESERVE ROWS;

As the GLOBAL keyword is obsolete PostgreSQL will generate a warning about the use of the deprecated keyword, you can simply ignore it. If you don't want to be annoyed by this warning you can use GLOBAL as a comment instead as following. When the GLOBAL keyword is used as a comment, the extension will detect the comment and act as if the GLOBAL keyword was used.

CREATE /*GLOBAL*/ TEMPORARY TABLE a_gtt_table (
id integer PRIMARY KEY,
lbl text NOT NULL,
creation_date timestamp CHECK (creation_date > '2021-01-01')
) ON COMMIT PRESERVE ROWS;

As you have noticed in the above syntax, we can use any constraint on the temporary table. Another advantage is that we can also create indexes on these tables.

CREATE INDEX ON a_gtt_table (creation_date);

The only restriction is that you can not use Foreign Keys and Partitioning with Global Temporary Tables.

pgttdb=# \d a_gtt_table
   Unlogged table "pgtt_schema.a_gtt_table"
    Column     |            Type             | Collation | Nullable | Default 
---------------+-----------------------------+-----------+----------+---------
 id            | integer                     |           | not null | 
 lbl           | text                        |           | not null | 
 creation_date | timestamp without time zone |           |          | 

Indexes:

    "a_gtt_table_pkey" PRIMARY KEY, btree (id)
    "a_gtt_table_creation_date_idx" btree (creation_date)

Check constraints:

    "a_gtt_table_creation_date_check" CHECK (creation_date > '2021-01-01 00:00:00'::timestamp without time zone)

Once the GTT table is created, you can use it like any other table in your application. Only the records inserted in the current transaction will be available. The only required action is to load the library at session start, this can be achieved by the application when the connection to the database is established.

pgttdb=# LOAD ‘pgtt’;
LOAD

pgttdb=# INSERT INTO a_gtt_table SELECT g.id, 'Label#'||g.id, current_timestamp FROM generate_series(1, 10000) g(id);
INSERT 0 10000

pgttdb=# SELECT count(*) FROM a_gtt_table;
 count 
-------
 10000
(1 row) pgttdb=# \d a_gtt_table Table "pg_temp_3.a_gtt_table"     Column     |            Type             | Collation | Nullable | Default  ---------------+-----------------------------+-----------+----------+--------- id            | integer                     |           | not null |  lbl           | text                        |           | not null |  creation_date | timestamp without time zone |           |          | 

Indexes:

    "a_gtt_table_pkey" PRIMARY KEY, btree (id)
    "a_gtt_table_creation_date_idx" btree (creation_date)

Check constraints:

    "a_gtt_table_creation_date_check" CHECK (creation_date > '2021-01-01 00:00:00'::timestamp without time zone)

As you can see in the above and following logs, the table now is a temporary table. Statistics can also be run on the table and it will be done on the underlying temporary table.

pgttdb=# ANALYZE a_gtt_table;
ANALYZE

contrib_regression=# SELECT * FROM pg_stats WHERE tablename = 'a_gtt_table';
-[ RECORD 1 ]-
schemaname             | pg_temp_3
tablename              | a_gtt_table
attname                | id
inherited              | f
null_frac              | 0
avg_width              | 4
n_distinct             | -1
most_common_vals       | 
most_common_freqs      | 
histogram_bounds       | {1,100,200,300,400,500,600,700,...,9400,9500,9600,9700,9800,9900,10000}
correlation            | 1
most_common_elems      | 
most_common_elem_freqs | 
elem_count_histogram   | 

-[ RECORD 2 ]
schemaname             | pg_temp_3
tablename              | a_gtt_table
attname                | lbl
inherited              | f
null_frac              | 0
avg_width              | 10
n_distinct             | -1
most_common_vals       | 
most_common_freqs      | 
histogram_bounds       | {Label#1,Label#1087,Label#1177,Label#1267,...,Label#9818,Label#9908,Label#9999}
correlation            | 0.81865406
most_common_elems      | 
most_common_elem_freqs | 
elem_count_histogram   | 

-[ RECORD 3 ]-
schemaname             | pg_temp_3
tablename              | a_gtt_table
attname                | creation_date
inherited              | f
null_frac              | 0
avg_width              | 8
n_distinct             | 1
most_common_vals       | {"2021-06-09 20:03:11.787924"}
most_common_freqs      | {1}
histogram_bounds       | 
correlation            | 1
most_common_elems      | 
most_common_elem_freqs | 
elem_count_histogram   | 
Performance

The performances of this extension has always proved great. The overhead compared to local temporary tables is only around 1% or lesser.

Limitations

The main interest of Global Temporary Tables versus Local Temporary Tables is to reduce the bloat in the system catalog when your application will create tons of temporary tables every day. In this case, the PGTT extension is not helpful to reduce the catalog bloat. Even if the table is defined only once, there is a temporary table created each time it is used in a session. To avoid this bloat, another version of the extension called PGTT-RSL is available. In this version there is no system catalog bloat because it is working on an unlogged table with Row Security Level rules to make rows visible by a single session only. The point is that it is slower than the version based on a temporary table. This extension will be explained in another post.

Are you looking for support during Migrations to PostgreSQL or PostgreSQL Remote Database Services ?

Contact Us to know more about how we can help you.

Leave a Comment

Your email address will not be published.

Scroll to Top