cancel
Showing results for 
Search instead for 
Did you mean: 

User Tip: Postgres Plus 9.4 and Global Temporary Tables

EDB Team Member

10/21/2015

 

EnterpriseDB (EDB) customers who moved/migrated their database from Oracle to EDB’s Postgres Plus Advanced Server (PPAS) frequently ask for Global Temporary Table in PPAS.

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

Before we continue with the implementation, let's first understand characteristics of Global Temporary Tables. Following are the important characteristics:

  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, where activity won’t be logged.
  2. Create Row Level Security in such a way that sessions should be able to see their information (based on PID).
  3. Create a process which can clean up data from GTT based on pids which are not active in the database.

Let’s see how we can implement it in Advanced Server.

1. Create an UNLOGGED table with all columns required and an 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 a UPDATABLE view which can hide the 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 clean up Table based on stale/old sessions.
For the job, the user/developer can do following:
a. use superuser and execute DELETE command on the table:

DELETE FROM test_global_temporary WHERE pid NOT in (SELECT pid FROM pg_stat_activity);

b. To Schedule above DELETE command, a 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, let’s try above implementation of Global Temporary Table.

Open two sessions as a regular 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 sessions insert some records:
From the 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 the 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.