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

What is oracle_fdw?

The oracle_fdw extension is a foreign data wrapper that allows you to access Oracle table and views (including materialized views) via foreign tables.

When a PostgreSQL client accesses a foreign table, oracle_fdw accesses the corresponding data in the foreign Oracle database via the Oracle Call Interface (OCI) library on the PostgreSQL server.

Accessing Oracle databases

Accessing Oracle tables and views using oracle_fdw

How to use oracle_fdw

This section describes specific preparation and procedure for using oracle_fdw. It assumes that the PostgreSQL, oracle_fdw, and the OCI library have already been installed in the PostgreSQL server. It also assumes that the external table already exists in the Oracle database, and that the listener settings for remote connection have been completed.

Set up environment variables

Set the environment variables below to access the OCI library from oracle_fdw:

• LD_LIBRARY_PATH: Specify the directory where the OCI library is installed (for example, in Linux: /usr/lib/oracle/18.5/client64/lib)
• NLS_LANG: Specify the same value as NLS_LANG of the linked Oracle database

Prepare access to the Oracle database

For oracle_fdw to access the Oracle database, the settings below need to be specified.

Configuring PostgreSQL to access an Oracle table

1 Load oracle_fdw

2 Create the foreign server

3 Create a user mapping to the foreign server

4 Create the foreign table

1Load oracle_fdw

$ psql
postgres=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
postgres=# \dew A
                   List of foreign-data wrappers
    Name    |  Owner   |      Handler       |      Validator
------------+----------+--------------------+----------------------
 oracle_fdw | postgres | oracle_fdw_handler | oracle_fdw_validator
(1 row)

2Create the foreign server

postgres=# CREATE SERVER ora_sv FOREIGN DATA WRAPPER oracle_fdw
postgres-# OPTIONS (dbserver 'host_ora:1521/XEPDB1');
CREATE SERVER
postgres=# \des+ A
                                                      List of foreign servers
  Name  |  Owner   | Foreign-data wrapper |  Access privileges  | … |            FDW options            |…
--------+----------+----------------------+---------------------+ … +-----------------------------------+
 ora_sv | postgres | oracle_fdw           | postgres=U/postgres | … | (dbserver 'host_ora:1521/XEPDB1') |
(1 row)

postgres=# GRANT USAGE ON FOREIGN SERVER ora_sv TO postgres;
GRANT

3Create a user mapping to the foreign server

postgres=# CREATE USER MAPPING FOR postgres SERVER ora_sv
postgres-# OPTIONS ( USER 'ora_user', PASSWORD 'xxxx');
CREATE USER MAPPING
postgres=# \deu+ A
                List of user mappings
 Server | User name |          FDW options
--------+-----------+-------------------------------------
 ora_sv | postgres  | ("user" 'ora_user', password 'xxxx')
(1 row)

4Create the foreign table

postgres=# CREATE FOREIGN TABLE f_ora_tbl(
postgres(# id int OPTIONS (key 'true'), A
postgres(# name varchar(64),
postgres(# t_data timestamp)
postgres-# SERVER ora_sv OPTIONS (SCHEMA 'ORA_USER' , TABLE 'ORA_TBL'); B
CREATE FOREIGN TABLE
postgres=# \det+ C
                             List of foreign tables
 Schema |   Table    | Server |               FDW options              | Description
--------+------------+--------+----------------------------------------+-------------
 public | f_ora_tbl  | ora_sv | (schema 'ORA_USER', "table" 'ORA_TBL') |
(1 row)
postgres=# \d f_ora_tbl D
                           Foreign table "public.f_ora_tbl"
 Column |            Type             | Collation | Nullable | Default | FDW options
--------+-----------------------------+-----------+----------+---------+--------------
 id     | integer                     |           |          |         | (key 'true')
 name   | character varying(64)       |           |          |         |
 t_data | timestamp without time zone |           |          |         |
Server: ora_sv
FDW options: (schema 'ORA_USER', "table" 'ORA_TBL')

Note the following when creating a foreign table:

• The columns must be created with the same data types as the Oracle table or view.
  While oracle_fdw automatically converts data types when accessing foreign tables, it is recommended to check the result if there is data type discrepancy between the data sources.
• If you want to update data on the Oracle side, you must specify OPTIONS (key 'true') for all primary key columns.
• For the database schema and table names, use the same capitalization as used in the Oracle side.
  Note that Oracle databases identifiers defined without double quotes are created using uppercase.
• The schema and table names must be enclosed in single quotation marks in OPTIONS.

Database identifier capitalization

As mentioned above, the capitalization of the schema and table names defined for the foreign table in PostgreSQL must match exactly their definition in Oracle. Note that while no error is raised if a different capitalization is specified during creation of the foreign table, an error will be raised when trying to access it.

postgres=# CREATE FOREIGN TABLE f_ora_tbl2(
postgres(# id int OPTIONS(key 'true'),
postgres(# name varchar(64),
postgres(# t_data timestamp)
postgres-# SERVER ora_sv OPTIONS (SCHEMA 'ora_user', TABLE 'ora_tbl');
CREATE FOREIGN TABLE A
 
postgres=# SELECT * FROM f_ora_tbl2; B
ERROR:  Oracle table "ora_user"."ora_tbl" for foreign table "f_ora_tbl2" does not exist or does not allow read access
DETAIL:  ORA-00942: table or view does not exist.
HINT:  Oracle table names are case sensitive (normally all uppercase).

Access the Oracle database

Now that you are ready to use the foreign table, issue a query to confirm that you can access it.

Accessing an Oracle table via the OCI

1 Client sends a query for the foreign table

2 PostgreSQL requests oracle_fdw to acquire the execution plan and table data of the Oracle table

3 oracle_fdw reads foreign server and user mapping information and returns access information

4 oracle_fdw sends the query to Oracle database via the OCI library

5 oracle_fdw acquires the result and returns it to PostgreSQL

6 Client receives the result

You can verify that the foreign table created is accessing the Oracle table intended, as follows:

1Execute a query on the foreign table

postgres=# SELECT * FROM f_ora_tbl;
 id | name |           t_data
----+------+----------------------------
  1 | abc  | 2020-01-17 21:26:01.129322
  2 | LMN  | 2020-01-17 21:26:07.786242
  3 | XYZ  | 2020-01-17 21:26:13.957481
(3 rows)

2Show the query plan

Use EXPLAIN ANALYZE VERBOSE to display the execution plan and make sure you are accessing the table on the Oracle side.

postgres=# EXPLAIN ANALYZE VERBOSE SELECT * FROM f_ora_tbl;
                                      QUERY PLAN
-----------------------------------------------------------------------------------------
 Foreign Scan on public.f_ora_tbl …
   Output: id, name, t_data
   Oracle query: SELECT … r1."ID", r1."NAME", r1."T_DATE" FROM "ORA_USER"."ORA_TBL" r1 A
   Oracle plan: SELECT STATEMENT B
   Oracle plan:   TABLE ACCESS FULL ORA_TBL
 Planning Time: 1.719 ms
 Execution Time: 1.019 ms
(7 rows)

In the return execution plan:

• The row starting with Foreign Scan displays the table accessed on the Oracle side.
• The row starting with Oracle query displays the SQL statement executed on the Oracle side (because we specified ANALYZE)
• The rows starting with Oracle plan displays the execution plan on the Oracle side (because we specified VERBOSE)

3Update the foreign table

Update the foreign table and check the result to confirm that it has been updated.

postgres=# UPDATE f_ora_tbl SET name='pot', t_date=CURRENT_TIMESTAMP WHERE id=2;
UPDATE 1
postgres=# SELECT * FROM f_ora_tbl;
 id | name |           t_date
----+------+----------------------------
  1 | abc  | 2020-01-17 21:26:01.129322
  2 | pot  | 2020-06-20 16:58:32.359565
  3 | XYZ  | 2020-01-17 21:26:13.957481
(3 rows)

Notes about using oracle_fdw

Using oracle_fdw without understanding its mechanism may result in excessive access times and affect system performance. To make the best use of the wrapper, note the following:

• Pushdown

  Pushdown is a mechanism that foreign data wrappers deploy to allow the remote side to execute partial processing such as WHERE clauses in the client's SQL query. The following clauses are supported by oracle_fdw pushdown and are executed on the Oracle side conditionally:

  • WHERE is pushed down to the Oracle side.
  • ORDER BY is pushed down to the Oracle side when there is no JOIN and the sort order is common between PostgreSQL and Oracle database, such as for numeric data types.
  • JOIN is pushed down to the Oracle side for joins of up to 2 tables on the same foreign server in the SELECT statement.

  Note that WHERE and JOIN have the effect of reducing the amount of data transferred between the local and remote servers, which reduces the bottleneck in communication. We discuss pushdown further in part 2 of this article.

• Update transactions

  Transaction isolation level is SERIALIZABLE when querying an Oracle database from oracle_fdw. Therefore, be careful when you update the same foreign table with multiple concurrent transactions because an error may occur due to serialization failure.

  Prepared statements (PREPARE) and two-phase commit (PREPARE TRANSACTION, etc.) are not supported.

• Difference in data types

  As mentioned above, foreign tables must be created with the same column data types as the Oracle side. But even then, there are differences in data lengths and how fractions are rounded. A run-time error will occur if a character type column that exceeds the data length is accessed.

• Foreign table constraints and default values

  It is recommended to match the constraints (CHECK, NOT NULL, etc.) and default values (DEFAULT) of the remote table. Otherwise, UPDATEs and DELETEs on the foreign table may not be possible in cases where the result would violate a constraint.

Bulk creation of foreign tables

PostgreSQL's foreign data wrapper has a convenient feature that allows you to create foreign tables in bulk.

As described above, you can create a foreign table using CREATE FOREIGN TABLE, where you need to define each column according to its definition on the Oracle side. This can be a very laborious task if the number of tables is large.

But with IMPORT FOREIGN SCHEMA you can create foreign tables for each table defined in the imported schema, with the option to only import some of the tables from that schema. Note that the DEFAULT clause is not imported, so it must be added separately to columns later.

The Oracle database schema name must be enclosed in double quotes, because it must be specified in the same way as the information defined in the Oracle database system catalogue (the default is uppercase).

postgres=# CREATE SCHEMA imp_schema;
CREATE SCHEMA

postgres=# IMPORT FOREIGN SCHEMA "ORA_USER" FROM SERVER ora_sv INTO imp_schema;
IMPORT FOREIGN SCHEMA

postgres=# \det *.* A
     List of foreign tables B
   Schema   |   Table   | Server
------------+-----------+--------
 imp_schema | dept      | ora_sv
 imp_schema | members   | ora_sv
 imp_schema | ora_tbl   | ora_sv
 imp_schema | personal  | ora_sv
 public     | f_ora_tbl | ora_sv
(5 rows)