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:
- Global Temporary Table gives predefined structure for storing data.
- It’s an unlogged table which means any activity on this table will not be logged.
- 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:
- Create UNLOGGED TABLE in PPAS, where activity won’t be logged.
- Create Row Level Security in such a way that sessions should be able to see their information (based on PID).
- 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 extra column of Pid.
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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 the pid column. All sessions will be using this view as GTT.
|
1 |
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 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.
|
1 2 |
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 normal user (testdbuser) as given below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[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 first session:
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 6 7 |
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:
|
1 2 3 4 5 6 7 |
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.
Vibhor Kumar is a principal system engineer at EnterpriseDB.