OracleからAzure DB（PostgreSQL）へ移行する~⑧OracleとPostgreSQLの違い、知っておきたい12のこと

Oracle DatabaseからPostgreSQLへマイグレーションを行う際に大事な両者の違いについて記載します。

1.ストレージアーキテクチャの違い

• Oracle Databaseは「更新型」のアーキテクチャ
  Oracle　Databaseは更新型のアーキテクチャです。以下の図の通り、Data2の内容をアップデートする時、そのままデータを更新しに行くイメージとなります。
  

• PostgreSQLは「追記型」のアーキテクチャ
  PostgreSQLは追記型のアーキテクチャです。Data2をアップデートすることは内部的にはData2に削除フラグを立てて、参照不可にし、新しいData2の値を新規レコードとして追加するイメージになります。
  削除フラグがたったData2に関しては、バキューム処理で物理削除だったり、論理削除を行う仕様になっています。
  

2.トランザクションの違い

PostgreSQLは自動コミット

Oracle Databaseを使い慣れているエンジニアにとって中々慣れないのがPostgreSQLは自動コミットという点です。Oracle Databaseの場合、Insert、Delete、UpdateなどのDMLを実行した後に、明示的にCOMMIT打たないと変更を反映されず、さらに変更を取り消したい場合もROLLBACKを明示的に必要とします。一方でPostgreSQLはDMLを実行したらすぐに変更が反映される自動コミットとなります。
トランザクションとして実行したい場合はBEGINにより明示的にトランザクションを開始、トランザクションをコミットする場合はENDもしくはCOMMIT、ロールバックさせるにはROLLBACKの実行が必要です。

PostgreSQLではDDLもロールバックが可能

Oracle DatabaseはDDLは暗黙コミットが行われます。PostgreSQLでは暗黙コミットは実行されないため、トランザクション中のCREATE TABLEのようなDDLもロールバックすることが可能となります。

トランザクション中のエラーの制御

Oracle DatabaseではいくつかのDMLを実行し途中でエラーを出しても、最後にCOMMITを行うと正常に実行できたDMLについて処理が確定されます。PostgreSQLではBEGINの後に１度でもエラーを出すと最後にCOMMITをしても、ROLLBACKしたのと同じ扱いとなります。

3.定義の違い（データ型の指定）

Oracle DatabaseとPostgreSQLではデータ型が異なります。Oracle Databaseは数値型はNumberですが、PostgreSQLでは数値型は整数型のINTと小数も扱えるNUMERIC型に分かれます。文字型や日付型、バイナリ型など、型に関しては微妙にOracle DatabaseとPostgreSQLでは異なります。
各々で対応するデータ型も存在する為、移行ツールなどで判定を行う事で対応がある程度可能です。

4.連続値生成

連続した数字の生成において、Oracle DatabaseとPostgreSQL両方に順序（シーケンス）と自動インクリメント列が存在します。ただし表記の方法が微妙に違います。

	順序	自動インクリメント
Oracle	CREATE SEQUENCE deptseq START WITH 1 INCREMENT BY 1;	CREATE TABLE test (testno NUMBER GENERATED ALWAYS AS IDENTITY);
PostgreSQL	CREATE SEQUENCE deptseq START WITH 1 INCREMENT BY 1;	CREATE TABLE test (testno SERIAL);

5.シノニムの有無

Oracle Databaseではシノニムは存在していますが、PostgreSQLではシノニムは存在しません。なのでOracle DatabaseのシノニムはPostgreSQLではVIEWに置換する必要があります。

Oracle Database	パブリックシノニム/プライベートシノニム
PostgreSQL	シノニムは存在しない

6.パッケージ、プロシージャ

PostgreSQLではパッケージは存在しません。一方で、プロシージャは、PL/pgSQL、PL/Python、PL/Perlなど様々なプロシージャが存在しております。なので、Oracle Databaseのプロシージャはこれらに書き換えることが可能です。

	パッケージ	プロシージャ
Oracle Database	存在する	存在する
PostgreSQL	存在しない	存在する(PL/pgSQL, PL/Python, PL/Tcl, PL/Perl)

ただし、Oracle Databaseのプロシージャに比べてPostgreSQLのプロシージャの方がロジック実行は遅いと言われています。ループ処理を書かずに、SQL一括処理を目指して性能改善する必要があります。

7.組み込み関数

Oracle DatabaseとPostgreSQLでは組み込み関数に違いがあります。Oracle Databaseでは利用できていた組み込み関数が、移行先のPostgreSQLでは存在しないこともあるため注意が必要です。（存在しない組み込み関数は同等の機能を実現する独自ファンクションを定義する必要があります。）

orafceを導入することでPostgreSQL内にOracle Databaseと互換性のある関数を実装することが可能。（すべてではありません。）

8.外部結合

Oracle Database独自記法として外部結合は(+)を使用しています。（一方Oracle Databaseでは9i以降この表記は非推奨。）
PostgreSQLでは（＋）が存在しなので、[LEFT/RIGHT] OUTER JOINに変更する必要があります。

例①

--Oracle Databaseの場合
SELECT d.deptno, d.dname, e.empno, e.ename, e.comm
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
AND e.comm(+) = 300;

--PostgreSQLの場合
SELECT d.deptno, d.dname, e.empno, e.ename, e.comm
FROM scott.emp e
    RIGHT OUTER JOIN scott.dept d ON (e.deptno = d.deptno AND e.comm = 300);

例②

--Oracle Databaseの場合
SELECT d.deptno, d.dname, e.empno, e.ename, e.comm
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
AND e.comm = 300;

--PostgreSQLの場合
SELECT d.deptno, d.dname, e.empno, e.ename, e.comm
FROM scott.emp e
    RIGHT OUTER JOIN scott.dept d
        ON (e.deptno = d.deptno)
WHERE e.comm = 300;

9.行レベルロック

一般的に業務ロジック上では共用による不整合を回避するためにSELECT FOR UPDATEが存在しています。これはOracle DatabaseとPostgreSQLでも同様に存在し、SELECT FOR UPDATEを実行して、Oracle DatabaseとPostgreSQLは同じように行レベルロックを取得できます。ただし、Oracle DatabaseにおいてはFOR UPDATE句としてWAIT n、NOWAITが存在しますが、PostgreSQLではFOR UPDATE句は存在するものの、WAIT n、NOWAITが存在しません（ロック解除まで無限に待機）。パラメータlock_timeoutを指定することで、設定値以上の待機によりエラーとなるようにすることは可能なので、このパラメータで調整する必要があります。（このパラメータはセッションレベルでの変更も可能）

10.ヒント句

Oracle Databaseにはヒント句が存在しますが、PostgreSQLではヒント句は存在しません。（拡張機能として「pg_hint_plan」が存在するがOracle Databaseほどの種類はない）
※pg_hint_planはAzure Database for PostgreSQLには存在しない。　

11.NULL

Oracle DatabaseではNULLと空文字は同じもの（NULL）として扱われます。
PostgreSQLではNULLと空文字は別物として扱われます。この為、Oracle DatabaseからPostgreSQLへ移行する場合、NULLあるいは空文字のどちらかの利用の禁止等の基準を設けることが推奨となります。NULLの四則演算、NULLとの文字列結合の場合、すべて結果がNULLとなるため、NULLが格納される可能性がある列を取り扱う場合、COALESCE関数（Oracle DatabaseでいうNVL関数）を使うなどの工夫が必要となる。

12.MERGE

Oracle DatabaseではMERGE文（あればUPDATEなければINSERT）が存在するが、PostgreSQLはMERGE文は存在しない。一方でUPSERT（INSERT CONFRICT)が存在するのでこちらで代用する。

最後に

Oracle DatabaseとPostgreSQLの違いに関して、移行際に抑えておきたいポイントに関しては皆さんに紹介できたのかなと思っております。

次回は

Azure Database for PostgreSQLの機能の紹介を行いたいと思います。