IvorySQL の紹介

更新日:2022年2月14日

IvorySQL は HighGo社を中心に開発されているオープンソース（Apache License）の Oracle互換データベースです。2021年 12月にバージョン 1.0 がリリースされました。IvorySQL は PostgreSQL と Oracle互換機能を提供するサードパーティ拡張 orafce の組み合わせをベースとしており、そこに更に独自の Oracle互換機能を追加しています。

本稿では、IvorySQL 1.0 の導入方法、機能、使用方法を解説します。また、Oracle Database 移行先の選択肢としての現時点の見解を記載します。

2021年 1月 25日に IvorySQL 1.1 がリリースされました。本バージョンについてに補足も記載します。

IvorySQL の導入方法

インストール

IvorySQL は github で開発リポジトリが公開されていて、そこからソースコードを取得できます。また、RHEL7、RHEL8 向けの rpmパッケージが HighGo の yumリポジトリで公開されています。

ソースコードからビルドする手順例を以下に示します。

$ git clone https://github.com/IvorySQL/IvorySQL.git
$ cd IvorySQL
$ ./configure --prefix=/usr/local/ivorysql --enable-debug
$ make
$ su -c 'make install'
$ cd contrib
$ make
$ su -c 'make install'

ビルド手順は PostgreSQL と同じです。configure で指定可能なオプションは PostgreSQL と違いありません。contrib が重要ですのでこれもビルド、インストールします。

環境変数としては以下を設定すれば良いでしょう。データが格納されるディレクトリを PostgreSQL と同様に PGDATA に設定します。

export PATH=/usr/local/ivorysql/bin:$PATH
export PGDATA=/DATA/ivdata
export PGUSER=ivorysql

続いて、rpmパッケージから導入する手順を示します。本稿作成に当たっては RHEL 8.x を使用しましたが、RHEL 7.x や互換OS でも手順に違いはありません。

HighGo社の Webページ から、リポジトリ定義のパッケージivorysql-release-1.0-1.noarch.rpmが入手できます。まずはこれをインストールします。

# yum install ivorysql-release-1.0-1.noarch.rpm

この rpmファイルには若干問題あるようで以下のエラーが出ます。

エラー: トランザクション ロックを（/var/lib/rpm/.rpm.lock 上に）作成できません。(リソースが一時的に利用できません)
エラー: /etc/pki/rpm-gpg/HIGHGO-SOFTWARE-GPG-KEY: キー 1 のインポートに失敗しました。
警告: %post(ivorysql-release-1.0-1.noarch) スクリプトの実行に失敗しました。終了ステータス 1

Error in POSTIN scriptlet in rpm package ivorysql-release

そこで、失敗が報告されたキーのインポートを手動で実行しておきます。

# rpm --import /etc/pki/rpm-gpg/HIGHGO-SOFTWARE-GPG-KEY

次に IvorySQL のパッケージをインストールします。

# yum install ivorysql1-server ivorysql1 ivorysql1-libs ivorysql1-contrib

パッケージの構成は PostgreSQL の rpmパッケージを踏襲したものとなっています。IvorySQL を稼働させるための OSユーザ ivorysql が作られ、ivorysqlユーザのシェル設定に PGDATA環境変数が予め設定されています。また、追加のシェル設定を .pgsql_profile から読み込むようになっています。

PATH環境変数は設定されないので以下のように追加しておきます。

# su - ivorysql
$ export PATH=/usr/local/ivorysql/ivorysql-1/bin:$PATH
$ cat > .pgsql_profile <<'EOF'
export PATH=/usr/local/ivorysql/ivorysql-1/bin:$PATH
EOF

セットアップ

initdb でデータベースインスタンスの初期データを作成します。Oracle互換機能を使うにはオプションに「--compatible-mode oracle」を指定します。デフォルトは互換モードではない「postgres」です。今のところ、すなわち IvorySQL 1.0 では、これは postgresql.conf の設定 compatible_mode の初期値を与えているだけです。それ以外のオプションは PostgreSQL の initdb と同じです。

$ initdb -D $PGDATA -U ivorysql --locale=C --encoding=UTF8 --compatible-mode oracle

pg_ctl コマンドでサービスを起動します。pg_ctl コマンドの使い方も PostgreSQL と同じです。

$ pg_ctl -D $PGDATA -l $PGDATA/start.log start

createdbコマンドでテスト用データベース ivdb1 を作って、psqlコマンドでデータベースに接続します。この辺りも PostgreSQL と変わるところがありません。

$ createdb ivdb1
$ psql ivdb1
psql (14.0)
"help"でヘルプを表示します。

ivdb1=#

最初に行うべきことは orafce拡張の導入です。IvorySQL の Oracle互換機能のいくつかは orafce の中で提供されています。（→ v1.1 からは orafce は自動でロードされるようになっています）

ivdb1=# CREATE EXTENSION orafce;

また、orafce で提供されるデータ型や関数、演算子などの互換オブジェクトをスキーマ修飾無しに使えるようにするため、search_path設定に oracleスキーマを加えます。pg_catalog よりも手前に置くことで、同名の組み込みオブジェクトよりも Oracle互換オブジェクトが優先して使われます。ここではデータベースに対して search_path のデフォルト値として設定することにします。また、ユーザと同名のivorysqlスキーマも作成しておきます。

ivdb1=# SET search_path TO "$user", oracle, pg_catalog, public;
ivdb1=# ALTER DATABASE ivdb1 SET search_path TO "$user", oracle, pg_catalog, public;
ivdb1=# CREATE SCHEMA ivorysql;

なお、github での開発者のコメントによると IvorySQL 2.0 では、compatible_mode = oracle であれば search_path の変更をしなくとも互換オブジェクトが使われるようにするようです。

ここまでの手順で IvorySQL を使用する準備ができました。

Oracle互換機能

IvorySQL には様々な Oracle互換機能が含まれています。ドキュメントに記載されている IvorySQL 独自の互換機能の他、orafce で提供されている機能もあります。

本節では IvorySQL で提供されている Oracle互換機能の中から主なものを見ていきます。

パッケージ

IvorySQL の最も有力な機能がパッケージのサポートです。パッケージとはストアドプロシージャや関数、データ型、変数、カーソルをまとめたものです。

パッケージは、名前空間を区切って機能の整理を良くするのみならず、特有の振る舞いを持ちます。このためネイティブの PostgreSQL の機能だけを使って移行しようとすると、同じ動作が再現できず、しばしば移行の難所となります。

同一セッション内の同一パッケージ内において、関数とプロシージャからカーソルと変数に共有してアクセスが可能で、これらに対する操作はコミット／ロールバックの影響を受けないという振る舞いをします。

いくつかサンプルを示します。コード中のコメントに説明を記載しています。

$ cat pkg1.sql

CREATE OR REPLACE PACKAGE pkg1 AUTHID DEFINER IS -- パッケージ宣言
  PROCEDURE f_plus(p1 integer);    -- 変数に引数値を加えるプロシージャ
  FUNCTION f_incr RETURN integer;  -- 変数に 1 を足して、値を返す関数
END;

CREATE OR REPLACE PACKAGE BODY pkg1 AS           -- パッケージ本体定義
  v_num integer;    -- パッケージ変数／直接アクセスはできない
  PROCEDURE f_plus(p1 integer) IS
    BEGIN
      v_num := v_num + p1;
    END;
  FUNCTION f_incr RETURN integer IS
    BEGIN
      v_num := v_num + 1;  
      RETURN v_num;
    END;
  BEGIN             -- セッション中、最初の使用時に実行されるブロック
    v_num := 0;     -- パッケージ変数を初期化
  END;

上記のパッケージを読み込んで使用します。

ivdb1=# \i pkg1.sql

ivdb1=# SELECT pkg1.f_incr;
 f_incr
--------
 1
(1 行)

ivdb1=# BEGIN;
ivdb1=*# SELECT pkg1.f_incr;
 f_incr
--------
 2
(1 行)

ivdb1=*# CALL pkg1.f_plus(10);
ivdb1=*# SELECT pkg1.f_incr;
 f_incr
--------
 13
(1 行)

ivdb1=*# ROLLBACK;

ivdb1=# SELECT pkg1.f_incr;
 f_incr
--------
 14
(1 行)

パッケージ変数が関数やプロシージャの間で共有されていることと、明示的トランザクション内でのパッケージ変数への操作が ROLLBACK されても取り消されていないことが、確認できます。

次はカーソルを使った例です。

$ cat pkg2.sql

-- 操作する 10行データを持つテーブルを用意
DROP TABLE IF EXISTS tq;
CREATE TABLE tq (id int, v text);
INSERT INTO tq SELECT g, md5(g::text) FROM generate_series(1, 10) g;

CREATE OR REPLACE PACKAGE pkg2 AUTHID DEFINER IS -- パッケージ宣言
  CURSOR cur1 RETURN tq%ROWTYPE; -- (カーソル戻り値は %ROWTYPE 必須)
  FUNCTION f_next RETURN text; -- カーソルの次の行を返す関数
  FUNCTION f_prev RETURN text; -- カーソルの前の行を返す関数
END;

CREATE OR REPLACE PACKAGE BODY pkg2 IS -- パッケージ本体定義
  CURSOR cur1 RETURN tq%ROWTYPE IS SELECT id, v FROM tq ORDER BY id;
  FUNCTION f_next RETURN text IS
    rec tq%ROWTYPE;
    BEGIN
      FETCH cur1 INTO rec;
      RETURN rec.id || ': ' || rec.v;
    END;
  FUNCTION f_prev RETURN text IS
    rec tq%ROWTYPE;
    BEGIN
      FETCH PRIOR IN cur1 INTO rec;
      RETURN rec.id || ': ' || rec.v;
    END;
  BEGIN
    OPEN cur1;
  END;

上記を読み込みして実行してみます。

ivdb1=# \i pkg2.sql

ivdb1=# SELECT ivorysql.pkg2.f_next;
       f_next
-------------------------------------
 1: c4ca4238a0b923820dcc509a6f75849b
(1 行)

ivdb1=# SELECT ivorysql.pkg2.f_next;
       f_next
-------------------------------------
 2: c81e728d9d4c2f636f067f89cc14862c
(1 行)

ivdb1=# SELECT ivorysql.pkg2.f_next;
       f_next
-------------------------------------
 3: eccbc87e4b5ce2fe28308fd9f2a7baf3
(1 行)

ivdb1=# SELECT ivorysql.pkg2.f_prev;
       f_prev
-------------------------------------
 2: c81e728d9d4c2f636f067f89cc14862c
(1 行)

カーソルを複数の関数から共有している動作が確認できます。上記例では行っていませんが、明示的なトランザクション内の関数実行をロールバックしても、カーソルの位置は変わりません。

また、上記例ではスキーマ名の修飾を加えて、パッケージ関数を呼び出しています。パッケージはスキーマに属するオブジェクトです。

パッケージ内の関数やプロシージャは plisql（PL/iSQL）という手続き言語で記述します。今のところこれはパッケージに対応している以外は PL/pgSQL とほぼ同じもので、PL/SQL で書かれたコードはそのままでは動作しません。

定義されているパッケージの一覧は以下の問い合わせで確認できます。今のところ psql の \d コマンドはパッケージに対応していません。

ivdb1=# SELECT pkgname, pkgnamespace::regnamespace, pkgowner::regrole
          FROM pg_package;
 pkgname | pkgnamespace | pkgowner
---------+--------------+----------
 pkg2    | ivorysql     | ivorysql
 pkg1    | ivorysql     | ivorysql
(2 行)

また、1.0バージョンではパッケージの宣言と定義を psql のプロンプトで直接入力するとうまく動作しません。ファイルから読み込む形で使用する必要がありました。

UPDATE、DELETE の互換構文サポート

IvorySQL は UPDATE文、DELETE文における、Oracle 特有の構文をサポートしています。

UPDATE文で以下のように更新対象テーブルの別名が指定できます。

ivdb1=# CREATE TABLE t1 (id int, v text);
ivdb1=# INSERT INTO t1 VALUES (1, 'AA'), (2, 'BB'), (3, 'CC');

ivdb1=# UPDATE t1 a SET a.v = 'XX' WHERE a.id = 1;

また、DELETE文で以下のように FROM を省略できます。

ivdb1=# DELETE t1 WHERE id = 2;

これらは compatible_mode設定が oracle ではなく postgres であっても、動作します。

シーケンスの互換構文サポート

IvorySQL はシーケンス操作について、Oracle の構文をサポートしています。PostgreSQL の書き方も引き続き利用可能です。

ivdb1=# CREATE SEQUENCE seq1;

ivdb1=# SELECT seq1.nextval;
 nextval
---------
 1
(1 row)

ivdb1=# SELECT seq1.currval;
 currval
---------
 1
(1 行)

ivdb1=# SELECT nextval('seq1');
 nextval
---------
 2
(1 行)

こちらも compatible_mode設定に関わりなく、常にこのように動作します。

バイト単位文字数のサポート

文字列型に関する Oracle と PostgreSQL の違いは、Oracle では NCHAR、NVARCHAR2、VARCHAR2 という PostgreSQL には無いデータ型が使われることと、データ型に指定する文字列長の単位がデフォルトではバイト単位（PostgreSQL では必ず文字単位）であるということです。

IvorySQL では、orafce拡張により VARCHAR2型と NVARCHAR2型が提供されます。また、設定nls_length_semantics に char または byte を設定することで、文字列長の単位を文字単位またはバイト単位に指定できます。

単体配布されている orafce でも VARCHAR2型は提供されますが、以下のようにバイト単位での上限を超えた文字を切り捨てる動作になっていました。エラーになるのが Oracle互換の動作です。

orafce=# SELECT VARCHAR2(10) 'あいうえお';
 varchar2
----------
 あいう
(1 行)

（本動作は v1.1 で IvorySQL動作も orafce同様に文字を切り捨てる動作の方に変更されました）

IvorySQL で nls_length_semantics = byte を指定すると、バイト単位文字列長を超えたときには以下のようにエラーになります。また、NVARCHAR2型を使った場合、これは文字列長が必ず文字単位になるデータ型ですので、エラーになりません。

ivdb1=# SET nls_length_semantics TO byte;
ivdb1=# SELECT VARCHAR2(10) 'あいうえお';
ERROR: value too long for type varchar2(10 byte)

ivdb1=# SELECT NVARCHAR2(10) 'あいうえお';
 nvarchar2
------------
 あいうえお
(1 行)

nls_length_semantics設定は CHAR型、VARCHAR型の動作にも同様に影響します。

一方で nls_length_semantics = byte のときに、必ず文字単位であるはずの NCHAR型においてもバイト単位として扱われてしまいます。これは、IvorySQL や orafce による拡張が無くとも、PostgreSQL において NCHAR型が CHAR型の別名として受け入れられていることに起因すると考えられます。今後 IvorySQL が明示的に NCHAR型をサポートすることで、このような不整合が解消されることが望まれます。

対応していないOracle互換動作

本節では IvorySQL 1.0 ではサポートされていない、いくつかの機能について記載します。

NUMBERデータ型

IvorySQL のドキュメント上のサンプルではしばしば NUMBER型が登場しますが、今のところこれは IvorySQL 1.0 では提供されません。ネイティブの PostgreSQL に移植するときのように NUMERIC、integer、bigint などのいずれか数値データ型を選んで NUMBER型の代わりに使用する必要があります。

また、NUMERIC型を使う場合には注意が必要です。oracle互換のデータ型や演算子を透過的に使うために本例では search_path設定に oracleスキーマを含めていましたが、そうしていると数値リテラルを使った式で、使用する演算子を特定できずに以下のようなエラーを起こします。

ivdb1=# CREATE TABLE t_num (numcol numeric, v text);
ivdb1=# INSERT INTO t_num VALUES (1, 'AAA'), (2, 'BBB'), (3, 'CCC');

ivdb1=# SELECT * FROM t_num WHERE numcol = 1;
ERROR: operator is not unique: numeric = integer

ivdb1=# SET search_path TO "$user", public; -- search_path から oracle を外せば動作
ivdb1=# SELECT * FROM t_num WHERE numcol = 1;
 numcol | v
--------+-----
      1 | AAA
(1 行)

これは numcol = 1 ではなく numcol = '1' と書くか、数値部分をプリペアドステートメントのパラメータとして渡す限りは問題となりません。

NUMBER型は、開発中バージョンのドキュメントには対応型として明示的に記載があり、今後のバージョンで追加される予定のようです。（→ こちらは 1.1バージョンで追加されました。）

NULL と 空文字列の同一視

Oracle特有の振る舞いに、文字列データ型において NULL と空文字列（”）を同一視するということがあります。文字列連結では NULL が空文字列として扱われる一方、その他の文字列関数や演算子、NOT NULL制約では空文字列が NULL として扱われます。

この非互換性について IvorySQL固有のサポートは無く、ドキュメントにも言及がありません。しかしながら、同梱の orafce拡張に含まれる関数や演算子を通していくらかの互換動作が利用可能です。
例えば、orafce.varchar2_null_safe_concat設定を on にすることで、NULL値を含む VARCHAR2型の連結を以下のように実行できます。orafce.varchar2_null_safe_concat = off なら、この SELECT結果は NULL です。

ivdb1=# CREATE TABLE t_concat (c1 varchar2, c2 varchar2);
ivdb1=# INSERT INTO t_concat VALUES ('ABC', NULL);
ivdb1=# SET orafce.varchar2_null_safe_concat TO on;

ivdb1=# SELECT '{' || c1 || c2 || '}' as concat FROM t_concat;
 concat
--------
 {ABC}
(1 row)

一方で orafce で提供される oracle互換版の文字列処理関数であっても、「SELECT length('') FROM dual」が NULL になる、といった互換動作にはなりません（0になります）。また、NOT NULL制約に空文字列が投入不能、という動作にもなりません。

まとめ

IvorySQL にはここでは紹介できなかった互換機能も多数含まれています。見てきた通り、魅力的な互換機能を有する一方、まだまだ発展途上の部分も多くあるソフトウェアといえます。

オープンソースソフトウェアであることに加え、ネイティブ PostgreSQL に対する拡張モジュールではなく、データベース全体として提供される形態であるため、新たな互換機能の追加に仕組み上の制限が無いという点は魅力的です。今後の発展に期待したいところです。

IvorySQL 1.1

2022年 1月 25日に IvorySQL 1.1 がリリースされました。以下の機能追加と修正が適用されており、本稿で問題として挙げた点のいくつかは既に改善されています。

• NUMBER型のサポート
• orafceの自動ロード
• Oracle互換モードではsearch_path設定をしなくとも自動的に互換オブジェクトを使用
• バイト単位文字数のサポートについて挙動が変更されました
• そのままでは動作しないドキュメント上のサンプルを修正