How to Leverage Local Temporary Table for Global Temporary Table in EDB Postgres

In the past, I posted a blog on the concept of creating a Global Temporary Table (GTT) for migration from Oracle to EDB Postgres. In that blog I had shared the following characteristics a Global Temporary Table:

     1. The Global Temporary Table gives predefined structure for storing data.
     2. It’s an unlogged table which means any activity on this table will not be logged.    
     3. The data in a Global Temporary Table are private, such that session can only access data inserted by a session.

With above characteristics, there are two options available for GTT. Following are the two of those options:

     1. ON COMMIT PRESERVE ROWS
     2. ON COMMIT DELETE ROWS 
Option one can be implemented as mentioned in the Tip:: PPAS 9.4 and Global Temporary Table. However option two is not as easy to implement.

Users also have the option of implementing a Global Temporary Table using a Local Temporary Table (LTT). An LTT

(https://www.postgresql.org/docs/9.6/static/sql-createtable.html) supports both options and can be leveraged for a GTT.

To implement a Global Temporary Table in EDB Postgres, a user must have following objects in EDB Postgres:

      1. An UNLOGGED table structure that can help in creating a backend LTT;
      2. An automatic updatable VIEW with the name Global temporary table that will be used for the frontend SELECT/INSERT/DELETE/UPDATE; and
      3. A TRIGGER on view that will help in redirecting the INSERT on the view to the backend Local temporary table (LTT).
Based on the above, let's look at an example of how DBAs and Developers can create a Global Temporary Table in EDB Postgres.

Below is a definition of a Global Temporary Table:

CREATE GLOBAL TEMPORARY TABLE global_temp (
  ts       TIMESTAMP,
  action   CHAR(100),
  state    CHAR(50)
)
ON COMMIT DELETE ROWS;

To create the above Global Temporary Table, we will first create a backend UNLOGGED table, global_temp_backend, as given below:

CREATE UNLOGGED TABLE global_temp_backend (
  ts       TIMESTAMP,
  action   CHAR(100),
  state    CHAR(50)
);

After creating the above UNLOGGED table, we can create a view, which users will use as a Global Temporary Table:

CREATE VIEW global_temp AS SELECT * FROM global_temp_backend;

Now, we can create an INSTEAD OF trigger on the view, which will do the following:

CREATE a Local Temporary Table using the global_temp_backend definition if it does not exist in session.
    1. Re-route the insert to a Local Temporary Table. 
    2. Below is an example of such a trigger:

CREATE OR REPLACE FUNCTION global_temp_insert()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$function$
BEGIN
    INSERT INTO global_local_temp_backend VALUES(NEW.*);
    RETURN NEW;
   /* create local temporary table if not exists */
    EXCEPTION WHEN undefined_table THEN
      CREATE TEMP TABLE global_local_temp_backend
      (LIKE global_temp_backend INCLUDING ALL )
       INHERITS (global_temp_backend)
       ON COMMIT DELETE ROWS;
      INSERT INTO global_local_temp_backend VALUES(NEW.*);
      RETURN NEW;
END;
$function$;
 
CREATE TRIGGER insert_trigger_gt INSTEAD OF INSERT ON global_temp
FOR EACH ROW EXECUTE PROCEDURE global_temp_insert();

Below are some snapshots:

edb=# CREATE UNLOGGED TABLE global_temp_backend (
edb(#   ts       TIMESTAMP,
edb(#   action   CHAR(100),
edb(#   state    CHAR(50)
edb(# );
CREATE TABLE
edb=# CREATE VIEW global_temp AS SELECT * FROM global_temp_backend;
CREATE VIEW
edb=# CREATE OR REPLACE FUNCTION global_temp_insert()
edb-# RETURNS TRIGGER
edb-# LANGUAGE plpgsql
edb-# AS
edb-# $function$
edb$# BEGIN
edb$#     INSERT INTO global_local_temp_backend VALUES(NEW.*);
edb$#     RETURN NEW;
edb$#     EXCEPTION WHEN undefined_table THEN
edb$#       CREATE TEMP TABLE global_local_temp_backend () INHERITS (global_temp_backend)
edb$#        ON COMMIT DELETE ROWS;
edb$#       INSERT INTO global_local_temp_backend VALUES(NEW.*);
edb$#       RETURN NEW;
edb$# END;
edb$# $function$;
CREATE FUNCTION
edb=# CREATE TRIGGER insert_trigger_gt INSTEAD OF INSERT ON global_temp
edb$# FOR EACH ROW EXECUTE PROCEDURE global_temp_insert();
CREATE TRIGGER

Let's insert some records into the Global Temporary Table and verify how it works:

edb=# BEGIN;
BEGIN
edb=# INSERT INTO global_temp VALUES (current_timestamp, 'node-1-request sended.', 'OK');
NOTICE:  merging column "ts" with inherited definition
NOTICE:  merging column "action" with inherited definition
NOTICE:  merging column "state" with inherited definition
INSERT 0 1
edb=# INSERT INTO global_temp VALUES (current_timestamp, 'node-2-request sended.', 'OK');
INSERT 0 1
edb=# INSERT INTO global_temp VALUES (current_timestamp, 'node-1-answer received.', 'Failed');
INSERT 0 1
edb=# INSERT INTO global_temp VALUES (current_timestamp, 'node-2-answer received.', 'OK');
INSERT 0 1
edb=# SELECT * FROM global_temp WHERE state = 'OK';
            ts             |                                                action                                                |                       state                       
---------------------------+------------------------------------------------------------------------------------------------------+----------------------------------------------------
 03-JUL-17 11:34:44.074603 | node-1-request sended.                                                                               | OK                                               
 03-JUL-17 11:34:44.079148 | node-2-request sended.                                                                               | OK                                               
 03-JUL-17 11:34:44.081194 | node-2-answer received.                                                                              | OK                                               
(3 rows)
edb=#
edb=# COMMIT;
COMMIT
edb=# SELECT * FROM global_temp WHERE state = 'OK';
 ts | action | state
----+--------+-------
(0 rows)

The above result was expected, since as per GTT definition rows will be deleted after commit.

If DBAs and Developers want to create a Global Temporary Table with , then they can modify the above trigger definition and include the following:

      CREATE TEMP TABLE global_local_temp_backend
      (LIKE global_temp_backend INCLUDING ALL )
       INHERITS (global_temp_backend)
       ON COMMIT PRESERVE ROWS;