Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 1.3 Oracle Compatible Configuration Parameters
Postgres Plus Advanced Server supports the development and execution of PostgreSQL and Oracle applications. There are a number of system behaviors that can be altered to act in a more PostgreSQL or in a more Oracle compliant manner. These are controlled by configuration parameters that can be found in the postgresql.conf file in the database cluster data directory. Changing the parameters in the postgresql.conf file changes the behavior over all databases in the cluster. More fine-grained adjustment of these parameters can be done by database, by user or group, or by session. These parameters are the following:
● edb_redwood_date – Controls whether or not a time component is stored in DATE columns. For Oracle compatible behavior, set edb_redwood_date to TRUE.
● edb_redwood_strings – Equates NULL to an empty string for purposes of string concatenation operations. For Oracle compatible behavior, set edb_redwood_strings to TRUE.
● edb_stmt_level_tx – Isolates automatic rollback of an aborted SQL command to statement level rollback only – the entire, current transaction is not automatically rolled back as is the case for default PostgreSQL behavior. For Oracle compatible behavior, set edb_stmt_level_tx to TRUE; however, use only when absolutely necessary. See Section 1.3.3.
● oracle_home – Point Postgres Plus Advanced Server to the correct Oracle installation directory. See Section 1.3.4.
When DATE appears as the data type of a column in the commands, it is translated to TIMESTAMP(0) at the time the table definition is stored in the data base if the configuration parameter edb_redwood_date is set to TRUE. Thus, a time component will also be stored in the column along with the date. This is consistent with Oracle’s DATE data type.
If edb_redwood_date is set to FALSE the column’s data type in a CREATE TABLE or ALTER TABLE command remains as a native PostgreSQL DATE data type and is stored as such in the database. The PostgreSQL DATE data type stores only the date without a time component in the column.
Regardless of the setting of edb_redwood_date, when DATE appears as a data type in any other context such as the data type of a variable in an SPL declaration section, or the data type of a formal parameter in an SPL procedure or SPL function, or the return type of an SPL function, it is always internally translated to a TIMESTAMP(0) and thus, can handle a time component if present.
See Section 3.2.4 for more information on date/time data types.
In Oracle, when a string is concatenated with a null variable or null column, the result is the original string; however, in PostgreSQL concatenation of a string with a null variable or null column gives a null result. If the edb_redwood_strings parameter is set to TRUE, the aforementioned concatenation operation results in the original string as done by Oracle. If edb_redwood_strings is set to FALSE, the native PostgreSQL behavior is maintained.
The following example illustrates the difference.
The sample application introduced in the next chapter contains a table of employees. This table has a column named comm that is null for most employees. The following query is run with edb_redwood_string set to FALSE. The concatenation of a null column with non-empty strings produces a final result of null, so only employees that have a commission appear in the query result. The output line for all other employees is null.
SET edb_redwood_strings TO off; SELECT RPAD(ename,10) || ' ' || TO_CHAR(sal,'99,999.99') || ' ' || TO_CHAR(comm,'99,999.99') "EMPLOYEE COMPENSATION" FROM emp; EMPLOYEE COMPENSATION ---------------------------------- ALLEN 1,600.00 300.00 WARD 1,250.00 500.00 MARTIN 1,250.00 1,400.00 TURNER 1,500.00 .00 (14 rows)
The following is the same query executed when edb_redwood_strings is set to TRUE. Here, the value of a null column is treated as an empty string. The concatenation of an empty string with a non-empty string produces the non-empty string. This result is consistent with the results produced by Oracle for the same query.
SET edb_redwood_strings TO on; SELECT RPAD(ename,10) || ' ' || TO_CHAR(sal,'99,999.99') || ' ' || TO_CHAR(comm,'99,999.99') "EMPLOYEE COMPENSATION" FROM emp; EMPLOYEE COMPENSATION ---------------------------------- SMITH 800.00 ALLEN 1,600.00 300.00 WARD 1,250.00 500.00 JONES 2,975.00 MARTIN 1,250.00 1,400.00 BLAKE 2,850.00 CLARK 2,450.00 SCOTT 3,000.00 KING 5,000.00 TURNER 1,500.00 .00 ADAMS 1,100.00 JAMES 950.00 FORD 3,000.00 MILLER 1,300.00 (14 rows)
In Oracle, when a runtime error occurs in a SQL command, all the updates on the database caused by that single command are rolled back. This is called statement level transaction isolation. For example, if a single UPDATE command successfully updates five rows, but an attempt to update a sixth row results in an exception, the updates to all six rows made by this UPDATE command are rolled back. The effects of prior SQL commands that have not yet been committed or rolled back are pending until a COMMIT or ROLLBACK command is executed.
In PostgreSQL, if an exception occurs while executing a SQL command, all the updates on the database since the start of the transaction are rolled back. In addition, the transaction is left in an aborted state and either a COMMIT or ROLLBACK command must be issued before another transaction can be started.
If edb_stmt_level_tx is set to TRUE, then an exception will not automatically roll back prior uncommitted database updates, emulating the Oracle behavior. If edb_stmt_level_tx is set to FALSE, then an exception will roll back uncommitted database updates.
Note: Use edb_stmt_level_tx set to TRUE only when absolutely necessary, as this may cause a negative performance impact.
The following example run in PSQL shows that when edb_stmt_level_tx is FALSE, the abort of the second INSERT command also rolls back the first INSERT command. Note that in PSQL, the command \set AUTOCOMMIT off must be issued, otherwise every statement commits automatically defeating the purpose of this demonstration of the effect of edb_stmt_level_tx.
\set AUTOCOMMIT off SET edb_stmt_level_tx TO off; INSERT INTO emp (empno,ename,deptno) VALUES (9001, 'JONES', 40); INSERT INTO emp (empno,ename,deptno) VALUES (9002, 'JONES', 00); ERROR: insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk" DETAIL: Key (deptno)=(0) is not present in table "dept". COMMIT; SELECT empno, ename, deptno FROM emp WHERE empno > 9000; empno | ename | deptno -------+-------+-------- (0 rows)
In the following example, with edb_stmt_level_tx set to TRUE, the first INSERT command has not been rolled back after the error on the second INSERT command. At this point, the first INSERT command can either be committed or rolled back.
\set AUTOCOMMIT off SET edb_stmt_level_tx TO on; INSERT INTO emp (empno,ename,deptno) VALUES (9001, 'JONES', 40); INSERT INTO emp (empno,ename,deptno) VALUES (9002, 'JONES', 00); ERROR: insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk" DETAIL: Key (deptno)=(0) is not present in table "dept". SELECT empno, ename, deptno FROM emp WHERE empno > 9000; empno | ename | deptno -------+-------+-------- 9001 | JONES | 40 (1 row) COMMIT;
A ROLLBACK command could have been issued instead of the COMMIT command in which case the insert of employee number 9001 would have been rolled back as well.
Before creating a link to an Oracle server, you must direct Advanced Server to the correct Oracle home directory. You can either set the environment variable ORACLE_HOME to the installation directory of the Oracle client, or set the value of the oracle_home configuration parameter in the postgresql.conf file. The oracle_home configuration parameter will override the ORACLE_HOME environment variable.
To set the oracle_home configuration parameter in the postgresql.conf file, edit the file, adding the following line:
oracle_home = 'complete_path_to_the_Oracle_home_directory'
The command must specify the complete path to the home directory of the Oracle client.