本記事は PostgreSQL Advent Calendar 2021 4日目の記事です。oracle_fdw を紹介します。
昨日は @abejjj さんの「pg_bigmインデックスでもHOTが有効なのか検証してみる。」でした。明日は @YuitoSato さんの「PostgreSQLのNull許容外部キーの使い所について」の予定です。
oracle_fdw とは
oracle_fdw は PostgreSQL の拡張機能（Extension）で、PostgreSQL から Oracle Database へ接続するための FOREIGN DATA WAPPER です。oracle_fdw を使うことで PostgreSQL に接続したアプリケーションが Oracle Database 上のテーブルに対して DML を発行できるようになります。
Oracle Database 上のテーブルは FOREIGN TABLE として作成されるため、アプリケーションはテーブルが PostgreSQL 上のテーブルか Oracle Database 上のテーブルかを意識する必要はありません。テーブルに対して DML を発行するだけです。oracle_fdw は PostgreSQL 9.6.9 以降または PostgreSQL 10.4 以降がサポートされます。
インストール
Oracle Instant Client のインストール
oracle_fdw を使って Oracle Database に接続するクライアント・ソフトウェアとしては Oracle Instant Client が便利です。Oracle Instant Client は無料でダウンロードできます。zip 形式、rpm 形式が選択できますが本記事では zip 形式を使います。
いくつかのパッケージを選択する必要があります。oracle_fdw の動作に必要なパッケージは Basic Package だけです。ただし oracle_fdw をソースコードからコンパイルする場合はヘッダファイルが必要になるため SDK Package も一緒にダウンロードします。ダウンロードした zip ファイルは任意のディレクトリに展開します。複数のパッケージをダウンロードした場合でも同じディレクトリに展開します。
Oracle Instant Client のインストールが完了したら PostgreSQL 管理ユーザーで以下の環境変数を定義します。
| 環境変数 | 説明 | 設定例 |
|---|---|---|
| ORACLE_HOME | Oracle Instant Client をインストールしたディレクトリ（ソースのビルドに必要） | /home/postgres/instantclient_21_3/ |
| LD_LIBRARY_PATH | Oracleライブラリのパス（実行時に必要） | /home/postgres/instantclient_21_3/ |
oracle_fdw のインストール
環境変数の設定後に oracle_fdw をビルドします。ソースコードはこちらからダウンロードできます。oracle_fdw は環境変数 ORACLE_HOME と pg_config コマンドを実行することで Oracle Database Client のインストール場所と PostgreSQL のインストール場所を検知します。ビルドとインストールは make コマンドと make install コマンドを実行するだけです。
$ export ORACLE_HOME=$HOME/instantclient_21_3
$ cd oracle_fdw/
$ make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I"/home/postgres/instantclient_21_3/sdk/include" -I"/home/postgres/instantclient_21_3/oci/include" -I"/home/postgres/instantclient_21_3/rdbms/public" -I"/home/postgres/instantclient_21_3/" -I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.12/client64 -I/usr/include/oracle/19.12/client -I/usr/include/oracle/19.11/client64 -I/usr/include/oracle/19.11/client -I/usr/include/oracle/19.10/client64 -I/usr/include/oracle/19.10/client -I/usr/include/oracle/19.9/client -I/usr/include/oracle/19.9/client64 -I/usr/include/oracle/19.8/client -I/usr/include/oracle/19.8/client64 -I/usr/include/oracle/19.6/client -I/usr/include/oracle/19.6/client64 -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/local/pgsql/include/server -I/usr/local/pgsql/include/internal -D_GNU_SOURCE -c -o oracle_utils.o oracle_utils.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I"/home/postgres/instantclient_21_3/sdk/include" -I"/home/postgres/instantclient_21_3/oci/include" -I"/home/postgres/instantclient_21_3/rdbms/public" -I"/home/postgres/instantclient_21_3/" -I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.12/client64 -I/usr/include/oracle/19.12/client -I/usr/include/oracle/19.11/client64 -I/usr/include/oracle/19.11/client -I/usr/include/oracle/19.10/client64 -I/usr/include/oracle/19.10/client -I/usr/include/oracle/19.9/client -I/usr/include/oracle/19.9/client64 -I/usr/include/oracle/19.8/client -I/usr/include/oracle/19.8/client64 -I/usr/include/oracle/19.6/client -I/usr/include/oracle/19.6/client64 -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/local/pgsql/include/server -I/usr/local/pgsql/include/internal -D_GNU_SOURCE -c -o oracle_gis.o oracle_gis.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/local/pgsql/lib -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags -L"/home/postgres/instantclient_21_3/" -L"/home/postgres/instantclient_21_3/bin" -L"/home/postgres/instantclient_21_3/lib" -L"/home/postgres/instantclient_21_3/lib/amd64" -lclntsh -L/usr/lib/oracle/21/client64/lib -L/usr/lib/oracle/19.12/client64/lib -L/usr/lib/oracle/19.12/client/lib -L/usr/lib/oracle/19.11/client64/lib -L/usr/lib/oracle/19.11/client/lib -L/usr/lib/oracle/19.10/client64/lib -L/usr/lib/oracle/19.10/client/lib -L/usr/lib/oracle/19.9/client/lib -L/usr/lib/oracle/19.9/client64/lib -L/usr/lib/oracle/19.8/client/lib -L/usr/lib/oracle/19.8/client64/lib -L/usr/lib/oracle/19.6/client/lib -L/usr/lib/oracle/19.6/client64/lib -L/usr/lib/oracle/19.3/client/lib -L/usr/lib/oracle/19.3/client64/lib -L/usr/lib/oracle/18.5/client/lib -L/usr/lib/oracle/18.5/client64/lib -L/usr/lib/oracle/18.3/client/lib -L/usr/lib/oracle/18.3/client64/lib -L/usr/lib/oracle/12.2/client/lib -L/usr/lib/oracle/12.2/client64/lib -L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib -L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib
$
$ su
Password:
# make install
/bin/mkdir -p '/usr/local/pgsql/lib'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/bin/mkdir -p '/usr/local/pgsql/share/doc/extension'
/bin/install -c -m 755 oracle_fdw.so '/usr/local/pgsql/lib/oracle_fdw.so'
/bin/install -c -m 644 .//oracle_fdw.control '/usr/local/pgsql/share/extension/'
/bin/install -c -m 644 .//oracle_fdw--1.2.sql .//oracle_fdw--1.0--1.1.sql .//oracle_fdw--1.1--1.2.sql '/usr/local/pgsql/share/extension/'
/bin/install -c -m 644 .//README.oracle_fdw '/usr/local/pgsql/share/doc/extension/'
#
インストールが完了すると Extension として確認できます。
postgres=# SELECT name, default_version FROM pg_available_extensions WHERE name LIKE 'oracle%';
name | default_version
------------+-----------------
oracle_fdw | 1.2
(1 row)
構成
oracle_fdw を構成します。
EXTENSION のロード
oracle_fdw を使うデータベースに管理者権限（SUPERUSER）で接続し、CREATE EXTENSION 文を実行します。この時点で失敗する場合、環境変数 LD_LIBRARY_PATH の設定が間違っている可能性があります。環境変数の定義を確認するには oracle_diag 関数を実行します。
postgres=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
postgres=# SELECT oracle_diag();
oracle_diag
-----------------------------------------------------------------------------------------------------------------
oracle_fdw 2.5.0devel, PostgreSQL 14.0, Oracle client 21.3.0.0.0, ORACLE_HOME=/home/postgres/instantclient_21_3
(1 row)
oracle_fdw をロードすることで以下のオブジェクトが作成されます。
| 名前 | 種類 | バージョン |
|---|---|---|
| oracle_diag | FUNCTION | 1.0 |
| oracle_execute | FUNCTION | 1.1 |
| oracle_fdw_handler | FUNCTION | 1.2 |
| oracle_fdw_validator | FUNCTION | 1.2 |
| oracle_close_connections | FUNCTION | 1.2 |
| oracle_fdw | FOREIGN DATA WRAPPER | 1.2 |
FOREIGN SERVER の作成
リモート環境の Oracle Database を定義するため、管理者権限で CREATE SERVER 文を実行します。FOREIGN DATA WRAPPER として oracle_fdw を指定します。オプションとして最低限 dbserver を指定します。dbserver オプションは以下の構文で設定します。
//{Oracle Databaseホスト名かIPアドレス}:{ポート番号}/{サービス名}
サービス名は Oracle Database 管理者が定義しています。以下の例ではホスト dbsrv1 で稼働する Oracle Database サービス O19C1 に接続する FOREIGN SERVER を作成しています。接続ポート番号はデフォルトの 1521 を指定しています。
GRANT 文はユーザー demo に対して FOREIGN SERVER の使用を許可しています。
postgres=# CREATE SERVER o19c1 FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//dbsrv1:1521/O19C1');
CREATE SERVER
postgres=# GRANT USAGE ON FOREIGN SERVER o19c1 TO demo;
GRANT
上記の例では dbserver オプションのみ定義していますが、それ以外に以下のオプションを指定できます。オプション名、設定値共に大文字／小文字を区別しますので小文字で指定します。
| オプション名 | 説明 | デフォルト値 | 必須 | 備考 |
|---|---|---|---|---|
| dbserver | Oracle Database 接続先 | - | YES | |
| isolation_level | トランザクション分離レベル | serializable | NO | read_committed, read_only も使用可能 |
| nchar | 高度な NCHAR 変換を行う | off | NO | OCI_NCHAR_LITERAL_REPLACE_ON を指定して接続 |
サーバーが定義できたらユーザー・マッピングを作成します。Oracle Database に接続するためのユーザー名とパスワードを定義します。user オプションと passsword オプションが利用できます。
postgres=> CREATE USER MAPPING FOR demo SERVER o19c1 OPTIONS (user 'scott', password 'tiger');
CREATE USER MAPPING
FOREIGN TABLE の作成
CREATE FOREIGN TABLE 文を実行して FOREIGN TABLE を作成します。テーブル定義に加えて SERVER 句と、OPTIONS 句を指定します。OPTIONS 句には Oracle Database 側のテーブル名を示す table オプションが必須です。Oracle Database では特に意識しない限りオブジェクト名は大文字で作成されます。このため table オプションには大文字を使います。
更新処理を行う場合にはレコードを一意に特定するために列定義に key オプションが必要です。
postgres=> CREATE FOREIGN TABLE data1(c1 NUMERIC OPTIONS (key 'true'), c2 VARCHAR(10)) SERVER o19c1 OPTIONS (table 'DATA1');
CREATE FOREIGN TABLE
table 以外に以下のオプションを利用できます。
| オプション名 | 説明 | デフォルト値 | 必須 | 備考 |
|---|---|---|---|---|
| table | Oracle Database テーブル名 | - | YES | 大文字で指定 |
| dblink | テーブルにアクセスするためのデータベース・リンク定義 | - | NO | |
| schema | スキーマ名 | - | NO | 接続ユーザーと異なるテーブルを参照する場合 |
| max_long | LONG, LONG RAW 列の最大長 | 32767 | NO | |
| readonly | 読み取り専用 | false | NO | |
| sample_percent | ANALYZE 実行時の行割合 | 100 | NO | 0.000001～100 の範囲 |
| prefetch | プリフェッチ・レコード数 | 200 | NO | |
| strip_zeros | 文字列型の 0x00 データの削除 | false | NO | 列オプション |
| key | レコードを一意に特定する列 | - | NO | 列オプション |
table オプションにはテーブル名だけでなく、SELECT 文を記述することもできます。Oracle Database には FROM 句内に埋め込まれて送信されます。
postgres=> CREATE FOREIGN TABLE query1(c1 NUMERIC OPTIONS (key 'true'), c2 VARCHAR(10)) SERVER o19c1 OPTIONS (table '(SELECT c1, c2 FROM DATA1 WHERE c1<1000)');
CREATE FOREIGN TABLE
postgres=> EXPLAIN ANALYZE SELECT * FROM query1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on query1 (cost=10000.00..20000.00 rows=1000 width=70) (actual time=0.448..0.457 rows=3 loops=1)
Oracle query: SELECT /*f3a3f26a5a1379aef1fbb483c2bc71e3*/ r1."C1", r1."C2" FROM (SELECT c1, c2 FROM DATA1 WHERE c1<1000) r1
Planning Time: 1.344 ms
Execution Time: 0.472 ms
(4 rows)
実行計画の確認
FOREIGN TABLE に対して DML を実行できます。EXPLAIN 文により、実行計画を表示すると Oracle Database に転送した SQL 文が確認できます（Oracle query: 項目）。Oracle Database に転送される SQL 文には SQL 文の MD5 ハッシュ値がコメントとして付与されます。本記事の環境では、PostgreSQL 14.0 から Oracle Database 19c に接続しています。
SELECT 文
下記の SELECT 文の例を見ると、単純な WHERE 句はほぼそのまま転送されていますが、関数を使った WHERE 句、ORDER BY 句、集計関数は転送されずに PostgreSQL 側で実行していることがわかります。
postgres=> EXPLAIN SELECT COUNT(*) FROM data1 WHERE c1*2 =100 AND LEFT(c2, 2) = 'da' ORDER BY 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=10010.02..10010.03 rows=1 width=8)
Sort Key: (count(*))
-> Aggregate (cost=10010.00..10010.01 rows=1 width=8)
-> Foreign Scan on data1 (cost=10000.00..10010.00 rows=1 width=0)
Filter: ("left"((c2)::text, 2) = 'da'::text)
Oracle query: SELECT /*7567bdf9c313b46d272c3d22f7621062*/ r1."C1", r1."C2" FROM "DATA1" r1 WHERE ((r1."C1" * 2) = 100)
(6 rows)
デフォルトの初期コストは 10000、統計情報が無い場合のタプル数のデフォルトは 1000 と想定されています。
UPDATE / DELETE 文
更新文（DELETE / UPDATE）文を実行する場合、レコードを一意に特定する列に key オプションが存在しないと以下のエラーが発生します。
postgres=> DELETE FROM data1 WHERE c1=100;
ERROR: no primary key column specified for foreign Oracle table
DETAIL: For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.
HINT: Set the option "key" on the columns that belong to the primary key.
key オプションを指定していても、Oracle Database から複数レコードが返ると以下のエラーが発生します。
postgres=> DELETE FROM data1 WHERE c1=100;
ERROR: DELETE on Oracle table removed 2 rows instead of one in iteration 0
HINT: This probably means that you did not set the "key" option on all primary key columns.
また更新処理は一旦 SELECT FOR UPDATE 文が実行されてから対象レコードが更新されます。
postgres=> EXPLAIN ANALYZE DELETE FROM data1 WHERE c1=100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Delete on data1 (cost=10000.00..20000.00 rows=0 width=0) (actual time=0.551..0.552 rows=0 loops=1)
Oracle statement: DELETE FROM "DATA1" WHERE "C1" = :k1
-> Foreign Scan on data1 (cost=10000.00..20000.00 rows=1000 width=32) (actual time=0.550..0.551 rows=0 loops=1)
Oracle query: SELECT /*5cf5c6f38c3bd8a36f3c9aa79ed898e6*/ r1."C1" FROM "DATA1" r1 WHERE (r1."C1" = 100) FOR UPDATE
Planning Time: 1.210 ms
Execution Time: 0.580 ms
(6 rows)
開発中バージョンのバグでテーブル内で key オプションと strip_zeros オプション両方を指定していると更新 DML がエラーになります。oracle_fdw 2.5 で解消 されます。
postgres=> CREATE FOREIGN TABLE data2(c1 NUMERIC OPTIONS (key 'true'), c2 VARCHAR(10) OPTIONS (strip_zeros 'true')) SERVER o19c1 OPTIONS (table 'DATA2');
CREATE FOREIGN TABLE
postgres=> DELETE FROM data2 WHERE c1=100;
ERROR: impossible column option "strip_zeros"
INSERT 文
INSERT 文は基本的にそのまま送信されますが、関数は PostgreSQL 側で実行された結果が送信されます。
postgres=> EXPLAIN ANALYZE INSERT INTO data1 VALUES (100, LEFT('ABCDEF', 2)) ;
QUERY PLAN
---------------------------------------------------------------------------------------------
Insert on data1 (cost=0.00..0.01 rows=0 width=0) (actual time=0.404..0.404 rows=0 loops=1)
Oracle statement: INSERT INTO "DATA1" ("C1", "C2") VALUES (:p1, :p2)
-> Result (cost=0.00..0.01 rows=1 width=70) (actual time=0.001..0.001 rows=1 loops=1)
Planning Time: 0.974 ms
Execution Time: 0.437 ms
(5 rows)
TRUNCATE 文
FOREIGN TABLE に対する TRUNCATE 文の実行は PostgreSQL 14 からサポートされていますが、oracle_fdw についてはまだ対応されていません。
postgres=> TRUNCATE TABLE data1;
ERROR: cannot truncate foreign table "data1"
データ型の違い
Oracle Database と PostgreSQL には様々なデータ型が定義されています。それぞれよく似た機能を持っていますが微妙な違いがあります。
下記の表は代表的なデータ型の違いです。
| PostgreSQLデータ型 | Oracle Databaseデータ型 | 違い |
|---|---|---|
| numeric | NUMBER | ほぼ同じ |
| char | CHAR | Oracle Databaseは多くの場合バイト数 |
| varchar | VARCHAR2 | Oracle Databaseは多くの場合バイト数 |
| date | DATE | Oracle Databaseは時分秒まで保持 |
| real | BINARY_FLOAT | ほぼ同じ |
| double precision | BINARY_DOUBLE | ほぼ同じ |
| timestamp | TIMESTAMP | ほぼ同じ |
| bytea | BLOB | BLOB の方が大規模データ可能 |
| text | CLOB | CLOB の方が大規模データ可能 |
特にCHAR 型、VARCHAR 型は注意が必要です。Oracle Database 側で以下の定義で作成したテーブルを PostgreSQL から利用する場合を想定します。
SQL> SHOW PARAMETER nls_length_semantics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string BYTE
SQL>
SQL> CREATE TABLE char1(c1 CHAR(5), c2 VARCHAR2(5));
表が作成されました。
Oracle Database 側は c1,c2 列共に最大 5 バイトです。PostgreSQL 側で FOREIGN TABLE を作成します。PostgreSQL の c1, c2 列は共に 最大 5 文字になります。
postgres=> CREATE FOREIGN TABLE char1(c1 char(5), c2 varchar(5)) SERVER o19c1 OPTIONS (table 'CHAR1');
CREATE FOREIGN TABLE
c2 列に漢字２文字を格納しようとすると領域不足でエラーになります（文字コードは UTF-8 を想定）。
postgres=> INSERT INTO char1 VALUES ('ABC', '漢字');
ERROR: error executing query: OCIStmtExecute failed to execute remote query
DETAIL: ORA-12899: 列"SCOTT"."CHAR1"."C2"の値が大きすぎます(実際: 6、最大: 5)
更に c1 列に漢字１文字を格納しようとするとこれもエラーになります。これは１文字の漢字（＝３バイト）の後に、スペースを４文字付与して格納しようとしているためです（合計で７バイト）。
postgres=> INSERT INTO char1 VALUES ('漢', 'ABC');
ERROR: error executing query: OCIStmtExecute failed to execute remote query
DETAIL: ORA-12899: 列"SCOTT"."CHAR1"."C1"の値が大きすぎます(実際: 7、最大: 5)
ロケールと文字コード
Oracle Database Client が想定するクライアントのロケール情報は一般的には環境変数 NLS_LANG を指定しますが oracle_fdw では PostgreSQL サーバーの以下の設定から自動的に構成されます。
| パラメーター | 説明 | 設定例 | NLS_LANG設定 |
|---|---|---|---|
| server_encoding | PostgreSQL サーバのエンコード | UTF8 | AL32UTF8 |
| lc_message | メッセージ | ja_JP | Japanese_Japan |
oracle_fdw は lc_message の先頭３文字からロケールを判断し、Oracle Database 接続時の環境変数 NLS_LANG を構成します。
独自の設定を使用できるように oracle_fdw には CREATE FOREIGN DATA WRAPPER 文に nls_lang オプションを提供しています。