Qiita Engineer Festa20242024年7月17日まで開催中！

株式会社Works Human Intelligence

DB2（Oracle互換モード）からPostgreSQLのマイグレーションにめちゃくちゃ苦労した話

Posted at 2024-06-12

はじめに

本記事は数年前（当時は前職）に経験した、DB2（Oracle互換）資材をPostgreSQLにマイグレーションする過程で得た知見や苦労をまとめた記事となります。

DBMSをPostgreSQLに移行するためのノウハウは様々公開されているため、一つひとつを体系的に整理するのではなく、自身の経験を元に、より詳細な修正内容や、大変だった修正の体験談などを中心に記載しようと思います。

DBMSをPostgreSQLに移行する際、本記事が参考になれば幸いです。

前提

マイグレーションの概要について

PJTの詳細は割愛しますが、現行システムのソースにまるまる修正が入る対応のため、改修規模はかなり大きかったです。

改修規模は大きいですが、キーワードレベルで置換可能な構文は一括で置換（※）を行い、残りの置換できない範囲を手修正する形をとり、手修正の量を減らすことで工数削減を図っていました。

と言いつつも実際のところは、想定外の修正が数多く発生し、かつキーワードレベルでの置換で対応できない修正が多く存在したため、かなりの範囲を手修正しましたが・・。

※当時のPJTは独自に作成したツールを用いて、置換前後の正規表現を定義した設定ファイルとソースファイルを読み込ませ、設定ファイルの内容に基づいてソースファイルの一括置換を行っていました。

一例として、

Number型をNumeric型に置換する
DECODE関数をCASE式に置換する
CURRENT TIMESTAMPをCURRENT_TIMESTAMPに置換する
FROM DUALを削除する

などが挙げられます。

自身の担当内容

PostgreSQLへのマイグレーションにおける、当時の自身が担当内容は以下となります。

ストアドプロシージャの改修
- PL/SQL→PL/pgSQLに修正
- コアなビジネスロジックがストアドプロシージャでゴリゴリ実装されていたため、改修規模は大きい
  - 1度に合計50以上のプロシージャやファンクションが呼び出される処理など・・。かなり複雑なソースもあり。
SQLMAP（ibatisのFWで提供されているSQLやオブジェクトへのマッピングを記述するXMLファイル）の改修
- SQLMAPに定義されているSQLの構文をDB2（Oracle互換モード）→PostgreSQLに修正
- Javaの実装がシンプルな一方でSQLはかなり複雑な状態

今回の記事ではあくまでも私自身の経験したマイグレーションの部分を取り上げてお話するため、本記事ではSQL・ストアドプロシージャのマイグレーションがメインとなります。

PostgreSQLの移行事例

PostgreSQLの移行事例に関して、ネット上でいくつか公開されているので紹介いたします。

PostgreSQL　エンタープライズ・コンソーシアムのサイト

私がPJTに参画したタイミングですでにDB2（Oracle互換モード）資材をPostgreSQLへどのように変換するかの方針は内部資料（見積もり時にpostgreSQLの移行を検証した際の資料）として整理されていたため、PJT内部の資料に加えて必要に応じて上記のURLの資料を参考にして製造を進めた形となります。

その他、参考になりそうなサイト

苦労した点

ここからは、DB2（Oracle互換モード）からPostgreSQLのマイグレーションで苦労した点についてお話していこうと思います。

上記で紹介したサイトには載っていないような非常に細かい内容や、修正方針が記載されている項目の中でも、修正する立場として大変だったという体験談を中心に取り上げていきます。

サンプルコードとして記載するソースは基本的に、OracleとPostgreSQLで比較しています。（Oracleのソースに関してはタイトル記載にある「DB2（Oracle互換モード）」と同じ動作をする想定のため）

DB2（Oracle互換モード）固有の動作をする内容のみ、DB2（Oracle互換モード）のサンプルコードを記載して紹介したいと思います。あらかじめご了承ください。

空文字とNULLの扱い

Oracleでは空の文字列はNULLと同値として扱われる一方で、PostgreSQLにおいてはこれらは別の値として扱われます。
そのため、テーブルに空文字を登録する場合もOracleではNULLが登録される一方でPostgreSQLは空文字のまま登録されます。

この違いは調べるとすぐにたどり着けるような内容ですが、いざ修正すると非常に大変でした。
具体的にどのような点で大変だったか、具体例を挙げると以下となります。

IS NULL、IS NOT NULL演算子の比較対象の値の修正が必要
空文字に対するキャストに注意が必要
文字列結合演算子（||）からCONCAT関数への修正が必要
Java側のNULL判定が効かなくなるなど、修正が必要

`IS NULL`、`IS NOT NULL`演算子の比較対象の値の修正が必要について

空文字の値に関してIS NULL、IS NOT NULL演算子の結果がOracleとPostgreSQLで異なります。

Oracle

SQL> select
  2    case
  3      when '' is null     then 'NULLです'
  4      when '' is not null then 'NULLではない'
  5    end as NULL判定 from dual;

NULL判定
--------------------------------
NULLです

PostgreSQL（変更前）

testdb=# select
testdb-#   case
testdb-#     when '' is null     then 'NULLです'
testdb-#     when '' is not null then 'NULLではない'
testdb-#   end as NULL判定;
   null判定
--------------
 NULLではない
(1 行)

そのため、nullif関数などを利用して、空文字をNULLとして扱うように修正する必要があります。

PostgreSQL（変更後）

testdb=# select
testdb-#   case
testdb-#     when nullif('', '') is null     then 'NULLです'
testdb-#     when nullif('', '') is not null then 'NULLではない'
testdb-#   end as NULL判定 ;
 null判定
----------
 NULLです
(1 行)

修正自体は難しくなさそうに見えますが、実際に修正する立場になると、

利用箇所が膨大である
一括での置換が難しい（Number→Numericのような単純な置換でない）
修正が漏れていたとしても構文エラーとならず動作してしまうため、動作確認時に見落とされがち

という点で、全ソースを漏れなく修正しきるのは骨が折れます。

結局、後工程で修正漏れが見つかったため、Grepして愚直に横展開をすることになりました・・。

空文字に対するキャストに注意が必要について

PostgreSQLで空文字に対してキャストを行う場合、一部のデータ型（※）においてはエラーが発生します。
※numericなどの数値データ型、dateなどの日付／時刻データ型、booleanなど

一方、Oracleの場合はNULLとして扱われるため、NULLが返却されます。

oracle

SQL> select cast('' as numeric(5, 0)) as 数値型にキャスト from dual;
数値型にキャスト
----------------

PostgreSQL

testdb=# select cast('' as numeric) as 数値型にキャスト;
ERROR:  "numeric"型の入力構文が不正です: ""
行 1: select cast('' as numeric(5, 0)) as 数値型にキャスト;

上記に関しても、PostgreSQLにおいてはnullif関数などを使用して、空文字をNULLに変換してからキャストを行う必要があります。

IS NULL、IS NOT NULL演算子とは異なり、実行時にエラーとなるため、エラーの検知は容易です。しかし、当時の動作確認や単体テストでは、レコードの値やPL/pgSQLの変数の値が空文字であるデータでのテストができていなかったため、この問題を検知するのは単体テストの終盤になってからでした。

この問題も機械的な置換が難しく、修正する必要がある箇所が非常に多いため、製造メンバーの作業を止めて人海戦術で横並び対応をすることに・・。

文字列結合演算子（`||`）から`CONCAT関数`への修正が必要について

これは上記2つと若干意味合いが異なりますが、文字列結合演算子（||）を利用する際、PostgreSQLにおいて、結合対象の値にNULLが含まれている場合、演算の結果自体がNULLで返却されます。
OracleではNULLの値は無視され（※）、演算の結果自体がNULLとなりません。

※OracleのSQL言語リファレンスの内容を確認すると、厳密には片方のみNULLの場合はもう一方の値を返すという旨の記載があります。

Oracleは、長さが0(ゼロ)の文字列をNULLとして処理しますが、長さが0(ゼロ)の文字列を別のオペランドと連結すると、その結果は常にもう一方のオペランドになります。結果がNULLになるのは、2つのNULL文字列を連結したときのみです。ただし、この処理はOracle Databaseの今後のバージョンでも継続されるとはかぎりません。nullになる可能性のある式を連結する場合は、NVL関数を使用してその式を長さ0の文字列に明示的に変換してください。

この差分を解消するためには、文字列結合演算子（||）からCONCAT関数への修正が必要となります。

oracle

SQL> select 'Oracle ' || NULL || ' PostgreSQL' AS 文字列結合結果 from dual;

文字列結合結果
------------------------------------
Oracle  PostgreSQL

PostgreSQL（変更前）

testdb=# select 'Oracle ' || NULL || ' PostgreSQL' AS 文字列結合結果;
 文字列結合結果
----------------

(1 行)

PostgreSQL（変更後）

testdb=# select concat('Oracle ' , NULL , ' PostgreSQL') AS 文字列結合結果;
 文字列結合結果
----------------
Oracle  PostgreSQL
(1 行)

上記の場合、修正は比較的容易ですが、現行システムではSQLの実行結果やPL/pgSQLの処理内でログメッセージを成形するため、カラムの値や変数、文字列を結合する処理が多くありました。これにより、構文が複雑になり修正作業が手間を要することが多かったです。

何回、concat関数の閉じ括弧（)）の位置を間違えて構文エラーが発生したか・・。

修正に手間がかかるパターンの例（実際のソースはより複雑です）

testdb=# select
testdb-#     lpad(t1.empno, 3, ' ') ||
testdb-#     ',' || lpad(t1.empname, 40, ' ') ||
testdb-#     ',' || lpad(( select t2.posname from pos t2 where t1.poscode = t2.poscode), 20, ' ')  AS 社員データ
testdb-# from emp t1 limit 1;
                               社員データ
-------------------------------------------------------------------------
   1,                                    営業部長,                  部長

※DB2はPostgreSQLと同様に、結合対象の値にNULLが含まれている場合、演算の結果自体がNULLで返却されるため、Oralce互換モード固有の動作の認識です。
DB2における文字列結合の動作に関しては、新卒１年目の時にあんな記事やこんな記事を書いていたな・・。

Java側のNULL判定が効かなくなるなど、修正が必要について

現行システムのソースにおいて、ibatisでSQLを実行して取得したデータ対して、NULL判定を行う処理がありました。
このソースがPostgreSQLとなり、空文字とNULLが区別される状態となったため、空文字のデータが存在する場合にNULL判定をすり抜け、現行システムと動作が異なる事象を検知しました。

サーバーサイド側でSQLの実行結果に空文字が含まれることを想定しない実装となっていたため、JavaでDBアクセスに関するクラスに対して、以下の対応を実施しました。

SQLを実行してデータを取得する際に、空文字の場合はNULLに変換して取得する
SQLにパラメーターを渡す際に、空文字の場合はNULLに変換して値を渡す

これは共通処理を修正することで対応が可能であり、これまでの内容に比較して修正工数は小さいですが、SQLやPL/pgSQLだけでなく、Javaなど、SQLの呼び出し元にも影響のある可能性があるという点に留意頂ければと思います。

どうすればよかったのか

空文字とNULLの扱いについてかなり苦労しました。
できた取り組み・できなかった取り組みそれぞれありますが、まとめると以下の点に気を付ける必要があると思います。

データをNULLで扱い、空文字・NULLの違いによるリスクを減らす

移行データに空文字が存在する場合はNULLに修正して移行する
INSERT・UPDATEする値に空文字が存在する場合はNULLに修正する
- べた書きの空文字は容易に修正可能
- 一方で、Trim関数でスペースを削除する際、値がオールスペースの場合に空文字となる、というケースに注意する必要がある
SQLを実行してデータを取得する際に、空文字の場合はNULLに変換して取得する
SQLにパラメーターを渡す際に、空文字の場合はNULLに変換して値を渡す

空文字の場合でも問題が発生しないよう、漏れなく修正する仕組みを作る

製造時のチェックリストを作成し、今回のケースを取り込む
- 製造時のセルフレビュー、レビュアーのレビュー時に活用
単体テストのデータパターン観点に、空文字・NULLの値を取り入れる
- 観点が取り込まれているかはレビューで担保する
結合テスト以降で不具合を検知したら全ソース対象に横並びを実施する
- 単体テストで空文字・NULLのデータパターンの試験をしているのであれば、単体テストでは横並びは必ずしも必要でない認識（テストで検知できるので単体テストで横並びを行うと余分に工数がかかる）

暗黙的な型変換

Oracleでは暗黙的な型変換（※）が積極的に行われている一方で、PostgreSQLは暗黙的な型変換に関して消極的です。
※暗黙的な型変換のパターンはOracleのリファレンス参照

一例ですが、暗黙的な型変換が行われない場合、Where句での絞り込みなど文字列型と数値型で比較するケースにおいてエラーが発生します。

PostgreSQL

testdb=# select * from dept;
 deptno | deptname
--------+----------
 0      | 営業部
 1      | 開発部
 2      | 人事部
(3 行)
testdb=# select * from dept where deptno = 1;
ERROR:  演算子が存在しません: character = integer
行 1: select * from dept where deptno = 1;
                                      ^
HINT:  指定した名称と引数の型に合う演算子がありません。明示的な型キャストが必要かもしれません。

暗黙の型変換対応も非常に大変であり、具体例としては以下が挙げられます。

LPADやSUBSTRなどの標準関数、ユーザー定義関数、プロシージャを呼び出す際、引数のデータ型不一致によるエラーが発生
WHRER句や外部結合のON句で値を比較する際にデータ型不一致によるエラーが発生
Java側の修正が必要
不具合発生時の横展開対応が困難

`LPAD`や`SUBSTR`などの標準関数、ユーザー定義関数、プロシージャを呼び出す際に、引数のデータ型不一致によるエラーが発生について

LPADやSUBSTRなどの標準関数の多くはOracle・PostgreSQLどちらにおいても利用可能です。
そのため、マイグレーションを開始した当初、標準関数の修正はPostgreSQLで対応していないOracleの標準関数に対して発生する認識でしたが、暗黙的な型変換の違いにより、PostgreSQLで利用できる標準関数にも修正が発生することになりました。

PostgreSQLでも利用できるから修正工数はかからない。という安易な判断はしないほうが良いです。

Oracle

SQL> select lpad(123, '6', 'A') from dual;
LPAD(123,'6'
------------
AAA123

PostgreSQL

testdb=# select lpad(123, '6', 'A');
ERROR:  関数lpad(integer, unknown, unknown)は存在しません
行 1: select lpad(123, '6', 'A');
             ^
HINT:  指定した名前と引数型に合致する関数がありません。明示的な型変換が必要かもしれません。

WHRER句や外部結合のON句で値を比較する際にデータ型不一致によるエラーが発生について

これはタイトルの通りなのですが、私が担当していた現行システムは、同じカラム名でもテーブルにより、数値型と文字列型のカラムになっているものが混在していたため、それぞれのテーブルを結合している個所は軒並みエラーとなりました。

そのような背景もあり、今回のマイグレーションではこのパターンによる修正も数多く発生しました。

Java側の修正について

Java側からSQLMAPにString型のパラメータを渡す際、パラメータの利用箇所（関数の引数、WHRER句の比較対象の値）がnumeric型などの数値型の場合、データ型不一致によるエラーが発生します。
現行システムのソースは基本的にString型の値をSQLMAPに渡していたため、SQLのみ修正した状態で画面の動作確認を行うとデータ型不一致によるエラーが多発し、まともに疎通ができない状態でした。

SQLMAPに渡すパラメータの値を数値型に変更する必要があったため、String型→BigDecimal型に安全に型変換を行う共通関数を作成して、パラメータを渡す際にBigDecimal型へ変換するという対応を行いました。（※）

※SQLMAP側で定義されているSQLに対して、数値型にキャストする処理を追加すればJava側の改修は不要となりすが、パラメータをSQLMAPに渡すタイミングで正しい型にするのがあるべき姿だろうと判断し、Java側の改修に至りました。

ただし、今となってはibatisの仕組み（TypeHandlerなど）を活用して、SQLMAP側に閉じたほうが改修量は抑えられたのかなと思っています・・。

不具合発生時の横展開対応が困難について

暗黙的な型変換は他の修正方針と比較して修正箇所の全容を洗い出すのが困難です。
空文字とNULLの扱いの話では、一括の置換が困難という話をしましたが、IS NULL、IS NOT NULLなどでGrepすることで、修正箇所の特定自体は可能でした。

一方、暗黙的な型変換の場合は呼び出し元の引数の型、変数の値、カラムのデータ型、Javaから渡されるパラメータの値など、意識する点が多くあるため、単純にGrepして特定するというのは難しいです。

不具合が発生した同一ファイル内範囲での横並び、同一カラム名での横並びはある程度可能ですが、全容を洗い出すというのは困難であり、出たとこ勝負というのが正直なところでした。

~~逆に暗黙の型変換をPostgreSQLにも導入してみるという案を提案してみましたが却下されました。~~

一時表（TEMPORARY TABLE）

Oracleにおいて一時表（TEMPORARY TABLE）とはトランザクション終了時、またはセッション終了時にテーブル内のデータが削除されるテーブルです。
一方、PostgreSQLにおいてはデータのみならず、一時表自体が削除されてしまいます。

対応としては一時表を利用する処理の直前で一時表をCreateを行えばよいですが、現行システムは何十ものプロシージャが呼び出される複雑なビジネスロジック内で一時表を利用していたため、実際に対応する際はもう一工夫加えました。

対応内容

一時表のDDL定義（CTEATE TEMPORARY TABLE IF NOT EXISTS テーブル名...）管理するTBLを作成
- 一時表の名称、一時表のDDL定義の2カラムで構成
  - 一時表のDDL定義の変更があった場合にDBパッチで対応が可能
  - パッチで対応可能なため、DDL定義変更時のPL/pgSQLの修正が不要
- 既に一時表が作成されている可能性を考慮して、DDLにはIF NOT EXISTSのオプションを指定
一時表を作成するプロシージャを新規実装
- 引数に一時表の名称を指定して、1.で作成したTBLの一時表の名称に紐づくDDLを実行
- 同一セッション内で過去に利用したレコードが残っている可能性を考慮して、オプションとして、Create実行後にTRUNCATEを行うか判定する処理を実装
一時表が呼び出される処理の先頭で、2.のプロシージャを呼び出す処理を追加し、一時表を作成
- どのタイミングで一時表を作成するかは有識者に協力頂き、2.のプロシージャの呼び出し個所を検討

互換性の無い関数／パッケージ

関数の互換性に関しては、別紙:組み込み関数対応表（Oracle-PostgreSQL）にかなり詳細に記載があるため、基本的にはそちらを参考にすればよいと思います。
一方で、上記資料にも記載がない内容や、DB2（Oracle互換モード）⇔PostgreSQLのケースにおいてはそのまま流用できない関数について言及していこうと思います。

NVL／COALESCE

NVL関数はPostgreSQLには存在しない関数のため、Oracleから移行する際はCOALESCEに変更するのが別紙:組み込み関数対応表（Oracle-PostgreSQL）にも記載があります。
ただし、第一引数の値がNULLの場合は空文字とNULLの扱いの違いのために動作差異が発生します。

Oracle

SQL> select COALESCE('', 'NULLです') AS NULL判定 from dual;
NULL判定
--------------------
NULLです
SQL> select NVL('', 'NULLです') AS NULL判定 from dual;
NULL判定
--------------------
NULLです

PostgreSQL

testdb=# select coalesce('', 'NULLです') AS NULL判定;
 null判定
----------

(1 行)

対応方法

一例ですが、NVL関数に関しては、標準関数のCOALESCE関数で代用するのではなく、互換関数を作成し、互換関数の内部処理でNULLIF関数を利用して空文字とNULLを同一で扱うことで、代用が可能です。

NVL互換関数の一例

CREATE OR REPLACE FUNCTION test_plpgsql.NVL(VARCHAR, VARCHAR) RETURNS TEXT AS $$ 
DECLARE	
BEGIN
    RETURN COALESCE(NULLIF($1, ''), $2);
END;
$$ LANGUAGE plpgsql STABLE;

実行結果

testdb=# select test_plpgsql.nvl('', 'NULLです') AS NULL判定;
 null判定
----------
 NULLです
(1 行)

一例として、互換関数の一例としてNVL(VARCHAR, VARCHAR)を取り上げましたが、
第一引数、第二引数に指定するデータ型のパターンは様々存在するため、必要に応じて追加してください。

引数に文字列の長さを指定する関数／長さ自体を返却する関数

以下、取り上げる関数はDB2（Oracle互換モード）⇔PostgreSQLで発生する差分となります。

引数に文字列の長さを指定する関数や長さ自体を返却する関数は一例として以下が挙げられます。
以下、表の中で、SUBSTRB／LENGTHBを除く関数は文字数を扱っていますが、これはあくまでもOracle、PostgreSQLの話であり、DB2は文字数ではなく、バイト数で扱われます。

関数名	説明	補足
SUBSTR(char, m, n)	char のm 番目から n 文字分の文字列を抜き出して戻す。
SUBSTRB(char, m, n)	char のm 番目から n バイト分の文字列を抜き出して戻す。	PostgreSQLには存在しない関数
LPAD(char1, n, char2)	char1 の左に char2 に指定した文字を連続的に埋め込んで n 桁にして戻す。
RPAD(char1, n, char2)	char1 の右に char2 で指定した文字を連続的に埋め込み、長さ n にして戻す。
LENGTHB(char)	char の長さを文字単位で戻す。	PostgreSQLには存在しない関数
LENGTH(char)	char の長さをバイト単位で戻す

そのため、対象の文字列が１バイト文字とマルチバイト文字が混在しているケースにおいて、動作差異が発生します。

Oracle　※※文字数で扱われる※※

SQL> select substr('あいABCDEF', 3, 5) AS substr from dual;
SUBSTR
----------
ABCDE

PostgreSQL　※※文字数で扱われる※※

testdb=# select substr('あいABCDEF', 3, 5) AS substr;
 substr
--------
 ABCDE
(1 行)

DB2（Oracle互換モード）※※バイト数で扱われる※※

[db2inst1@db2server /]$ db2 "select substr('あいABCDEF', 3, 5) AS substr from dual"
SUBSTR
------
 いA
  1 record(s) selected.

対応方法

SUBSTR／LPAD／RPAD
- 対象の文字列が半角英数字等、すべて１バイト文字の場合はそのままでOK
- 対象の文字列が１バイト文字とマルチバイト文字が混在している場合はバイト数を扱えるように互換関数の作成が必要
  - 互換関数作成時は、移行前のDBと移行後のDBの文字コード（※1）の扱いに注意が必要
SUBSTRB
- 互換関数の作成が必要
  - 互換関数作成時は、移行前のDBと移行後のDBの文字コード（※1）の扱いに注意が必要
LENGTHB／LENGTH
- 基本的にはoctet_length関数で代用可能だが、Char型を扱う場合は注意が必要（※2）
- Char型のケースも救いたい場合は、互換関数作成を検討

※1 文字コードについて

移行前のDBと移行後のDBの文字コードが異なる場合、同じ文字でもバイト数が異なる場合があるため、互換関数を作成する際は注意が必要です。以下、一例を記載します。

	全角文字の1文字	半角カナ文字の1文字
SJIS	2バイト	1バイト
UTF-8	3バイトまたは4バイト	3バイト

※2 octet_lengthの動作について

octet_length関数は文字列中のバイト数を扱う関数であり、DB2（Oracle互換モード）のLENGTHB／LENGTH関数の代用として利用可能ですがChar型の長さを取得する際、DB2とPostgreSQLで動作が異なります。

DB2（Oracle互換モード）の場合はChar型の長さが取得される一方で、
PostgreSQLの場合、Char型かつ全角文字を含む場合、以下の計算式で長さが取得されるように見えます。
octet_lengthの結果 = Char型の長さ + [全角文字数] ×（[1文字のバイト数] - 1)

※正確な情報は正直わかりませんが、自分が把握している範囲での共有です。なぜこのような仕様なのかはわかりませんが・・。

PostgreSQL　※※Char型の場合は【Char型の長さ + [全角文字数] ×（[1文字のバイト数] - 1)】※※

testdb=# select octet_length('あいABCDEF') AS octet_length;
 octet_length
--------------
           12
(1 行) 
testdb=# select octet_length('あいABCDEF'::char(20)) AS octet_length;
 octet_length
--------------
           24
(1 行)
testdb=# select octet_length('あいうABCDEF'::char(20)) AS octet_length;
 octet_length
--------------
           26
(1 行)

DB2（Oracle互換モード）　※※Char型の場合はデータ型の長さが返却される※※

[db2inst1@db2server /]$ db2 "select lengthb('あいABCDEF') AS lengthb from dual"
LENGTHB
-----------
         12
  1 record(s) selected.
[db2inst1@db2server /]$ db2 "select lengthb(CAST('あいABCDEF' AS CHAR(20))) AS lengthb from dual"
LENGTHB
-----------
         20
  1 record(s) selected.
[db2inst1@db2server /]$ db2 "select lengthb(CAST('あいうABCDEF' AS CHAR(20))) AS lengthb from dual"
LENGTHB
-----------
         20
  1 record(s) selected.

ROWNUM

selectした結果に対して連番を付与したい場合、OracleではROWNUMを利用しましたが、PostgreSQLrow_number() OVER ()で代用する必要があります。

Oracle　※※ROWNUM利用例※※

SQL> select ROWNUM as 連番, deptno, deptname from user1.dept order by deptno;

      連番 DEPTNO     DEPTNAME
---------- ---------- ----------------------------------------
         1 0          営業部
         2 1          開発部
         3 2          人事部

postgresSQL　※※row_number() OVER ()で代用※※

testdb=# select row_number() OVER () as 連番, * from test_plpgsql.dept order by deptno;
 連番 | deptno | deptname
------+--------+----------
    1 | 0      | 営業部
    2 | 1      | 開発部
    3 | 2      | 人事部
(3 行)

ただし、row_number() OVER () はWHERE句で利用できないため、その場合は
FETCH FIRST n ROWS ONLYやlimitで代用する必要があります。

Oracle　※※ROWNUM利用例※※

SQL> select deptno, deptname from user1.dept where ROWNUM <= 1 order by deptno;

DEPTNO     DEPTNAME
---------- ----------------------------------------
0          営業部

postgresSQL　※※FETCH FIRST n ROWS ONLYで代用※※

testdb=# select * from test_plpgsql.dept order by deptno FETCH FIRST 1 ROWS ONLY;
 deptno | deptname
--------+----------
 0      | 営業部
(1 行)

RID

以下、取り上げる関数はDB2（Oracle互換モード）⇔PostgreSQLで発生する差分となります。

RIDはROWNUMと似ていますが、ROWNUMは、選択された行に対して仮想的な行番号を付与する擬似列の一方で、RIDは、行を一意的に識別するために使用します。

上記の通り、レコードの順序を変更しても、RIDの値は変動しません。

[db2inst1@db2server /]$ db2 "select rid() as RID, ROWNUM as ROWNUM, deptno, deptname from test.dept order by deptno;"
RID                  ROWNUM               DEPTNO DEPTNAME
-------------------- -------------------- ------ ----------------------------------------
                   4                    1 0      営業部
                   5                    2 1      開発部
                   6                    3 2      人事部

  3 record(s) selected.

[db2inst1@db2server /]$ db2 "select rid() as RID, ROWNUM as ROWNUM, deptno, deptname from test.dept order by deptno desc;"
RID                  ROWNUM               DEPTNO DEPTNAME
-------------------- -------------------- ------ ----------------------------------------
                   6                    1 2      人事部
                   5                    2 1      開発部
                   4                    3 0      営業部

  3 record(s) selected.

一方で、ROWNUMに関してはレコードの順序を変更した場合、ROWNUMの値も変動します。

RIDと同等の機能を持つ関数はPostgreSQLには存在しないため、
RIDを利用せずに一意なレコードとして特定する場合はWHERE句の条件にプライマリーキーを指定して代用する必要があります。

ファイル出力（`UTL_FILE`パッケージ）

Oracleでファイルの出力を行う場合はUTL_FILEパッケージを利用することで実現が可能ですが、PostgreSQLに同様のパッケージは存在しないため、自前で作成する必要があります。

COPYコマンドを利用すればファイルの出力自体は可能ですが、COPYコマンド単体ではファイルの追記が行えないため、追記が必要なログファイルなどを出力したい場合は一工夫が必要です。

超ざっくりですが、ファイル出力の処理を自前で実装するための処理の流れを紹介します。

対応方法

ファイル出力用の一時表を作成し、ファイル出力前にCreate
COPY ファイル出力用の一時表 FROM ...で既に存在しているファイルの内容を1.でCreateした一時表に登録
2.で登録した一時表に対して、追加で出力したい内容をINSERT
COPY ファイル出力用の一時表 TO ...で、3．で登録した内容も含めてファイルを更新

レコード型

Orecleのレコード型（TYPE レコード型名 IS RECORD ...）はPostgreSQLではそのまま利用できないため、ユーザー定義型（create type ユーザー定義型名 as ...）で代用する必要があります。

レコード型／ユーザー定義型の利用例についてはこちらを参考にしてください。

レコード型（Oracle）

CREATE OR REPLACE PACKAGE TestTypePackage IS
  TYPE recBankAccount IS RECORD ( 
      userid varchar(10)    , accnumber varchar(7)
      , acctype varchar(30) , name varchar(30)
      , balance varchar(20) , lastdate timestamp(6)
  ); 
END;

ユーザー定義型（PostgreSQL）

create type test_plpgsql.recBankAccount as ( 
      userid varchar(10)    , accnumber varchar(7)
      , acctype varchar(30) , name varchar(30)
      , balance varchar(20) , lastdate timestamp(6)
);

ただし、Orecleのレコード型は初期値を設定することが可能ですが、PostgreSQLのユーザー定義型は初期値の設定が不可のため、初期値を設定したい場合は別の手段で代替する必要があります。

私が出会ったソースの場合、定数として利用するケースが殆どであったため、固定値を返却する関数を作成して代位要することにしました。

レコード型（Oracle）

  TYPE resultCode IS RECORD ( 
      success numeric := 0,
      error   numeric := 255
  );

固定値を返却する関数を作成して代用（PostgreSQL）

CREATE OR REPLACE FUNCTION test_plpgsql.getResultCode(VARCHAR) RETURNS NUMERIC AS $$ 
DECLARE
    i_result NUMERIC := NULL;
BEGIN
    CASE $1
        WHEN 'success' THEN
            i_result := 0;
        WHEN 'error' THEN
            i_result := 255;
    END CASE;
    
    RETURN i_result;
END;
$$ LANGUAGE plpgsql;

引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数

タイトルの通り、PL/SQLでは引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数を実装することができますが、PL/pgSQLではそのような関数は実装できません。

この場合、以下の対応を行います。

PL/pgSQLでは、return文を使用せずに、OUTパラメーター追加し、元の戻り値としてそれを代用
戻り値のデータ型を、元のデータ型からRECORD型に修正（戻り値として返却する項目が増えるため、RECORD型として扱う必要がある）

この代替手法は、呼び出し元のコードにも修正が必要となるため、複数のメンバーが同時に作業する場合には注意が必要です。

当時の現行システムはこのパターンの関数が多数存在していたため、このケースの関数を呼び出している処理が存在している場合、呼び出し先の関数の修正担当者が追加したOUTパラメーターや戻り値の変数名をどのように命名しているのか、適宜連携しながら製造を進める必要がありました。そのような背景もあり、製造時にメンバー同士の連携不足で不具合が混入しやすい内容でした。

※この修正に関しては、該当する関数ごとに新旧の引数や戻り値についての対応表を作成し、製造担当者に記入してもらうようにし、メンバー間での連携がスムーズに行えるようにしました。

修正イメージ

Oracle

/* *******************************************
 * 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数
 * *******************************************/
CREATE OR REPLACE FUNCTION 
    ファンクション名(引数１ IN OUT データ型) RETURN データ型
IS
    変数１ データ型 := 値１;
BEGIN
    引数 := 値２;
    RETURN 変数１;
END;
/* *******************************************
 * 呼び出し元プロシージャ
 * *******************************************/
CREATE OR REPLACE PROCEDURE プロシージャ名
IS
    変数２（ファンクションの引数）   データ型;
    変数３（ファンクションの戻り値） データ型;
BEGIN 
    変数３（ファンクションの戻り値） := ファンクション名(変数２（ファンクションの引数）); 
END;

PostgreSQL

/* *******************************************
 * 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数
 * *******************************************/
CREATE OR REPLACE FUNCTION 
    ファンクション名(引数１ IN OUT データ型, 引数２ OUT データ型) RETURNS RECORD AS $$ 
DECLARE	
    変数１ データ型 := 値１;
BEGIN
    引数 := 値２;
    RETURN;
END;
$$ LANGUAGE plpgsql;
/* *******************************************
 * 呼び出し元プロシージャ
 * *******************************************/
CREATE OR REPLACE PROCEDURE プロシージャ名() AS $$ 
DECLARE	
    変数２（ファンクションの引数）   データ型;
    変数３（ファンクションの戻り値） データ型;
    変数４                           RECORD;
BEGIN 
    変数４ := ファンクション名(変数２（ファンクションの引数）); 
    変数２（ファンクションの引数）   := 変数４.引数１;
    変数３（ファンクションの戻り値） := 変数４.引数２;
END;
$$ LANGUAGE plpgsql;

サンプルコード

サンプルコード（Oracle）

/* *******************************************
 * 銀行口座TBLにレコードを追加するpackage
 * *******************************************/
CREATE OR REPLACE PACKAGE TestTypePackage IS
  TYPE recBankAccount IS RECORD( 
      userid varchar(10)    , accnumber varchar(7)
      , acctype varchar(30) , name varchar(30)
      , balance varchar(20) , lastdate timestamp(6)
  ); 
  -- ** 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数 **
  FUNCTION funcRegisterBankAccount(p_rec1 IN OUT recBankAccount) RETURN INTEGER;
    -- **** 動作検証用プロシージャ ****
  PROCEDURE procTestTypePackage;
END;
/
/* *******************************************
 * 銀行口座TBLにレコードを追加するpackagebody
 * *******************************************/
CREATE OR REPLACE PACKAGE BODY TestTypePackage IS
    -- **** 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数 ****
    FUNCTION funcRegisterBankAccount(p_rec1 IN OUT recBankAccount) RETURN INTEGER
    IS
    BEGIN
        IF p_rec1.userid IS NULL OR p_rec1.accnumber IS NULL OR p_rec1.acctype IS NULL THEN
            RETURN 255; -- プライマリーキーの値がNULLの場合は即時return
        END IF;
        -- **** IN OUTパラメータの値がNULLの場合は値を更新して、呼び出し元に返却する ****
        p_rec1.name := NVL(p_rec1.name, '名無し名義'); 
        p_rec1.balance := NVL(p_rec1.balance, 0); 
        p_rec1.lastdate := NVL(p_rec1.lastdate, CURRENT_TIMESTAMP); 
        -- 銀行口座TBLにレコードを登録
        INSERT INTO USER1.BANKACCOUNT 
            (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
        VALUES 
            (p_rec1.userid, p_rec1.accnumber, p_rec1.acctype, p_rec1.name, p_rec1.balance , p_rec1.lastdate);
        COMMIT;
        -- **** 正常終了の場合、戻り値に0を返却する ****
        RETURN 0;
    EXCEPTION
        WHEN OTHERS THEN
            RETURN 255;
    END;
    
    -- **** 動作検証用プロシージャ ****
    PROCEDURE procTestTypePackage IS
        i_result INTEGER; 
        rec1 recBankAccount; 
    BEGIN 
        rec1.userid := 'testuser01'; rec1.accnumber := '1234567'; rec1.acctype := '貯蓄'; 
        rec1.name := null; rec1.balance := null; rec1.lastdate := null; 
        -- **** 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数の実行 ****
        i_result := funcRegisterBankAccount(rec1); 
        -- **** 戻り値及びIN OUTパラメータの更新結果の確認 ****
        DBMS_OUTPUT.PUT_LINE('[処理結果]：' || i_result); 
        DBMS_OUTPUT.PUT_LINE('[userid]：' || rec1.userid); 
        DBMS_OUTPUT.PUT_LINE('[accnumber]：' || rec1.accnumber); 
        DBMS_OUTPUT.PUT_LINE('[acctype]：' || rec1.acctype); 
        DBMS_OUTPUT.PUT_LINE('[name]：' || rec1.name); 
        DBMS_OUTPUT.PUT_LINE('[balance]：' || rec1.balance); 
        DBMS_OUTPUT.PUT_LINE('[lastdate]：' || rec1.lastdate); 
    END;  
END TestTypePackage;
/

実行結果（Oracle）

SQL> CALL TestTypePackage.procTestTypePackage();
[処理結果]：0
[userid]：testuser01
[accnumber]：1234567
[acctype]：貯蓄
[name]：名無し名義
[balance]：0
[lastdate]：24-05-26 18:08:59.582000
コールが完了しました。

SQL> select userid, to_number(accnumber), acctype, name, to_number(balance), lastdate from USER1.BANKACCOUNT;
USERID          TO_NUMBER(ACCNUMBER) ACCTYPE         NAME                 TO_NUMBER(BALANCE) LASTDATE
--------------- -------------------- --------------- -------------------- ------------------ ----------------------------------------
testuser01                   1234567 貯蓄            名無し名義                            0 24-05-26 18:08:59.582000

サンプルコード（PostgreSQL）

/* *******************************************
 * ユーザー定義型
 * *******************************************/
create type test_plpgsql.recBankAccount as ( 
      userid varchar(10)    , accnumber varchar(7)
      , acctype varchar(30) , name varchar(30)
      , balance varchar(20) , lastdate timestamp(6)
); 
/* *******************************************
 * 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数
 * *******************************************/
CREATE OR REPLACE FUNCTION test_plpgsql.funcRegisterBankAccount
    (p_rec1 IN OUT test_plpgsql.recBankAccount, p_result OUT INTEGER) RETURNS RECORD AS $$ 
DECLARE	
BEGIN
    IF p_rec1.userid IS NULL OR p_rec1.accnumber IS NULL OR p_rec1.acctype IS NULL THEN
        -- プライマリーキーの値がNULLの場合は即時return
        p_result := 255;
        RETURN;
    END IF;
    -- **** IN OUTパラメータの値がNULLの場合は値を更新して、呼び出し元に返却する ****
    p_rec1.name := coalesce(nullif(p_rec1.name, ''), '名無し名義'); 
    p_rec1.balance := coalesce(nullif(p_rec1.balance, ''), '0'); 
    p_rec1.lastdate := coalesce(p_rec1.lastdate, CURRENT_TIMESTAMP); 
    -- 銀行口座TBLにレコードを登録
    INSERT INTO test_plpgsql.BANKACCOUNT 
        (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
    VALUES 
        (p_rec1.userid, p_rec1.accnumber::numeric, p_rec1.acctype, p_rec1.name, p_rec1.balance::numeric , p_rec1.lastdate);
    -- **** 正常終了の場合、戻り値に0を返却する ****
    p_result := 0;
EXCEPTION
    WHEN OTHERS THEN
        p_result := 255;
END;
$$ LANGUAGE plpgsql;
/* *******************************************
 * 動作検証用プロシージャ
 * *******************************************/
CREATE OR REPLACE PROCEDURE test_plpgsql.procTestTypePackage() AS $$ 
DECLARE	
    i_result INTEGER; 
    rec1 test_plpgsql.recBankAccount; 
    rec2 RECORD;
BEGIN 
    rec1.userid := 'testuser01'; rec1.accnumber := '1234567'; rec1.acctype := '貯蓄'; 
    rec1.name := null; rec1.balance := null; rec1.lastdate := null; 
    -- **** 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数の実行 ****
    rec2 := funcRegisterBankAccount(rec1); 
    rec1 := rec2.p_rec1;
    i_result := rec2.p_result;
    -- **** 戻り値及びIN OUTパラメータの更新結果の確認 ****
    RAISE INFO '[処理結果]：%'  , i_result; 
    RAISE INFO '[userid]：%'    , rec1.userid; 
    RAISE INFO '[accnumber]：%' , rec1.accnumber; 
    RAISE INFO '[acctype]：%'   , rec1.acctype; 
    RAISE INFO '[name]：%'      , rec1.name; 
    RAISE INFO '[balance]：%'   , rec1.balance; 
    RAISE INFO '[lastdate]：%'  , rec1.lastdate; 
END;  
$$ LANGUAGE plpgsql;

実行結果（PostgreSQL）

testdb=# call test_plpgsql.procTestTypePackage();
INFO:  [処理結果]：0
INFO:  [userid]：testuser01
INFO:  [accnumber]：1234567
INFO:  [acctype]：貯蓄
INFO:  [name]：名無し名義
INFO:  [balance]：0
INFO:  [lastdate]：2024-05-26 20:24:44.088765
CALL
testdb=# select * from test_plpgsql.BANKACCOUNT;
   userid   | accnumber | acctype |    name    | balance |          lastdate
------------+-----------+---------+------------+---------+----------------------------
 testuser01 |   1234567 | 貯蓄    | 名無し名義 |       0 | 2024-05-26 20:24:44.088765
(1 行)

引数の上限数

PostgreSQLの関数の引数の上限はデフォルトで100個と決まっています。
詳細は分かりませんが、どうもpg_config_manual.hに引数の上限が定義されているようです。

pg_config_manual.h（一部抜粋）

/*
 * Maximum number of arguments to a function.
 *
 * The minimum value is 8 (GIN indexes use 8-argument support functions).
 * The maximum possible value is around 600 (limited by index tuple size in
 * pg_proc's index; BLCKSZ larger than 8K would allow more).  Values larger
 * than needed will waste memory and processing time, but do not directly
 * cost disk space.
 *
 * Changing this does not require an initdb, but it does require a full
 * backend recompile (including any user-defined C functions).
 */
#define FUNC_MAX_ARGS		100

Orecleは100個を超えるの引数を持つ関数、プロシージャを作成することが可能なため、このケースの場合そのままPostgreSQLにマイグレーションすることはできません。

対応例としては以下が挙げられますが、2、3に関しては呼び出し元にもそれなりに修正が入ります・・。

不要な引数を削除して100個以下に収める
プロシージャを分割して引数を100個以下に収める
引数をJson型にして受け渡す
FUNC_MAX_ARGSで定義されている値を修正して、コンパイル

なお、上記仕様のため、CONCAT関数での最大連結文字数は100個が上限となります。
現行ソースで文字列結合演算子（||）を用いてSQLを動的に生成する処理をCONCAT関数に置換したら引数の上限数を超えてエラーになりました・・。（どれだけ複雑なクエリなのか・・）

`SELECT ... INTO 変数` の挙動

SELECT ... INTO 変数はSELECT文の実行結果を変数の値に設定する構文です。
Oracle、PostgreSQLどちらも構文を修正せずに利用可能ですが、

selectの結果が0件の場合
selectの結果が2件以上の場合

の上記２パターンにおいてOracleでは例外（※）が発生しますが、PostgreSQLは例外とならずに処理が継続します。

※SELECTの実行結果が0件の場合はNO_DATA_FOUND、2件以上の場合はTOO_MANY_ROWSというエラーが発生します。

対応方法

PostgreSQLにおいてはSTRICTを追加することで、Oracleと同様の動作をすることが可能となります。

修正前

SELECT ... INTO 変数

修正後

SELECT ... INTO STRICT 変数

それぞれのサンプルコードはこちらを参照。

NULLと空文字の違いの件と同様に、一括置換が難しい、かつ修正が漏れていたとしても構文エラーとならず動作してしまうため、後工程まで見落とされる可能性があります。（実際、この事象も愚直に横展開をすることになりました・・。）

製造工程で漏れなく修正しきるのは大変ですが、修正漏れを後工程へ持ち込まないために、単体テストの段階でテストデータが0件、1件、2件以上の3パターンを実施するなど、テスト観点に取り入れるのが大事だと思います。

カーソルの挙動

構文に関する修正は、初めに紹介した移行事例に関するサイトを参考にすればそこまで難しくありませんが、同じ構文でも動作が異なる部分に関して、今回は紹介していきたいと思います。

FETCH（読み込む行が存在しない場合）

以下の構文はOracle・PostgreSQLでも動作しますが、実は読み込む行が存在しない場合（ループ処理でFETCHする際、最終ループに当たるタイミングなど）の動作が異なります。

Oracleでの動作：変数の値が更新されない
PostgreSQLでの動作：変数の値がNULLに更新される

FETCH カーソル名 INTO 変数名;

サンプルコード

サンプルコード（Oracle）　※※変数の値が更新されない※※

CREATE OR REPLACE PROCEDURE USER1.procTestCurs IS  
    CURSOR curs1 IS SELECT deptno, deptname FROM USER1.dept ORDER BY deptno;
    v_deptno   CHAR(5)     := NULL;
    v_deptname VARCHAR(40) := NULL;
BEGIN    
    OPEN curs1;
    LOOP 
        -- ★★★最後のレコードをFETCHした後の動作が異なる★★★
        -- Oracleの場合    ：変数の値が更新されない
        -- PostgreSQLの場合：変数の値がNULLに更新される
        FETCH curs1 INTO v_deptno, v_deptname;
        IF curs1%NOTFOUND THEN
            EXIT;
        END IF;
        DBMS_OUTPUT.PUT_LINE('[ループの内側]deptno:' || v_deptno || 'deptname:' || v_deptname);
    END LOOP;
    
    CLOSE curs1;
    
    DBMS_OUTPUT.PUT_LINE('[ループの外側]deptno:' || v_deptno || 'deptname:' || v_deptname);

END;    
/

実行結果（Oracle）　※※変数の値が更新されない※※

SQL> CALL procTestCurs();
[ループの内側]deptno:0    deptname:営業部
[ループの内側]deptno:1    deptname:開発部
[ループの内側]deptno:2    deptname:人事部
[ループの外側]deptno:2    deptname:人事部

コールが完了しました。

サンプルコード（PostgreSQL） ※※変数の値がNULLに更新される※※

CREATE OR REPLACE PROCEDURE test_plpgsql.procTestCurs() AS $$
DECLARE    
    curs1      refcursor;
    v_deptno   CHAR(5)     := NULL;
    v_deptname VARCHAR(40) := NULL;
BEGIN    
    OPEN curs1 FOR SELECT deptno, deptname FROM test_plpgsql.dept ORDER BY deptno;

    LOOP 
        -- ★★★最後のレコードをFETCHした後の動作が異なる★★★
        -- Oracleの場合    ：変数の値が更新されない
        -- PostgreSQLの場合：変数の値がNULLに更新される
        FETCH curs1 INTO v_deptno, v_deptname;
        IF NOT FOUND THEN
            EXIT;
        END IF;
        RAISE INFO '[ループの内側]deptno:% deptname:%', v_deptno, v_deptname;
    END LOOP;
    
    CLOSE curs1;
    
    RAISE INFO '[ループの外側]deptno:% deptname:%', v_deptno, v_deptname;

END;    
$$ LANGUAGE plpgsql;

実行結果（PostgreSQL）　※※変数の値がNULLに更新される※※

testdb=# CALL test_plpgsql.procTestCurs();
INFO:  [ループの内側]deptno:0     deptname:営業部
INFO:  [ループの内側]deptno:1     deptname:開発部
INFO:  [ループの内側]deptno:2     deptname:人事部
INFO:  [ループの外側]deptno:<NULL> deptname:<NULL>
CALL

カーソルを利用する際、FETCHで受け取った値をループの外側で利用するケースはあまりないと思いますが、そのような実装がある場合は修正が必要となります。

サンプルコード_修正後（PostgreSQL）

CREATE OR REPLACE PROCEDURE test_plpgsql.procTestCurs() AS $$
DECLARE    
    curs1      refcursor;
    v_deptno   CHAR(5)     := NULL;
    v_deptname VARCHAR(40) := NULL;
+   v_deptno_tmp   CHAR(5)     := NULL;
+   v_deptname_tmp VARCHAR(40) := NULL;
BEGIN    
    OPEN curs1 FOR SELECT deptno, deptname FROM test_plpgsql.dept ORDER BY deptno;

    LOOP 
        -- ★★★最後のレコードをFETCHした後の動作が異なる★★★
        -- Oracleの場合    ：変数の値が更新されない
        -- PostgreSQLの場合：変数の値がNULLに更新される
-       FETCH curs1 INTO v_deptno, v_deptname;
+       FETCH curs1 INTO v_deptno_tmp, v_deptname_tmp;
        IF NOT FOUND THEN
            EXIT;
        END IF;

+       v_deptno := v_deptno_tmp;
+       v_deptname := v_deptname_tmp;
        RAISE INFO '[ループの内側]deptno:% deptname:%', v_deptno, v_deptname;
    END LOOP;


    CLOSE curs1;
    
    RAISE INFO '[ループの外側]deptno:% deptname:%', v_deptno, v_deptname;

END;    
$$ LANGUAGE plpgsql;

トランザクション制御

トランザクション制御に関しては、PL/SQLとPL/pgSQLで仕様に大きな違いがあり、単純な置換が困難です。

~~特にSAVEPOINT。お前だぁ！！！！~~

現行ソースをどのように修正するか一番悩んだところになりますが、当時試行錯誤した結果をまとめた記事があるので、そちらをご覧ください。

自立型トランザクション

Oracleの自立型トランザクション（PRAGMA AUTONOMOUS_TRANSACTION）はPostgreSQLでは対応していないため、DBLINKに置き換えた実装は修正する必要があります。

自立型トランザクションの利用目的の一例として、処理の途中でログ出力用のTBLにエラー内容を登録する。 というケースが挙げられます。

自立型トランザクションを利用しない場合、処理の途中でエラーが発生しRollbackを行うと、ログ出力用のTBLに登録した内容もRollbackされてしまいます。

一方、ログ出力用のTBLにエラー内容を登録する際に自立型トランザクションを利用することで、ログの内容はRollbackされずレコードに残り続けることができます。

ソース（Oracle）

/* *******************************************
 * 自立型トランザクション検証用ソース
 * *******************************************/
DECLARE
    ERROR_CODE NUMBER(5) := -20000;
BEGIN
    -- 自立型トランザクションを用いてログメッセージを出力
    writeDebugLog ('no_proc', '！！BANKACCOUNTテーブル登録開始！！');
    
    -- 銀行口座TBLにレコードを登録
    INSERT INTO BANKACCOUNT 
        (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
    VALUES 
        ('testcase01', 1, '普通', 'テストケース０１', 1000000, CURRENT_TIMESTAMP);
    
    -- 強制的に例外発生
    RAISE_APPLICATION_ERROR(ERROR_CODE, 'MY EXCEPTION!!');
    
    -- 自立型トランザクションを用いてログメッセージを出力    
    writeDebugLog ('no_proc', '！！BANKACCOUNTテーブル登録終了！！');
EXCEPTION
    WHEN OTHERS THEN
        -- 自立型トランザクションを用いてログメッセージを出力    
        writeDebugLog ('no_proc', '！！BANKACCOUNTテーブル登録時に例外発生！！' || ' SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM);
        ROLLBACK;
END;
/
/* *******************************************
 * 自立型トランザクションを用いてINSERT処理を行うプロシージャ
 * *******************************************/
CREATE OR REPLACE PROCEDURE writeDebugLog (p_procName IN varchar, p_logMsg IN varchar) IS
    PRAGMA AUTONOMOUS_TRANSACTION; --自律型トランザクションの指定
BEGIN

    INSERT INTO  DebugLog (procName, logMsg, lastDate) values (p_procName, p_logMsg, current_timestamp);
    COMMIT; -- 自律型トランザクションではトランザクションを終了させないとエラー
 
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END writeDebugLog;
/

実行結果（Oracle）

SQL> select * from DebugLog;
PROCNAME        LOGMSG                                                                                               LASTDATE
--------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------
no_proc         ！！BANKACCOUNTテーブル登録時に例外発生！！ SQLCODE=-20000, SQLERRM=ORA-20000: MY EXCEPTION!!        24-05-25 22:24:49.403000
no_proc         ！！BANKACCOUNTテーブル登録開始！！                                                                  24-05-25 22:24:49.403000

SQL> select * from BANKACCOUNT;
レコードが選択されませんでした。

自立型トランザクションを利用したソースをDBLINKに置き換えた場合のソースは以下となります。

ソース（PostgreSQL）

/* *******************************************
 * 自立型トランザクション検証用ソース
 * *******************************************/
DO $$
DECLARE
BEGIN
    -- 自立型トランザクションを用いてログメッセージを出力
    CALL writeDebugLog ('no_proc', '！！BANKACCOUNTテーブル登録開始！！');
    
    -- 銀行口座TBLにレコードを登録
    INSERT INTO BANKACCOUNT 
        (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
    VALUES 
        ('testcase01', 1, '普通', 'テストケース０１', 1000000, CURRENT_TIMESTAMP);
    
    -- 強制的に例外発生
    RAISE EXCEPTION SQLSTATE 'UD000' USING MESSAGE='MY EXCEPTION!!';
    
    -- 自立型トランザクションを用いてログメッセージを出力    
    CALL writeDebugLog ('no_proc', '！！BANKACCOUNTテーブル登録終了！！');
EXCEPTION
    WHEN OTHERS THEN
        -- 自立型トランザクションを用いてログメッセージを出力    
        CALL writeDebugLog ('no_proc', '！！BANKACCOUNTテーブル登録時に例外発生！！' || ' SQLSTATE=' || SQLSTATE || ', SQLERRM=' || SQLERRM);
END;
$$ LANGUAGE plpgsql;

/* *******************************************
 * 自立型トランザクションを用いてINSERT処理を行うプロシージャ
 * *******************************************/
CREATE OR REPLACE PROCEDURE writeDebugLog (p_procName IN varchar, p_logMsg IN varchar) AS $$
DECLARE
BEGIN

	PERFORM DBLINK_CONNECT('dblink_test', 'host=ホスト名 port=ポート番号 dbname=DB名 user=ユーザー名 password=パスワード');
    PERFORM DBLINK_EXEC('dblink_test',
        FORMAT('INSERT INTO  test_plpgsql.DebugLog (procName, logMsg, lastDate) values (%L, %L, current_timestamp);', p_procName, p_logMsg));
    PERFORM DBLINK_DISCONNECT('dblink_test');
 
EXCEPTION
    WHEN OTHERS THEN
        PERFORM DBLINK_DISCONNECT('dblink_test');
        RAISE EXCEPTION SQLSTATE 'UD000' USING MESSAGE=' SQLSTATE=' || SQLSTATE || ', SQLERRM=' || SQLERRM;
END;
$$ LANGUAGE plpgsql;

実行結果（PostgreSQL）

testdb=# select * from DebugLog;
 procname |                                       logmsg                                       |          lastdate
----------+------------------------------------------------------------------------------------+----------------------------
 no_proc  | ！！BANKACCOUNTテーブル登録開始！！                                                | 2024-05-25 23:12:50.857013
 no_proc  | ！！BANKACCOUNTテーブル登録時に例外発生！！ SQLSTATE=UD000, SQLERRM=MY EXCEPTION!! | 2024-05-25 23:12:50.900875
(2 行)

testdb=# select * from BANKACCOUNT;
 userid | accnumber | acctype | name | balance | lastdate
--------+-----------+---------+------+---------+----------
(0 行)

実装例は上記となりますが、DBLINK利用時の注意点をいくつかピックアップします。

DBLINK利用時に定義する接続先の情報について

DBLINKを利用する際、DBLINK_CONNECTの第二引数に接続先の情報を設定する必要がありますが、動作環境により接続先の情報は異なるため、引数に接続情報をべた書きするのは望ましくありません。

例

  PERFORM DBLINK_CONNECT('dblink_test', 'host=ホスト名 port=ポート番号 dbname=DB名 user=ユーザー名 password=パスワード');

このようなケースにおいては、CREATE SERVERの仕組みを利用することで、引数に接続情報をべた書きする必要がなくなるので、DBLINKを利用する際は参考にしてください。

DBLINKでプロシージャを実行する方法について

DBLINKの利用方法はPostgreSQLのリファレンスを参考にすればよいのですが、プロシージャの呼び出し方（特に引数にINOUTパラメータを保有しているようなプロシージャ）に関してはリファレンスを見てもどのように実装すればよいか理解が難しかったです。

DBLINKでプロシージャを実行する方法に関する記事を本記事とは別で作成しているので、実装方法が気になる方はご覧ください。

性能面の問題について

DBLINKを利用した処理は新規セッションでDBに接続し、負荷がかかるため、性能的にかなり問題が発生しました。
性能試験でDBLINKを利用している業務で軒並み性能遅延が発生し、DBLINKの利用を可能な限り減らすこととなったため、安易にDBLINKに置き換えず、必要性について検討してからマイグレーションを行ったほうが良いです。

外部結合演算子

外部結合演算子はWHERE句の条件式において、表結合として付帯したい表の列に外部結合演算子である(+)を付加することでOUTER JOINと同等の外部結合が可能となる演算子のことです。

Oracle

select
    t3.deptname AS 部署, t4.posname AS 役職, t2.empname AS 氏名, t2.age AS 年齢 
from
    member t1, emp t2, dept t3, pos t4 
where
    t1.empno = t2.empno(+) 
    and t1.deptno = t3.deptno(+) 
    and t2.poscode = t4.poscode(+) 
    and to_number(t1.empno, '00000') between 0 and 5 
order by
    t3.deptno, t4.poscode, t2.age

PostgreSQLでは外部結合演算子が利用できないため、適宜OUTER JOINに修正する必要があります。
単純な置換ができず、結合対象のテーブル、カラムが多くなればなるほど修正は手間となります。

PostgreSQL

select
    t3.deptname AS 部署, t4.posname AS 役職, t2.empname AS 氏名, t2.age AS 年齢  
from
    member t1 
    left outer join emp t2 
        on t1.empno = t2.empno 
    left outer join dept t3 
        on t1.deptno = t3.deptno 
    left outer join pos t4 
        on t2.poscode = t4.poscode 
where
    to_number(t1.empno, '00000') between 0 and 5 
order by
    t3.deptno, t4.poscode, t2.age

PL/pgSQLの製造・単体テストについて

SQLMAPに関しては、SQLファイルの修正後、画面の単体テストでまとめて試験を行っていましたが、PL/pgSQLに関しては、製造実施後、モジュール毎に単体テストを実施していました。

当時のPL/pgSQLの製造・単体テストの進め方について、紹介してきたいと思います。
最善の進め方かであるかはわかりませんが、少しでも参考になれば幸いです。

期間・体制

期間
- 製造、単体テストで合わせて8か月程度
体制
- 製造当初は3名。ピーク時は7名程度
- 自分はいちメンバーで、WBSや進捗報告はPLが実施していましたが、製造・単体テストの進め方やメンバーの作業のとりまとめなどは自身で行うことができ、ある程度裁量を与えられていました

製造について

PL/pgSQLの製造にあたり、既に用意されているDB2（Oracle互換モード）からPostgreSQLのマイグレーション方針の内部資料（見積もり時にpostgreSQLの移行を検証した際の資料）を参照し、PL/SQL→PL/pgSQLの修正を実施していましたが、

複数人で並行して製造を進めている
マイグレーションにあたり、インプットすべき知識が膨大、かつ内部資料に記載されていない修正パターンが多数存在

ということもあり、製造が順調に進まない、メンバーによって修正内容のばらつきが発生する等の懸念がありました。
そのため、製造メンバー全員が足並みそろえて製造するために、以下の点に気を付けて開発を進めていきました。

気を付けた点

修正方針資料の更新
- 内部資料に存在しない修正方針があれば、内部資料とは別に修正方針をまとめた資料に、修正前（PL/SQL）のソースと修正後（PL/pgSQL）の実装例を記載
懸案管理簿の更新
- 修正の仕方がわからない、修正に関して懸案があれば懸案管理簿に記載
開発メンバー同士の朝会実施
- 製造メンバーの朝会で、前日に追記した修正方針及び懸案管理簿を確認し、メンバーが記載した修正方針について問題がないか確認し、製造メンバー全員で認識合わせを実施
  - 既に製造完了したソースに追加改修が必要か確認し、必要であれば横展開を行い修正を実施
- 懸案管理簿の内容をメンバー全員で確認し、その場で解決できなければ、解決するためのプラン検討及び担当者の割り当てを実施（技術的な課題であり、解決したら修正方針に追記する）
チェックリストを用いたセルフレビュー
- 修正方針資料をインプットとして、モジュールごとにチェックリストを作成し、レビュー依頼前にセルフレビューを実施

単体テストについて

PL/pgSQLの単体テストは以下の通りに実行していました。

実行ツールについて

単体テストを実行するにあたり、以下の仕組みを構築しました。
（PJTのアーキテクトポジションのメンバーに実装いただきました。勉強になります・・。）

Nunitを用いてテストコードを作成し、テストコード内でPL/SQLとPL/pgSQLを実行
テスト項目はドキュメントコメントに記載
基本的にモジュール毎に試験を行うため、テストコード実行時、下位モジュールにスタブを適用するための仕組みを構築
テストコード実行時、テストデータの登録、削除を行うための仕組みを構築

テストの確認内容について

現行システムのPL/SQLと新規製造したPL/pgSQLをそれぞれ実行し、TBLの更新結果、戻り値、OUTパラメーター等の現新比較を実施

テストパターンの洗い出しについて

モジュール毎、処理内に存在する以下１～４の項目を洗い出し、Excelで表にまとめ（※）、２～４の処理をすべて網羅するようにテストケースを作成

条件分岐
TBLに対してCRUD処理を行っている箇所
プロシージャ、ファンクションを呼び出している箇所
例外処理

※NunitはあくまでもPL/SQLとPL/pgSQLの呼び出しに利用するものであり、テストのカバレッジを確認することができなかったため、この資料でテストが網羅できているか確認を行っていました。

よかった点

修正方針資料に修正内容を追記することで、マイグレーションに関する知見をチーム内に蓄積することができた
後工程で横展開が必要になった修正漏れもあったが、概ね、担当者毎に修正内容がばらつくことなく製造を行うことができた

反省点

製造時のチェックリストの形式化

修正方針資料を参考にしてチェックリストを作成しましたが、確認項目が非常に多く、チェックリストの確認に時間がかかりすぎることや形式的になってしまっている部分もあったと思われます。

マイグレーションに関する知識が不足している場合、製造開始時にはチェックリストが必要かもしれませんが、製造に慣れてきたら、チェックリストの確認・消し込みに割く時間を製造担当者の相互レビューの時間に充てたほうが良かったかもしれないと今では思います。（当時はPLがほぼすべてのソースに対してレビューを実施）

データパターン観点の試験の不足

今回試験するPL/pgSQLの資源は、画面の試験でも利用するため、モジュール単位で行う今回の単体試験は疎通の確認と最低限の現新比較がメインでしたが、NULL・空文字のデータやSELECTの取得件数（0件、1件、2件以上）など、データパターンを明示的に観点として含めていませんでした。

そのため、空文字とNULLの扱いによる動作差異やSELECT ... INTO 変数 の動作の差異など、実際の動作は異なるがエラーにはならない構文や仕様については単体テストで検知することができず、後工程で横展開が必要になったのは反省でした。

余談

今回はあくまでもDB2→PostgreSQLの移行でしたが、Oracleの要素も詰め込まれていたので、PostgreSQLの拡張としてorafceを採用したらどうだったのかなぁと思ったりします。

一括置換では対応できない、SUBSTRBなどの互換性のない関数やUTL_FILEなどのパッケージをそのまま利用できるのは大きいと思います。

終わりに

DB2（Oracle互換モード）からPostgreSQLのマイグレーションに関して、あまり公開されておらず、かつ本記事でも書ききれていないニッチなネタが数多く残されています・・。
（現場離れてかなり思い出せない内容も増えてきて、記憶の新しいうちにこの記事を書いておけばと少し後悔しています。PostgreSQLのマイグレーションは本当に、本当に大変だったので。）

いつの日か、これまで紹介した記事や本記事を参考にして、より良いPostgreSQLのマイグレーションの資料が公開されることを願います。

おまけ：（気が向いたら）加筆するかもしれないリスト

Exception句追加による性能面の影響（ロングトランザクションの発生）
- 参考になりそうなサイト
引数のデフォルト句の挙動の違い
精度指定の無いデータ型の桁数の違い
配列の構文の違い
ユーザー定義例外のマイグレーション方針について
ユーザー定義関数の変動性分類について
大文字小文字問題

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

DB2（Oracle互換モード）からPostgreSQLのマイグレーションにめちゃくちゃ苦労した話

はじめに

前提

マイグレーションの概要について

自身の担当内容

PostgreSQLの移行事例

PostgreSQL エンタープライズ・コンソーシアムのサイト

その他、参考になりそうなサイト

苦労した点

空文字とNULLの扱い

IS NULL、IS NOT NULL演算子の比較対象の値の修正が必要 について

空文字に対するキャストに注意が必要 について

文字列結合演算子（||）からCONCAT関数への修正が必要 について

Java側のNULL判定が効かなくなるなど、修正が必要 について

どうすればよかったのか

データをNULLで扱い、空文字・NULLの違いによるリスクを減らす

空文字の場合でも問題が発生しないよう、漏れなく修正する仕組みを作る

暗黙的な型変換

LPADやSUBSTRなどの標準関数、ユーザー定義関数、プロシージャを呼び出す際に、引数のデータ型不一致によるエラーが発生 について

WHRER句や外部結合のON句で値を比較する際にデータ型不一致によるエラーが発生 について

Java側の修正 について

不具合発生時の横展開対応が困難 について

一時表（TEMPORARY TABLE）

対応内容

互換性の無い関数／パッケージ

NVL／COALESCE

対応方法

引数に文字列の長さを指定する関数／長さ自体を返却する関数

対応方法

※1 文字コードについて

※2 octet_lengthの動作について

ROWNUM

RID

ファイル出力（UTL_FILEパッケージ）

対応方法

レコード型

引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数

修正イメージ

サンプルコード

引数の上限数

SELECT ... INTO 変数 の挙動

対応方法

カーソルの挙動

FETCH（読み込む行が存在しない場合）

サンプルコード

トランザクション制御

自立型トランザクション

DBLINK利用時に定義する接続先の情報について

DBLINKでプロシージャを実行する方法について

性能面の問題について

外部結合演算子

PL/pgSQLの製造・単体テストについて

期間・体制

製造について

気を付けた点

単体テストについて

実行ツールについて

テストの確認内容について

テストパターンの洗い出しについて

よかった点

反省点

製造時のチェックリストの形式化

データパターン観点の試験の不足

余談

終わりに

おまけ：（気が向いたら）加筆するかもしれないリスト

PostgreSQL　エンタープライズ・コンソーシアムのサイト

`IS NULL`、`IS NOT NULL`演算子の比較対象の値の修正が必要について

空文字に対するキャストに注意が必要について

文字列結合演算子（`||`）から`CONCAT関数`への修正が必要について

Java側のNULL判定が効かなくなるなど、修正が必要について

`LPAD`や`SUBSTR`などの標準関数、ユーザー定義関数、プロシージャを呼び出す際に、引数のデータ型不一致によるエラーが発生について

WHRER句や外部結合のON句で値を比較する際にデータ型不一致によるエラーが発生について

Java側の修正について

不具合発生時の横展開対応が困難について

ファイル出力（`UTL_FILE`パッケージ）

`SELECT ... INTO 変数` の挙動