Start  trial

    Start trial

      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 1 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 serialization 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 serialization error.

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

      Serialization 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
      †: 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.
      Explaining partitioning, the method for storing data in divided tables, based on declarative partitioning.
      Have a look at the most frequently used open source extensions enhancing PostgreSQL functionalities, and what they are used for.

      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.