How to link to Oracle databases using oracle_fdw (part 2)

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)

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)

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)

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)

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.

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.

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.