In this article we wrap up our discussion of oracle_fdw, the foreign data wrapper that allows PostgreSQL to access Oracle databases. We expand on pushdown and update transactions, and discuss data type conversion and how the wrapper handles differences in data types and constraints between the foreign table and the remote one.

Pushdown

Foreign data wrappers employ a mechanism called pushdown, which allows the remote side to execute the clauses WHERE, ORDER BY and JOIN. Pushing down WHERE and JOIN reduces the amount of data transferred between the local and remote servers, avoiding bottlenecks in communication.

The key points to keep in mind about pushdown are explained below.

WHERE pushdown

If the SQL statement has a WHERE clause, the search condition is passed to the Oracle database for execution, including any functions called in it. Please note that PostgreSQL and Oracle may have functions with the same name, but with different specifications, so you need to make sure that the function on the Oracle side has the intended specification.

Verifying if WHERE is pushed down

You can verify if the WHERE clause is pushed down by checking the execution plan with EXPLAIN ANALYZE VERBOSE.

postgres=# EXPLAIN ANALYZE VERBOSE SELECT * FROM f_ora_tbl WHERE id <= 3;
QUERY PLAN
-------------------------------------------------------------------------
Foreign Scan on public.f_ora_tbl
Output: id, name, t_date
Oracle query: SELECT r1."ID", r1."NAME", r1."T_DATE"
              FROM "ORA"."ORA_TBL" r1 WHERE (r1."ID" <= 3)
Oracle plan: SELECT STATEMENT
Oracle plan: TABLE ACCESS BY INDEX ROWID BATCHED ORA_TBL
Oracle plan: INDEX RANGE SCAN SYS_C0021627 (condition "R1"."ID"<=3)
Planning Time: 1.576 ms
Execution Time: 0.798 ms
(8 rows)

In the example above, we can see that the WHERE condition is running on the Oracle side based on the following:

  • The row starting with Foreign Scan shows the execution plan for the table on the Oracle side
  • The row starting with Oracle query shows the WHERE clause
  • The rows starting with Oracle plan show the search condition

ORDER BY pushdown

In general, if the SQL statement has an ORDER BY clause that does not include character type columns, it will be pushed down, and data will be sorted on the Oracle side.

  • The clause will not be pushed down if it contains character type columns, since oracle_fdw cannot guarantee that the sort order of Oracle and PostgreSQL will be the same.
  • The clause will be pushed down if the sort order can be guaranteed to be identical on both sides – this is the case for numeric and datetime data types. For other data types, the operation needs to be verified separately.
  • The clause will not be pushed down if the statement also contains a JOIN

Verifying if ORDER BY is pushed down

First, let's look at the execution plan of a statement where the column to be sorted is numeric type.

postgres=# EXPLAIN ANALYZE VERBOSE SELECT * FROM f_ora_tbl ORDER BY id; A
QUERY PLAN
-------------------------------------------------------------------------------
Foreign Scan on public.f_ora_tbl
Output: id, name, t_date
Oracle query: SELECT r1."ID", r1."NAME", r1."T_DATE"
              FROM "ORA_USER"."ORA_TBL" r1 ORDER BY B r1."ID" ASC NULLS LAST
Oracle plan: SELECT STATEMENT
Oracle plan: SORT ORDER BY B
Oracle plan: TABLE ACCESS FULL ORA_TBL
Planning Time: 1.843 ms
Execution Time: 3.270 ms
(8 rows)
A Sorted by numeric type
B Clause is pushed down to Oracle

In the example above, we can see that the clause is pushed down, because in the Foreign Scan section we have both the row starting with Oracle query and the one starting with Oracle plan showing ORDER BY.

Next, let's look at the execution plan when the column to be sorted is character type.

postgres=# EXPLAIN ANALYZE VERBOSE SELECT * FROM f_ora_tbl ORDER BY name; A
QUERY PLAN
----------------------------------------------------------------------------------------
Sort
Output: id, name, t_date
 Sort Key: f_ora_tbl.name
Sort Method: quicksort Memory: 25kB B
-> Foreign Scan on public.f_ora_tbl
  Output: id, name, t_date
  Oracle query: SELECT r1."ID", r1."NAME", r1."T_DATE" FROM "ORA_USER"."ORA_TBL" r1
  Oracle plan: SELECT STATEMENT
  Oracle plan: TABLE ACCESS FULL ORA_TBL
Planning Time: 1.618 ms
Execution Time: 3.245 ms
(11 rows)
A Sorted by character type
B Sort will occur on the PostgreSQL side

The Oracle query or Oracle plan rows in the result above do not contain the ORDER clause, showing that the clause is not pushed down – instead, the Sort Method row shows that sorting will be performed on the PostgreSQL side.

JOIN pushdown

Note the restrictions below on  JOIN clause pushdown:

  • The clause must be specified in a SELECT statement
  • The tables to be joined must be defined on the same foreign server
  • It must involve only 2 tables – join of additional tables will be performed on the PostgreSQL side
  • If the SELECT statement includes JOIN and WHERE, then both are pushed down
  • If the SELECT statement include JOIN and ORDER BY, then only JOIN is pushed down
  • The clause will be not be pushed down for cross joins without join condition

Verifying if JOIN is pushed down

Let's check the execution plan for a statement that joins 3 foreign tables defined on the same foreign server.

postgres=# EXPLAIN ANALYZE VERBOSE SELECT * FROM f_members t1
postgres-# INNER JOIN f_dept t2 ON t1.dep_id = t2.dep_id
postgres-# INNER JOIN f_personnel t3 ON t1.id = t3.id;
QUERY PLAN
-----------------------------------------------------------------------------------------
Hash Join
 Output: t1.id, t1.name, t1.dep_id, t2.dep_id, t2.dep_name, t3.id, t3.birth, t3.gender
 Hash Cond: (t1.id = t3.id) A
 -> Foreign Scan
     Output: t1.id, t1.name, t1.dep_id, t2.dep_id, t2.dep_name
      Oracle query: SELECT r1."ID", r1."NAME", r1."DEP_ID", r2."DEP_ID", r2."DEP_NAME"
                    FROM ("ORA_USER"."MEMBERS" r1
                   INNER JOIN "ORA_USER"."DEPT" r2 ON (r1."DEP_ID" = r2."DEP_ID"))
      Oracle plan: SELECT STATEMENT
      Oracle plan:  MERGE JOIN
     Oracle plan:   TABLE ACCESS BY INDEX ROWID DEPT
     Oracle plan:    INDEX FULL SCAN SYS_C0021629
     Oracle plan:   SORT JOIN (condition "R1"."DEP_ID"="R2"."DEP_ID") B
                             (filter "R1"."DEP_ID"="R2"."DEP_ID")
     Oracle plan:   TABLE ACCESS FULL MEMBERS
-> Hash
     Output: t3.id, t3.birth, t3.gender
     Buckets: 1024 Batches: 1 Memory Usage: 9kB
     -> Foreign Scan on public.f_personnel t3
         Output: t3.id, t3.birth, t3.gender
         Oracle query: SELECT r4."ID", r4."BIRTH", r4."GENDER"
                        FROM "ORA_USER"."PERSONNEL" r4
         Oracle plan: SELECT STATEMENT
         Oracle plan: TABLE ACCESS FULL PERSONNEL
Planning Time: 3.405 ms
Execution Time: 6.253 ms
(22 rows)
A Join of tables t1 and  t3 is performed on the PostgreSQL side
B Join of tables t1 and t2 is pushed down to Oracle 

In the example above, JOIN of tables t1 and t2 is pushed down to Oracle, as shown in the Oracle plan rows, but join of tables t1 and t3 is performed in PostgreSQL as shown by the Hash Cond row before the Foreign Scan block.

Next, let's check the execution plan of a statement where JOIN is pushed down but ORDER BY is not.

postgres=# EXPLAIN ANALYZE VERBOSE SELECT * FROM f_members t1
postgres-# INNER JOIN f_dept t2 ON t1.dep_id = t2.dep_id
postgres-# ORDER BY id;
QUERY PLAN
--------------------------------------------------------------------------------------
Sort
Output: t1.id, t1.name, t1.dep_id, t2.dep_id, t2.dep_name
Sort Key: t1.id
Sort Method: quicksort Memory: 25kB A
-> Foreign Scan
     Output: t1.id, t1.name, t1.dep_id, t2.dep_id, t2.dep_name
     Oracle query: SELECT r1."ID", r1."NAME", r1."DEP_ID", r2."DEP_ID", r2."DEP_NAME"
                   FROM ("ORA_USER"."MEMBERS" r1
                   INNER JOIN "ORA_USER"."DEPT" r2 ON (r1."DEP_ID" = r2."DEP_ID"))
     Oracle plan: SELECT STATEMENT
     Oracle plan: MERGE JOIN
     Oracle plan:   TABLE ACCESS BY INDEX ROWID DEPT
     Oracle plan:    INDEX FULL SCAN SYS_C0021629
     Oracle plan:   SORT JOIN (condition "R1"."DEP_ID"="R2"."DEP_ID") B
                              (filter "R1"."DEP_ID"="R2"."DEP_ID")
     Oracle plan:   TABLE ACCESS FULL MEMBERS
Planning Time: 2.581 ms
Execution Time: 1.517 ms
(15 rows)
A Sorted on the PostgreSQL side
B Joined on the Oracle side

In the example above, we can see that JOIN is pushed down because the Oracle plan rows shows MERGE JOIN, but that ORDER is not pushed down, as it is displayed in the Sort Method row before the Foreign Scan block.

Update transactions and serialisation errors

oracle_fdw also supports update transactions, using the SERIALIZABLE transaction isolation level to ensure consistency. This is because a single SQL statement may spawn multiple SQL statements to the Oracle database. Therefore, updating a foreign table with multiple concurrent transactions may result in a serialisation error.

To avoid this, ensure that applications do not update foreign tables concurrently. If a serialisation error occurs, roll back the transaction and execute again.

Serialisation error in concurrent transactions

oracle_fdw does not support prepared statements (PREPARE) and two-phase commit (PREPARE TRANSACTION, etc.), because they require control over the Oracle database.

Difference in data types

When creating foreign tables, the columns' data types must be compatible with the remote table. The table below list the data type combinations that oracle_fdw can convert.

Oracle data type Compatible PostgreSQL data types
CHAR char, varchar, text
NCHAR
VARCHAR
NVARCHAR2
LONG
VARCHAR2 char, varchar, text, json
CLOB
RAW uuid, bytea
BLOB bytea
BFILE (read only)
LONG RAW
NUMBER(n,m) with m<=0 numeric, float4, float8, int2, int4, int8, boolean, char, varchar, text
NUMBER numeric, float4, float8, char, varchar, text
FLOAT
BINARY_FLOAT
BINARY_DOUBLE
DATE date, timestamp, timestamptz, char, varchar, text
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH interval, char, varchar, text
INTERVAL DAY TO SECOND
MDSYS.SDO_GEOMETRY geometry*1
1: Limited support – refer to the oracle_fdw documentation for details

A runtime error will occur if the length of the data exceeds the actual column length. Also note that data types may behave differently, such as fraction rounding in floating point data types and datetime data types.

Keep in mind that by default the length of CHAR and VARCHAR2 types in Oracle are specified in bytes, while the length of PostgreSQL's types CHAR, VARCHAR, and TEXT are specified in characters.

Foreign table definition constraints and default values

When creating a foreign table, it is recommended to match the constraints (CHECK, NOT NULL, etc.) and default values (DEFAULT) defined in the remote table. The timing for constraint checks and default value application is discussed below, followed by issues that occur when this recommendation is not followed.

Timing of constraint check and default value application

To ensure that both foreign and remote tables are updated correctly, it is important that they both specify the same constraints and default specifications, or errors may occur. This section explains at what stage the constraints are checked and default values applied.

While oracle_fdw applies default values to columns created with DEFAULT, it does not check constraints – this is done on the Oracle side.

In the example below, we try to insert a row with NULL value for the emp_id column (created with NOT NULL in Oracle) and 'default' value for the dept column (created with DEFAULT 10 both in PostgreSQL and Oracle).

Trying to insert NULL in a NOT NULL column

Before sending the statement, oracle_fdw replaces 'default' with '10' for the dept column, but it does not verify the NULL constraint of the emp-id column, and sends the value as is. This will result in the constraint violation below in Oracle.

postgres=# INSERT INTO f_ora_tbl3 VALUES (10, NULL, 'abc', default);
ERROR: error executing query: OCIStmtExecute failed to execute remote query
DETAIL: ORA-01400: cannot insert NULL into ("ORA_USER"."ORA_TBL3"."EMP_ID")

If the primary key is incorrectly specified

Even if you do not define the constraints on the foreign table to match the Oracle table, SQL statements will not return an error as long as constraints are not violated on the Oracle side.

In the example below, we create a table and incorrectly specify emp_id as the primary key, instead of id.
After that, we execute 2 INSERTs adding the same value '9' to the incorrect primary key, and that does not generate an error, because as far as the remote Oracle table is concerned, the only constraint on emp_id is NOT NULL.
However, later when we try to execute UPDATE and DELETE, we receive an error because now we have a  conflict between the primary key constraint on the foreign table.

postgres=# CREATE FOREIGN TABLE f_ora_tbl3(
postgres(# id integer NOT NULL, A
postgres(# emp_id integer OPTIONS(key 'true'), B
postgres(# name varchar(32),
postgres(# dept integer DEFAULT 10)
postgres-# SERVER ora_sv OPTIONS (SCHEMA 'ORA_USER', TABLE 'ORA_TBL3');
CREATE FOREIGN TABLE
postgres=# INSERT INTO f_ora_tbl3 VALUES(1, 9,'aaa',7);
INSERT 0 1
postgres=# INSERT INTO f_ora_tbl3 VALUES(2, 9,'bbb',4); C
INSERT 0 1
postgres=# SELECT * FROM f_ora_tbl3;
id | emp_id | name | dept
---+--------+------+-----
1 |     9 | aaa  |   7
 2 |      9 | bbb  |   4 D
(2 rows)

postgres=# UPDATE f_ora_tbl3 SET name='ccc' WHERE id=2; E
ERROR: UPDATE on Oracle table changed 2 rows instead of one in iteration 1
HINT: This probably means that you did not set the "key" option on all primary key columns.

postgres=# DELETE FROM f_ora_tbl3 WHERE id=2; E
ERROR: DELETE on Oracle table removed 2 rows instead of one in iteration 1
HINT: This probably means that you did not set the "key" option on all primary key columns.
A The primary key column is specified using NOT NULL instead of OPTIONS(key 'true')
B Column emp_id is specified using OPTIONS(key 'true') instead of NOT NULL
C The primary key constraint of the foreign table is not checked, so the row is inserted
D 2 rows contain the same value for the incorrect primary key col emp_id, resulting in conflict on the foreign table
E UPDATE and DELETE fail with oracle_fdw error

If the default value is not specified

In the example below, we create a table and incorrectly specify the dept column without the default value of 10. Then, we INSERT a row specifying 'default' for the dept column - since the foreign table was not specified with a default value, oracle-fdw will replace it will NULL and send it to Oracle. Since the column is not specified with NOT NULL on the Oracle side, the statement will succeed creating the row with NULL on the dept column, which is not the intended result.

Specifying 'default' for a column with DEFAULT definition

Other points to note when using oracle_fdw

  • Statistics are not updated automatically
    When autovacuum is run on normal tables, ANALYZE is also run and statistics are updated. This does not apply to foreign tables. Therefore, a regular execution of ANALYZE needs to be incorporated in operation and management.
  • Stored procedures and user functions defined only in the remote database cannot be executed via oracle_fdw
    When executing a SQL statement that references a stored procedure or user function (including existing functions) on the Oracle side, the PostgreSQL parser does not reference the Oracle side definitions. Since they are not referenced, an error will be issued indicating that those definitions do not exist.
  • Performance will be affected if a large amount of data is acquired by foreign table scan
    By default, up to 200 rows can be acquired in one communication between PostgreSQL and Oracle database, as acquiring more than that in one go would impact performance. oracle_fdw is implemented using row prefetch of Oracle database, and the number of rows to fetch can be specified between 0 (disable prefetch) and 10240. This is done by specifying prefetch in OPTIONS of the foreign table. Larger values improve performance but use more memory on the PostgreSQL server.

More PostgreSQL Insider articles

This article explains how PostgreSQL Foreign Data Wrappers can access various external data sources such as Oracle, MySQL, SQL Server, and MongoDB, among others.
Read more
Explaining partitioning, the method for storing data in divided tables, based on declarative partitioning.
Read more
Have a look at the most frequently used open source extensions enhancing PostgreSQL functionalities, and what they are used for.
Read more

FUJITSU Enterprise Postgres is designed for developers, data architects, and DBAs seeking the efficiency of a leading open source based database technology with the enhanced security and performance features plus support required in a serious business environment.

Brochure

See how FUJITSU Enterprise Postgres enhances PostgreSQL providing 5 key enterprise capabilities for your mission-critical applications.

1st  page brochure FUJITSU Enterprise Postgeres - Fujitsu's enhanced open source PostgreSQL
VIEW BROCHURE

Read our latest blogs

Read our most recent articles regarding all aspects of PostgreSQL and FUJITSU Enterprise Postgres.