はじめに
サーバーリプレース作業にて、データベースを Oracle 11g から PostgreSQL 9.6 に移行作業をしています。
OracleからPostgreSQL移行について
Oracle 用に作成していたバッチにSQL*Loader コマンドを使用してCSVファイルをテーブルにインポートする処理があり、psqlの¥COPYコマンドに置き換えて運用していました。
現象
先日、対象テーブルに該当データが存在していないという報告がありました。
原因
ログを確認したら一意制約違反エラーによりロールバックされていました。
どうも運用的に既に登録済みの一部のデータがCSVファイルに入ることがあるようで、運用開始から１週間くらい問題が発生しなかったのはたまたまだったようです。
では、Oracle の時に問題にならなかったのはどういうことなのか調べると、Oracle 用のSQL*Loader コマンドには、ERRORS(エラーの許容最大数)というオプションがあり、無制限(-1)に指定されていました。これにより一意制約違反エラーがあった場合でも許容されていたのです。
対応
psqlの¥COPYコマンドには、残念ながらERRORS(エラーの許容最大数)というオプションがありません。
今回に関しては、一意制約違反エラーのみ無視して登録してくれればいいわけです。
PostgreSQL 向けの高速データロードユーティリティ「pg_bulkload」には、制御オプションにDUPLICATE_ERRORS(一意制約違反の許容件数)があり、無制限(-1)に指定することが出来ます。これだと思ったんですが、Windows版は長いこと更新がストップしているということで採用するのをやめました。
PostgreSQL9.5以降から、INSERTにオプションのON CONFLICT句を追加され、主キー制約などに反するINSERTを無視する(DO NOTHING)ことが出来るようになりました。
第21回 PostgreSQL 9.5について（前編） （2）INSERTでUPSERT機能の追加
よって、下記の手順で登録すれば良さそうです。
- 一時テーブルを用意する。
- ¥COPYコマンドで一時テーブルにインポートする。
- 一時テーブルから対象テーブルにINSERTのON CONFLICT DO NOTHINGを使用して登録する。
一時テーブルですが当初は対象テーブルの名前を変更した実テーブルを作成しましたが、今後同じようなことを考えると汎用性を持たせたい。テーブル定義の複製が出来れば、どのテーブルでも可能になる。
テーブルの複製は下記の方法で出来ます。likeオプションは状況に応じたものを指定(空白区切りで複数指定可能)すればいいです。
CREATE TABLE table_name (LIKE source_table [like_option...]);
| likeオプション | 内容 |
|---|---|
| INCLUDING DEFAULTS | DEFAULT 制約をコピーします。 |
| INCLUDING IDENTITY | カラムへの IDENTITY の指定がコピーされます。カラムに割り当てられるシーケンスはコピー元とコピー先で異なります。 |
| INCLUDING CONSTRAINTS | CHECK 制約がコピーされます。 |
| INCLUDING INDEXES | PRIMARY KEY 、 UNIQUE 、 EXCLUDE の各制約がコピーされます。 |
| INCLUDING COMMENTS | カラム、制約、インデックスに関するコメントがコピーされます。 |
| INCLUDING STATISTICS | 拡張統計情報がコピーされます。 |
| INCLUDING STORAG | カラム定義に関する STORAGE 設定がコピーされます。 |
| INCLUDING ALL | 全てのオプションを指定したのと同じです。 |
もう一工夫として、一時テーブルをTEMPORARY TABLEとして作成することで接続したセッションの間にしか生存できないテーブルとなります。
CREATEの後にTEMPORARY又はTEMPを付けてテーブルを作成し、コミットしたら自動的に削除(DROP)するようにします。
CREATE TEMP TABLE table_name (LIKE source_table [like_option...]) ON COMMIT DROP;
最終的には下記のようになりました。
BEGIN;
CREATE TEMP TABLE test_temp (LIKE test INCLUDING DEFAULTS) ON COMMIT DROP;
¥COPY test_temp FROM 'C:\Work\test.csv' csv;
INSERT INTO test SELECT * FROM test_temp ON CONFLICT DO NOTHING;
COMMIT;
最後に
これで無事に一意制約違反エラーでロールバックされずに登録出来るようになりました。
ちなみに登録するデータが重複していたら、更新に切り替えることも出来ます。
ON CONFLICT DO NOTHING から ON CONFLICT DO UPDATE SET にして列名と更新値を指定します。