Leveraging Local Temporary Table for Global Temporary Table in EDB Postgres 9.6

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:

  1. CREATE a Local Temporary Table using the global_temp_backend definition if it does not exist in session.
  2. Re-route the insert to a Local Temporary Table.

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;

Tip:: PPAS 9.4 and Global Temporary Table

Customers who moved/migrated their database from Oracle to PPAS frequently ask for Global Temporary Table in PPAS.

Currently, PPAS doesn’t support Global Temporary tables. However, there is a way user can achieve this functionality in PPAS.

Before we continue with the implementation, lets first understand characteristics of Global Temporary Table. Following are the important characteristics of Global Temporary Table.
1. 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 data inserted by a session can only be accessed by that session.

Based on the above characteristics of Global Temporary Table AKA GTT, we can define similar kind of work by using the following method:
1. Create UNLOGGED TABLE in PPAS, which activity won’t be logged.
2. Create Row Level Security in such a way that session should be able to see their information (based on PID).
3. Create a process which can cleanup data from GTT based on pids which are not active in database.

Lets see how we can implement it in Adavanced Server.

1. Create an UNLOGGED table with all columns required and extra column of Pid.

CREATE UNLOGGED TABLE test_global_temporary_table(id numeric, col text, pid bigint default pg_backend_pid());

2. Create a function to restrict the visibility of data.

CREATE OR REPLACE FUNCTION verify_pid_context (
    p_schema       TEXT,
    p_object       TEXT
)
RETURN VARCHAR2
IS
DECLARE
   predicate TEXT;
BEGIN
    IF ( current_setting('is_superuser') = 'on')
    THEN
      predicate = 'true';
    ELSE
      predicate := format('pid = %s',pg_backend_pid());
    END IF;
    RETURN predicate;
END;

3. Apply the security policy based on above function.

DECLARE
  v_object_schema VARCHAR2(30)   := 'public';
  v_object_name VARCHAR2(30)     := 'test_global_temporary_table';
  v_policy_name VARCHAR2(30)     := 'secure_by_pid';
  v_function_schema VARCHAR2(30) := 'public';
  v_policy_function VARCHAR2(30) := 'verify_pid_context';
  v_statement_types VARCHAR2(30) := 'INSERT,UPDATE,DELETE,SELECT';
  v_update_check BOOLEAN         := TRUE;
  v_enable BOOLEAN               := TRUE;
BEGIN
  DBMS_RLS.ADD_POLICY( v_object_schema,
                       v_object_name,
                       v_policy_name,
                       v_function_schema,
                       v_policy_function,
                       v_statement_types,
                       v_update_check,
                       v_enable
                     );
END;

4. Create UPDATABLE view which can hide pid column. All sessions will be using this view as GTT.

   CREATE OR REPLACE VIEW test_global_temporary AS SELECT id, col FROM test_global_temporary_table;

5. Create a backend job, which can cleanup Table based on stale/old sessions.
For job, user/developer can do following:
a. use superuser and execute DELETE command on table:
DELETE FROM test_global_temporary WHERE pid NOT in (SELECT pid FROM pg_stat_activity);
b. To Schedule above DELETE command, user can use one of the following:
i. Crontab
ii. Or PPAS DBMS_SCHEDULE Package.

6. GRANT ALL privileges to database user who can access Global Temporary Table.

    GRANT ALL on test_global_temporary TO testdbuser;
    GRANT ALL on test_global_temporary_table To testdbuser;

Now, lets try above implementation of Global Temporary Table.

Open two sessions as a normal user (testdbuser) as given below:

[vibhorkumar@localhost ~]$ psql -U testdbuser edb
psql.bin (9.4.4.9)
Type "help" for help.

edb=> 
edb=> select pg_backend_pid();
 pg_backend_pid 
----------------
          32722
(1 row)


edb=> select pg_backend_pid();
 pg_backend_pid 
----------------
          32729
(1 row)

Now from both session insert some records:
From first session:

edb=> INSERT INTO test_global_temporary VALUES(1,'FROM pid 32722');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(2,'FROM pid 32722');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(3,'FROM pid 32722');
INSERT 0 1

From Second session:

edb=> INSERT INTO test_global_temporary VALUES(1,'FROM pid 32729');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(2,'FROM pid 32729');
INSERT 0 1
edb=> INSERT INTO test_global_temporary VALUES(3,'FROM pid 32729');
INSERT 0 1

From First Session:

edb=> SELECT * FROM test_global_temporary;
 id |      col       
----+----------------
  1 | FROM pid 32722
  2 | FROM pid 32722
  3 | FROM pid 32722
(3 rows)

From Second Session:

edb=> SELECT * FROm test_global_temporary;
 id |      col       
----+----------------
  1 | FROM pid 32729
  2 | FROM pid 32729
  3 | FROM pid 32729
(3 rows)

which shows that unlogged table with right RLS policy and backend job, can be a potential solution for Global Temporary Tables.