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
   predicate TEXT;
    IF ( current_setting('is_superuser') = 'on')
      predicate = 'true';
      predicate := format('pid = %s',pg_backend_pid());
    END IF;
    RETURN predicate;

3. Apply the security policy based on above function.

  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;
  DBMS_RLS.ADD_POLICY( v_object_schema,

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

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 (
Type "help" for help.

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

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

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

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

From Second session:

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

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.

PostgreSQL/Postgres Plus advanced Server Process Details

Following are some of the important Process of PostgreSQL/Postgres Plus Advanced Server.

vibhore@ubuntu:~/edb/edb-postgres$ ps -eaf|grep postgre
1002      1080     1  0 06:07 ?        00:00:00 /opt/PostgresPlus/8.4AS/bin/edb-postgres -D /opt/PostgresPlus/8.4AS/data (Mandatory)
1002      1101  1080  0 06:07 ?        00:00:00 postgres: logger process   (Mandatory)
1002      1103  1080  0 06:07 ?        00:00:01 postgres: writer process                                               (Mandatory)
1002      1104  1080  0 06:07 ?        00:00:01 postgres: wal writer process (Mandatory)
1002      1105  1080  0 06:07 ?        00:00:00 postgres: autovacuum launcher process  (Optional if autovacuum is on)
1002      1106  1080  0 06:07 ?        00:00:00 postgres: stats collector process      (Mandatory)

Above is an snapshot of the processes which exist in PostgreSQL/Postgres Plus Advanced Server.
Postmaster: is a superior process among others its the process which keep monitor the other process and also spawns new process if user request comes to it. It is the process which is responsible for Host Based Authentication and User Based Authentication. This process also checks the consistency of database and accordingly does the recovery of PostgreSQL Instance.

Logger Process: This process is responsible for logging the details of activity from the startup to shutdown of the PostgreSQL Instance. Logging behavior of this process can be control by following catagories of parameters:
Category: Where to Log: Parameters:
1. log_destination (string): This parameter tells logger process about methods for logging server messages, including stderr, csvlog and syslog.
2. logging_collector (boolean): This parameter allows messages sent to stderr, and CSV-format log output, to be captured and redirected into log files.
3. log_directory (string): This parameter specifies directory of logfile.
4. log_filename (string): When logging_collector is enabled, this parameter sets the file names of the created log files. The value is treated as a strftime pattern, so %-escapes can be used to specify time-varying file names.
5. log_rotation_age (integer): This parameter determines the maximum lifetime of an individual log file.After this many minutes have elapsed, a new log file will be created. Set to zero to disable time-based creation of new log files. This parameter can only be set in the postgresql.conf file or on the server command line.
6. log_rotation_size (integer): this parameter determines the maximum size of an individual log file. After this many kilobytes have been emitted into a log file, a new log file will be created.
7. log_truncate_on_rotation (boolean): This parameter will cause PostgreSQL to truncate (overwrite), rather than append to, any existing log file of the same name. However, truncation will occur only when a new file is being opened due to time-based rotation, not during server startup or size-based rotation.
8. syslog_facility (enum): This parameter determines the syslog “facility” to be used. You can choose from LOCAL0, LOCAL1, LOCAL2, LOCAL3, LOCAL4, LOCAL5, LOCAL6, LOCAL7; the default is LOCAL0. See also the documentation of your system’s syslog daemon.
9. syslog_ident (string): This parameter determines the program name used to identify PostgreSQL messages in syslog logs. The default is postgres.
10. silent_mode (boolean): This parameter is set, the server will automatically run in background and disassociate from the controlling terminal. This parameter can only be set at server start.

Category: When to Log: Parameters:
Following are parameters which controls the message level:
1. client_min_messages (enum)
2. log_min_messages (enum)
3. log_error_verbosity (enum)
4. log_min_error_statement (enum)
5. log_min_duration_statement (integer)
Detail about the above parameter values can be found in following link:

Category: What to Log: Parameters:
1. Debug Parameters:
debug_print_parse (boolean)
debug_print_rewritten (boolean)
debug_print_plan (boolean)
These parameters enable various debugging output to be emitted. When set, they print the resulting parse tree, the query rewriter output, or the execution plan for each executed query.
2. debug_pretty_print (boolean): This indents the messages produced by debug_print_parse, debug_print_rewritten, or debug_print_plan. This results in more readable but much longer output than the “compact” format used when it is off.
3. log_checkpoints (boolean): This checkpoints to be logged in the server log. Some statistics about each checkpoint are included in the log messages, including the number of buffers written and the time spent writing them.
4. log_connections (boolean): This parameter causes each attempted connection to the server to be logged, as well as successful completion of client authentication.
5. log_disconnections (boolean): This outputs a line in the server log similar to log_connections but at session termination, and includes the duration of the session
6. log_duration (boolean): This parameter causes the duration of every completed statement to be logged.
7. log_lock_waits (boolean): It controls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock.
8. log_statement (enum): It controls which SQL statements are logged. Valid values are none, ddl, mod, and all. ddl logs all data definition statements, such as CREATE, ALTER, and DROP statements. mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type.
9. log_temp_files (integer): It controls logging of use of temporary files. Temporary files can be created for sorts, hashes, and temporary query results. A log entry is made for each temporary file when it is deleted.
10. log_timezone (string): It sets the time zone used for timestamps written in the log.Unlike timezone, this value is cluster-wide, so that all sessions will report timestamps consistently.

writer process: This is a BG (Back Ground) Writer process. This process writes dirty shared buffer to Disk on following events:
1. When Checkpoint occurs.
2. When Checkpoint timeout happens
3. When WAL Writter finishes the number segments mention in Checkpoint_segments.
4. When there is no space left in shared buffer for new Blocks.

Parameters which can help in controlling the activity of BG Writter activities if given below:
1. bgwriter_delay (integer): This parameter specifies delay between activity rounds for the background writer. In each round the writer issues writes for some number of dirty buffers (controllable by the following parameters). It then sleeps for bgwriter_delay milliseconds, and repeats.
2. bgwriter_lru_maxpages (integer): This parameter specifies max number of buffers will be written to disk in each round. Setting this to zero disables background writing (except for checkpoint activity).
3. bgwriter_lru_multiplier (floating point): This parameter use in estimating of the number of buffers that will be needed during the next round. Formula which use is given below:
(average recent need) * bgwriter_lru_multiplier
Dirty buffers are written until there are ((average recent need) * bgwriter_lru_multiplier) many clean, reusable buffers available.

wal writer process: This process writes all the transaction from WAL Buffer to WAL files in pg_xlog. Parameters which can control the WAL Writter is given below. It is similar to log writer in Oracle (which flush redo log buffer to redo logfile). Parameter which can be use to control the behavior of WAL Writer can be found in following link:

stats collector process:
The collector can count accesses to tables and indexes in both disk-block and individual-row terms. It also tracks the total number of rows in each table, and information about vacuum and analyze actions for each table. It can also count calls to user-defined functions and the total time spent in each one.
Information of paramter which can be use to control its activity can be find in following link:

autovacuum process:
Autovacuum is a process, which performs following activity:
1. To recover or reuse disk space occupied by updated or deleted rows.
2. To update data statistics used by the PostgreSQL query planner.
3. To protect against loss of very old data due to transaction ID wraparound.

Till 8.2, default value of this parameter is off. From 8.3 onwards, its value is on, which means now, its default process from 8.3 Onwards.