ことの経緯
よくあるOracleからPostgreSQLへの載せ替え案件の支援をしている中で、PLSQLをコンバージョンしよう！
というのが発端。
自動変換ツール（AWS SCT）を用いたものの、痒い所に手が届いていないところだったり、手動修正を余儀なくされたポイントだったりがいくつかあったので、備忘としてまとめておこうかな…というのが今回の経緯です。
SCTのOracle -> PostgreSQL
AWS SCTくんはDBエンジンが変わることによって、考慮しなければならない部分を自動で変換してくれる便利ツールです。しかも無料です。（この書き方するとどこかのOSっぽくなる）
今回のケースであれば、「Oracleではこう書いてるけど、PostgreSQLではこう書くんやで！」と直してくれるわけですね。
また、手動変換や注意が必要とされる自動変換部分については「ここは確認してね！」というレポートの一覧を出してくれるので、開発者はそれらの一覧を見ながら問題が発生している箇所を確認して修正していくことができます。
SCT自体の使い方はググったら記事が結構出てくるのでここでは紹介しません。
むしろ問題はSCTで変換した後の確認ポイントで、どのサイトを見てもふんわりとした表現でしか修正方法についての記載がないので具体的にどう対処したらええねん、という部分で結構時間を取られた気がします。
文字列連結の変換(PostgreSQLでパイプ文字連結をするな）
Oracleの場合、文字列の結合時にパイプ文字を使用した際、nullが含まれていても挙動的には問題ありません。
が、Postgresqlの場合はnullが含まれた場合は結合結果の文字自体がnullになるので、CONCAT関数を用いて修正します。
SAMPLE_STR = 'hoge' || var_fuga || 'piyo'; -- Oracle
CONCAT_WS('','hoge', var_fuga, 'piyo'); -- SCT:Oracle to PostgreSQL
CONCAT('hoge', var_fuga, 'piyo'); -- たぶん手で直すならこのケースのほうが多そう
CONCAT_WS(',', 'hoge', var_fuga, 'piyo'); -- カンマ区切りならこう
文字列連結のnull問題をCOALESCE関数を用いて力業で解決する方法もあるにはあるのですが、可読性としてはパイプ文字連結よりもむしろ読みやすくなるのでCONCAT関数、またはCONCAT_WS関数の利用が推奨されるかと思います。
| 関数 | 説明 |
|---|---|
| concat(str "any" [, str "any" [, ...] ]) | すべての引数の結合。ただしNULLは無視される。 |
| concat_ws(sep text, str "any" [, str "any" [, ...] ]) | 第一引数を区切り文字とし、残りのすべての引数を結合する。最初の引数は区切り文字列として使われる。NULLは無視される。 |
DATE関数変換
TO_DATEの関数自体はPostgreSQLにも存在するのですが、SCTくんはaws_oracle_extを活用して変換してくれます。
が、PostgreSQLでDATE型をTIMESTAMP型として変換した場合などで、そもそもTO_DATE関数自体を見直すべき可能性もあるかと思います。
ちなみにaws_oracle_extとはSCTくんがコンバージョン後もなんちゃってoracleとしてSQLを動かせるように用意してくれるSCTの拡張パックスキーマです。スキーマっていうよりは感覚的にはライブラリに近い。
TO_DATE('19000101', 'YYYYMMDD'); -- Oracle
aws_oracle_ext.TO_DATE('19000101', 'YYYY/MM/DD'); -- SCT:Oracle to PostgreSQL
to_timestamp('19000101', 'YYYY/MM/DD'); -- これでOKなパターンは多い気がする
aws_oracle_extのglobalVariable関連の関数がコケる
SCTでの自動変換時にしか発生しえない気がしますが、なんかコケまくった挙句、どうにも性能的にも悪そうだったので、結果としてはaws_oracle_extは利用せず手動で改修しました。
どうしてこんな作りに変換されてしまったのかといえば、おそらくOracleのパッケージの挙動を残すためなのだろうと思います。
同一パッケージ上で参照する変数をグローバル変数として変換し、他のファンクションに参照させる…という形を取ろうとするようです。
ちなみにこの関数、いくら検索してもリファレンスらしいものがヒットせず、結局どのように利用するのが正しいのかわからないままです……
配列変換
PostgreSQLでは配列が使えるので、配列的な処理をさせたい場合は手動で変換してあげたほうがいケースは結構ありそうだな、と思いました。
Oracleの場合は要素数の指定にINDEX BYを使うので、そのあたりも起因してなのかSCTで変換されたものはおかしな変換になりがちなような気がする。
ちなみに、INDEX BY VARCHAR(100)などの連想配列に関してはjsonなどへ変換せざるを得ないので、jsonへの変換も記載しておきます。
/* Oracle */
TYPE AR1 IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
CL_ARR AR1;
CL_ARR(0) := 'RED';
CL_ARR(1) := 'BLUE';
/* SCT:Oracle to PostgreSQL (エラーになる) */
aws_oracle_ext.array$()
aws_oracle_ext.array$setVal('RED')
aws_oracle_ext.array$setVal('BLUE')
-- Err
/* PostgreSQL */
cl_arr jsonb; -- 変数宣言
cl_arr := '["RED","BLUE","PINK"]'::jsonb;
cl_arr := cl_arr || '"GREEN"'::jsonb; -- 末尾に新しい要素を追加
cl_arr := cl_arr - 1; -- インデックスを指定して要素を削除
cl_arr := cl_arr - "PINK" -- 値を指定して要素を削除
RAISE NOTICE 'すべての要素', cl_arr;
-- NOTICE: すべての要素: ["RED", "GREEN"]
RAISE NOTICE 'インデックスを指定してアクセス', cl_arr ->> 0;
-- NOTICE: インデックスを指定してアクセス: RED
トランザクション問題
OracleからPostgreSQLへトランザクション処理を移行する際、基本的な構文は似ていますが、トランザクションの開始方法とDDLの扱いに重要な違いがあります。
Oracleは文が実行されると自動でトランザクションが始まりますが、PostgreSQLではBEGINで明示的に開始する必要があります。また、PostgreSQLでは一部の例外を除き、トランザクション内でCREATE TABLEのようなDDLを実行すると暗黙的なコミットが発生するか、エラーとなるため注意が必要です。
| 項目 | Oracle | PostgreSQL | 移行時の注意点 |
|---|---|---|---|
| トランザクションの開始 | 暗黙的に開始 |
BEGINまたはSTART TRANSACTIONで明示的に開始 |
PL/pgSQLのコードブロックの先頭にBEGINを記述する習慣をつける必要がある。 |
| エラー発生時の挙動 |
EXCEPTIONブロックで処理を継続できる場合がある |
ほとんどのエラーでトランザクション全体が中断状態になる | エラー発生後はROLLBACKが必須です。SAVEPOINTを使わない限り、部分的な処理継続はできない。 |
| DDLの扱い | トランザクション内でDDLを実行可能 | トランザクション内でDDLを実行できない (暗黙コミットまたはエラー) | DDLとDMLはトランザクションを分ける必要がある。 |
| 自律型トランザクション |
PRAGMA AUTONOMOUS_TRANSACTIONで実現 |
直接の代替機能なし |
dblink拡張機能などを使った回避策が必要。 |
空文字判定
OracleとPostgreSQLでは、空文字（''）とNULLの扱いに非常に重要な違いがあります。Oracleでは空文字はNULLとして扱われますが、PostgreSQLではこれらは明確に区別されます。
| 項目 | Oracleの挙動 | PostgreSQLの挙動 (標準SQL) |
|---|---|---|
空文字('')の扱い |
NULLとして扱われる |
長さ0の文字列として扱われる |
| 値のチェック方法 |
col IS NULL (空文字もNULLもこれでヒット) |
col IS NULL (NULLのみ)col = '' (空文字のみ) |
LENGTH関数の結果 |
LENGTH('') → NULL
|
LENGTH('') → 0
|
連結演算子(||) |
'A' || NULL → 'A' (NULLは無視される) |
'A' || NULL → NULL (NULLと連結すると結果もNULL) |
NOT NULL制約 |
空文字を挿入しようとするとNULLになるため、制約違反となる。 |
空文字はNULLではないため、制約違反とならず挿入できる。 |
チェック方法の差異
IF var_id IS NULL THEN XXX -- Oracle
IF var_id IS NULL OR var_id <> '' THEN XXX -- PostgreSQL
AWS SCT利用の際はレポートだけでなく様々な点に注意が必要
ここまでいくつかの注意点に触れてきましたが、この他にも性能面での劣化対策であったりDBの差異で注意すべき点はまだまだあります。以下にいくつかの注意点をまとめます。
- オブジェクト一覧
- ストアドプロシージャ
- 関数
- パッケージ（PostgreSQLには概念がない → 分割必要）
- トリガー
- シノニム
- シーケンス
- ヒント句の利用状況（PostgreSQLには基本的にない）
- 依存関係
- オブジェクト依存関係の抽出（SCTレポートで確認）
- クロススキーマ参照の確認
- DBリンク（→ FDW or Lambda+API に置換）
- データ方変換で問題が起きやすいポイント
- NUMBER(38,0) → NUMERIC(38) が遅い → BIGINT に置換可能か判断
- CHAR 固定長 → PostgreSQLは空白詰めに差異
- RAW → BYTEA
- COLLECTION（VARRAY / NESTED TABLE）→ JSONB or 別テーブル化
- ストアドの不可避の置き換え
- PACKAGES → Function/Procedure へ分解
- OUT/INOUT パラメータ仕様の差異
- EXCEPTION の書き換え
- CURSOR の扱いの変更
- RECORD 型 → composite type
- SYS_REFCURSOR → refcursor
- PLSQL 特有構文（%TYPE, %ROWTYPE）の扱い
- Oracle の BOOLEAN は SQL レベルで存在しないため注意
- DBMS_* パッケージの代替（DBMS_OUTPUT、DBMS_SCHEDULERなど）
- インデックス
- Oracle の BITMAP → PostgreSQL にはない（普通の index へ）
- Oracle の FUNCTION INDEX → PostgreSQL は expression index
- PK + Foreign Key の整合性
- 統計情報
- ANALYZE の設定
- autovacuum パラメータの調整
- work_mem / shared_buffers 調整
AWS SCTを活用する際はレポートの精査はもちろんですが上記の点に注意して移行を進める必要があります。
特に性能面の劣化は顕著に出るのでストアドのビジネスロジックの仕様変更なども含めて検討する必要性が発生することもあるので注意しましょう。