Compatibility options v16

Suggest edits

These configuration parameters control various database compatibility features.

edb_redwood_date

Parameter type: Boolean

Default value: false

Range: {true | false}

Minimum scope of effect: Per session

When value changes take effect: Immediate

Required authorization to activate: Session user

Translates DATE to TIMESTAMP when DATE appears as the data type of a column in the commands and the table definition is stored in the database. A time component is stored in the column along with the date.

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, it's always internally translated to a TIMESTAMP. It can thus handle a time component if present. Examples of these contexts include:

The data type of a variable in an SPL declaration section
The data type of a formal parameter in an SPL procedure or SPL function
The return type of an SPL function

edb_redwood_greatest_least

Parameter type: Boolean

Default value: true

Range: {true | false}

Minimum scope of effect: Per session

When value changes take effect: Immediate

Required authorization to activate: Session user

The GREATEST function returns the parameter with the greatest value from its list of parameters. The LEAST function returns the parameter with the least value from its list of parameters.

When edb_redwood_greatest_least is set to TRUE, the GREATEST and LEAST functions return null when at least one of the parameters is null.

SET edb_redwood_greatest_least TO on;

SELECT GREATEST(1, 2, NULL, 3);

Output

greatest
----------

(1 row)

When edb_redwood_greatest_least is set to FALSE, null parameters are ignored except when all parameters are null. In that case, the functions return null.

SET edb_redwood_greatest_least TO off;

SELECT GREATEST(1, 2, NULL, 3);

Output

greatest
----------

        3
(1 row)

SELECT GREATEST(NULL, NULL, NULL);

Output

greatest
----------

(1 row)

edb_redwood_raw_names

Parameter type: Boolean

Default value: false

Range: {true | false}

Minimum scope of effect: Per session

When value changes take effect: Immediate

Required authorization to activate: Session user

When edb_redwood_raw_names is set to FALSE, database object names such as table names, column names, trigger names, program names, and user names appear in uppercase letters when viewed from Redwood catalogs (that is, system catalogs prefixed by ALL_, DBA_, or USER_). In addition, quotation marks enclose names that were created with enclosing quotation marks.

When edb_redwood_raw_names is set to TRUE, the database object names are displayed as they're stored in the PostgreSQL system catalogs when viewed from the Redwood catalogs. Names created without quotation marks around them appear in lower case as expected in PostgreSQL. Names created enclosed by quotation marks appear as they were created but without the quotation marks.

For example, the following user name is created, and then a session is started with that user:

CREATE USER reduser IDENTIFIED BY password;
edb=# \c - reduser
Password for user reduser:
You are now connected to database "edb" as user "reduser".

When connected to the database as reduser, the following tables are created:

CREATE TABLE all_lower (col INTEGER);
CREATE TABLE ALL_UPPER (COL INTEGER);
CREATE TABLE "Mixed_Case" ("Col" INTEGER);

When viewed from the Redwood catalog USER_TABLES, with edb_redwood_raw_names set to the default value FALSE, the names appear in upper case. The exception is the Mixed_Case name, which appears as created and also enclosed by quotation marks.

edb=> SELECT * FROM USER_TABLES;

Output

 schema_name  | table_name   | tablespace_name  | status  | temporary
--------------+--------------+------------------+---------+-----------
 REDUSER      | ALL_LOWER    |                  | VALID   | N
 REDUSER      | ALL_UPPER    |                  | VALID   | N
 REDUSER      | "Mixed_Case" |                  | VALID   | N
(3 rows)

When viewed with edb_redwood_raw_names set to TRUE, the names appear in lower case except for the Mixed_Case name, which appears as created but without quotation marks.

edb=> SET edb_redwood_raw_names TO true;
SET
edb=> SELECT  * FROM USER_TABLES;

Output

 schema_name | table_name | tablespace_name  | status | temporary
-------------+------------+------------------+--------+-----------
reduser      | all_lower  |                  | VALID  | N
reduser      | all_upper  |                  | VALID  | N
reduser      | Mixed_Case |                  | VALID  | N
(3 rows)

These names now match the case when viewed from the PostgreSQL pg_tables catalog:

edb=> SELECT schemaname, tablename, tableowner FROM pg_tables WHERE
tableowner = 'reduser';

Output

 schemaname | tablename  | tableowner
------------+------------+------------
reduser     | all_lower  | reduser
reduser     | all_upper  | reduser
reduser     | Mixed_Case | reduser
(3 rows)

edb_redwood_strings

Parameter type: Boolean

Default value: false

Range: {true | false}

Minimum scope of effect: Per session

When value changes take effect: Immediate

Required authorization to activate: Session user

If the edb_redwood_strings parameter is set to TRUE, when a string is concatenated with a null variable or null column, the result is the original string. If edb_redwood_strings is set to FALSE, the native PostgreSQL behavior is maintained, which is the concatenation of a string with a null variable or null column that gives a null result.

This example shows the difference. The sample application contains a table of employees. This table has a column named comm that's null for most employees. The following query is run with edb_redwood_string set to FALSE. Concatenating 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;

Output

   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. Concatenating an empty string with a non-empty string produces the non-empty string.

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;

Output

      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)

edb_stmt_level_tx

Parameter type: Boolean

Default value: false

Range: {true | false}

Minimum scope of effect: Per session

When value changes take effect: Immediate

Required authorization to activate: Session user

The term statement-level transaction isolation describes the behavior in which a runtime error occurs in a SQL command, and all the updates on the database caused by that single command are rolled back. 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 haven't yet been committed or rolled back are pending until a COMMIT or ROLLBACK command is executed.

In EDB Postgres Advanced Server, 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 start.

If edb_stmt_level_tx is set to TRUE, then an exception doesn't roll back prior uncommitted database updates. If edb_stmt_level_tx is set to FALSE, then an exception rolls back uncommitted database updates.

Note

Use edb_stmt_level_tx set to TRUE only when necessary, as it can have have a negative performance impact.

This 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. In PSQL, you must issue the command \set AUTOCOMMIT off. Otherwise every statement commits automatically, which doesn't show 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;

Output

empno  | ename | deptno
-------+-------+--------
(0 rows)

In this example, with edb_stmt_level_tx set to TRUE, the first INSERT command wasn't 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;

Output

empno  | ename | deptno
-------+-------+--------
  9001 | JONES |     40
(1 row)

COMMIT;

If a ROLLBACK command is issued instead of the COMMIT command, the insert of employee number 9001 is rolled back as well.

db_dialect

Parameter type: Enum

Default value: postgres

Range: {postgres | redwood}

Minimum scope of effect: Per session

When value changes take effect: Immediate

Required authorization to activate: Session user

In addition to the native PostgreSQL system catalog pg_catalog, EDB Postgres Advanced Server contains an extended catalog view. This is the sys catalog for the expanded catalog view. The db_dialect parameter controls the order in which these catalogs are searched for name resolution.

When set to postgres, the namespace precedence is pg_catalog and then sys, giving the PostgreSQL catalog the highest precedence. When set to redwood, the namespace precedence is sys and then pg_catalog, giving the expanded catalog views the highest precedence.

default_with_rowids

Parameter type: Boolean

Default value: false

Range: {true | false}

Minimum scope of effect: Per session

When value changes take effect: Immediate

Required authorization to activate: Session user

When set to on, CREATE TABLE includes a ROWID column in newly created tables, which you can then reference in SQL commands. In earlier versions of EDB Postgres Advanced Server, ROWIDs were mapped to OIDs. With EDB Postgres Advanced Server version 12 and later, the ROWID is an autoincrementing value based on a sequence that starts with 1. It's assigned to each row of a table created with the ROWIDs option. By default, a unique index is created on a ROWID column.

The ALTER and DROP operations are restricted on a ROWID column.

To restore a database with ROWIDs with EDB Postgres Advanced Server 11 or an earlier version, you must perform the following:

pg_dump: If a table includes OIDs then specify --convert-oids-to-rowids to dump a database. Otherwise, ignore the OIDs to continue table creation on EDB Postgres Advanced Server version 12 and later.
pg_upgrade: Errors out. But if a table includes OIDs or ROWIDs, then you must perform the following:
1. Take a dump of the tables by specifying the --convert-oids-to-rowids option.
2. Drop the tables, and then perform the upgrade.
3. After the upgrade is successful, restore the dump into a new cluster that contains the dumped tables into a target database.

optimizer_mode

Parameter type: Enum

Default value: choose

Range: {choose | ALL_ROWS | FIRST_ROWS | FIRST_ROWS_10 | FIRST_ROWS_100 | FIRST_ROWS_1000}

Minimum scope of effect: Per session

When value changes take effect: Immediate

Required authorization to activate: Session user

Sets the default optimization mode for analyzing optimizer hints.

The following table shows the possible values.

Hint	Description
`ALL_ROWS`	Optimizes for retrieval of all rows of the result set.
`CHOOSE`	Does no default optimization based on assumed number of rows to retrieve from the result set. This is the default.
`FIRST_ROWS`	Optimizes for retrieval of only the first row of the result set.
`FIRST_ROWS_10`	Optimizes for retrieval of the first 10 rows of the results set.
`FIRST_ROWS_100`	Optimizes for retrieval of the first 100 rows of the result set.
`FIRST_ROWS_1000`	Optimizes for retrieval of the first 1000 rows of the result set.

These optimization modes are based on the assumption that the client submitting the SQL command is interested in viewing only the first n rows of the result set and then abandons the rest of the result set. Resources allocated to the query are adjusted as such.

← Prev

Client connection defaults/other defaults

↑ Up

Configuration parameters by functionality

Customized options

Compatibility options v16

edb_redwood_date

edb_redwood_greatest_least

edb_redwood_raw_names

edb_redwood_strings

edb_stmt_level_tx

Note

db_dialect

default_with_rowids

optimizer_mode

← Prev

↑ Up

Next →