【PostgreSQL】psqlのスクリプトエラーを無視し続行させる

# はじめに
サーバーリプレース作業にて、データベースを Oracle 11g から PostgreSQL 9.6 に移行作業をしています。
[OracleからPostgreSQL移行について](https://qiita.com/yaju/items/7cf5ab6987ecd1d83b13)

OracleのSQL*Loader（CSV形式などのテキストファイルからデータベース内にデータを取り込むためのツール）には、エラーを許容させるERRORSパラメータがあります。途中でエラーが発生してもERRORSに指定された値のエラー数を超えるまでは、挿入されたデータはコミットされます。
PostgreSQLのpsqlには、ERRORSパラメータのようなものはありません。調べたところでは、pg_bulkload でDUPLICATE_ERRORSというのがあり一意制約違反の許容件数が対応しています。しかし、Windows版のpg_bulkloadは安定性に欠けているとのこともあり、長い間Windowsサポートを維持していません。

OracleのSQL*Loaderを使用したデータ取込用バッチをPostgreSQLに移植した時に、ERRORSパラメータを考慮する必要があったため、調査したことをまとめました。

# OracleとPostgreSQLのトランザクションの違い
[オススメ！OSS-DB情報 第1回　トランザクションについて](https://oss-db.jp/dojo/dojo_01)

|事象|PostgreSQL|Oracle|
|:--|:--|:--|
|トランザクション中にエラー発生|トランザクション自体がエラーにされる|単に無視される、トランザクション全体には影響を及ぼさない|
|DDL実行時の動作の違い|DDLもトランザクションの一部である<br>例えばトランザクション中にDROP TABLEを実行し最後にROLLBACKすれば、DROPしたテーブルが元に戻る|DDLはトランザクションの一部にはならない<br>例えばトランザクション中にDROP TABLEを実行した時点で「自動 COMMIT」され、それ以前の更新が自動的にデータベースに反映されるため、DROPしたテーブルは元に戻らない|
|ツール終了時のトランザクション|psqlにてBEGINでトランザクションを開始後、COMMITせず終了させた場合、実行中のトランザクションはROLLBACKされ変更はキャンセルされる|sqlplusにてBEGINでトランザクションを開始後、COMMITせず終了させた場合、「自動 COMMIT」され、それ以前の更新が自動的にデータベースに反映される|

# 前提
PostgreSQLのpsqlでは、デフォルトで自動コミット(`autocommit`)が「有効」になっています。
OracleのSQL*Plusでは、デフォルトで自動コミット(`autocommit`)は「無効」になっています。

自動コミットの場合、BEGINやCOMMITを明示的に発行しないと、SQLの実行前にBEGINが暗黙的に発行され、SQLエラーなく終了すれば自動でSQLの実行結果がコミットされます。
BEGINが明示的に発行された場合は、COMMITが発行されるまでトランザクションがコミットされることはありません。

今回は、デフォルト設定のままを前提とします。

# 検証方法
下記のバッチを実行してどうなるかを検証します。
エラーは一意制約違反で統一しています。

```
正常なSQL1;
エラーになるSQL2;　-- SQL1と同じデータとなっている
正常なSQL3;
エラーになるSQL4;　-- SQL４内に同じデータが含まれる
正常なSQL5;
エラーになるSQL6;  -- SQL５に同じデータが含まれる
```

検証用テーブル

```sql:mtbook.sql
create table mybook (
  id integer, 
  name varchar(10),
  primary key(id)
)
```

test1〜3.csvは単一行、test4〜6.csvは複数行としています。

```:test1.csv
"id","name"
1,"テスト"
```
```:test2.csv
"id","name"
1,"テスト"
```
```:test3.csv
"id","name"
2,"テスト2"
```
```:test4.csv
"id","name"
3,"テスト3"
3,"テスト3"
4,"テスト4"
```
```:test5.csv
"id","name"
5,"テスト5"
6,"テスト6"
```
```:test6.csv
"id","name"
6,"テスト6"
7,"テスト7"
```
## 検証① BEGINの暗黙的発行
自動コミットとなります。

```:test.bat
@echo off
cd %~dp0

SET PGPATH=C:\PostgreSQL\9.6\bin\
SET PGPASSWORD=postgres
%PGPATH%psql.exe -U postgres -d test -f test.sql
```

```sql:test.sql
SET CLIENT_ENCODING TO 'UTF8';
BEGIN;
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test1.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test2.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test3.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test4.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test5.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test6.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
COMMIT;
```

## 結果① BEGINの暗黙的発行
エラーが発生したクエリ(COPY句)は無視され、正常なクエリだけがコミットされます。
test2.csvとtest4.csvとtest6.csvのデータは一意制約違反のデータが含まれているため登録されません。

```
SET
COPY 1
psql:test.sql:3: ERROR:  duplicate key value violates unique constraint "mybook_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY mybook, line 2
COPY 1
psql:test.sql:5: ERROR:  duplicate key value violates unique constraint "mybook_pkey"
DETAIL:  Key (id)=(3) already exists.
CONTEXT:  COPY mybook, line 3
COPY 2
psql:test.sql:7: ERROR:  duplicate key value violates unique constraint "mybook_pkey"
DETAIL:  Key (id)=(6) already exists.
CONTEXT:  COPY mybook, line 2
続行するには何かキーを押してください . . .
```

```sql
select count(*) from mybook
4
```

|id|name|
|:--|:--|
|1|テスト|
|2|テスト2|
|5|テスト5|
|6|テスト6|

## 検証② single-transaction
-fオプションを使用してpsqlがスクリプトを実行する時、`single-transaction`オプションを付けると、スクリプトをBEGIN/COMMITで囲み、単一トランザクション内でスクリプトを実行します。ただし、スクリプト内部でBEGIN、COMMIT、ROLLBACKを使用している場合、このオプションは想定した効果をもたらしません。
[psql -c command と psql -f filename のトランザクション制御の違い](https://www.greptips.com/posts/470/)

```:test2.bat
@echo off
cd %~dp0

SET PGPATH=C:\PostgreSQL\9.6\bin\
SET PGPASSWORD=postgres
%PGPATH%psql.exe -U postgres -d test -f test.sql --single-transaction
```

```sql:test.sql
SET CLIENT_ENCODING TO 'UTF8';
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test1.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test2.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test3.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test4.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test5.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test6.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
```

## 結果② single-transaction
test2.csvが一意制約違反で途中でエラーとなるため、全てロールバックされて件数は0件となります。

```
SET
COPY 1
psql:test.sql:3: ERROR:  duplicate key value violates unique constraint "mybook_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY mybook, line 2
psql:test.sql:4: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:test.sql:5: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:test.sql:6: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:test.sql:7: ERROR:  current transaction is aborted, commands ignored until end of transaction block
続行するには何かキーを押してください . . .
```

```sql
select count(*) from mybook
０
```

## 検証③ BEGINの明示的発行
BEGINとCOMMITを明示的に付けます。

```:test３.bat
@echo off
cd %~dp0

SET PGPATH=C:\PostgreSQL\9.6\bin\
SET PGPASSWORD=postgres
%PGPATH%psql.exe -U postgres -d test -f test２.sql
```

```sql:test２.sql
SET CLIENT_ENCODING TO 'UTF8';
BEGIN;
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test1.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test2.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test3.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test4.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test5.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test6.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
COMMIT;
```

## 結果③ BEGINの明示的発行
test2.csvが一意制約違反で途中でエラーとなるため、全てロールバックされて件数は0件となります。

```
SET
BEGIN
COPY 1
psql:test.sql:4: ERROR:  duplicate key value violates unique constraint "mybook_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY mybook, line 2
psql:test.sql:5: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:test.sql:6: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:test.sql:7: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:test.sql:8: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
続行するには何かキーを押してください . . .
```

```sql
select count(*) from mybook
0
```

## 検証④ ON_ERROR_ROLLBACK=on
psqlでは、1度エラーが出ると以降のSQLは全てエラー(commit/rollbackするまで)になります。エラーは無視して、OKなSQLだけ実行したい場合、`ON_ERROR_ROLLBACK on`を付けます。
仕組みはエラーが発生した時に自動でロールバックします(これはバックエンドの機能ではありません)。これは、psqlが内部で、すべてのクエリの実行をSAVEPOINT/RELEASEで括ることで実現しています。
[エラーの後にロールバックしなければいけない理由](https://web.archive.org/web/20110610035711/http://postgresql.g.hatena.ne.jp/iakio/20081120/1227202633)

```:test４.bat
@echo off
cd %~dp0

SET PGPATH=C:\PostgreSQL\9.6\bin\
SET PGPASSWORD=postgres
%PGPATH%psql.exe -U postgres -f -d test test2.sql  --set ON_ERROR_ROLLBACK=on
```

```sql:test2.sql
SET CLIENT_ENCODING TO 'UTF8';
BEGIN;
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test1.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test2.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test3.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test4.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test5.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY test.mybook(id,name) FROM 'C:/Temp/Test/test6.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
COMMIT;
```

## 結果④ ON_ERROR_ROLLBACK=on
エラーが発生したクエリ(COPY句)は無視(自動でロールバック)され、正常なクエリだけがコミットされます。
test2.csvとtest4.csvとtest6.csvのデータは一意制約違反のデータが含まれているため登録されません。

```
SET
BEGIN
COPY 1
psql:test.sql:4: ERROR:  duplicate key value violates unique constraint "mybook_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY mybook, line 2
COPY 1
psql:test.sql:6: ERROR:  duplicate key value violates unique constraint "mybook_pkey"
DETAIL:  Key (id)=(3) already exists.
CONTEXT:  COPY mybook, line 3
COPY 2
psql:test.sql:8: ERROR:  duplicate key value violates unique constraint "mybook_pkey"
DETAIL:  Key (id)=(6) already exists.
CONTEXT:  COPY mybook, line 2
COMMIT
続行するには何かキーを押してください . . .
```

```sql
select count(*) from mybook
4
```

|id|name|
|:--|:--|
|1|テスト|
|2|テスト2|
|5|テスト5|
|6|テスト6|

## 検証⑤ INSERTのON CONFLICT DO NOTHING
PostgreSQL 9.5からINSERT文にON CONFLICT句が追加されました。このON CONFLICT句には2つの動作（DO NOTHING句とDO UPDATE句）を指定できます。
DO NOTHING句は、テーブルに挿入したい行がまだ存在しない（制約に違反する行が存在しない）場合は通常の挿入処理となります。一方、テーブルに挿入したい行がすでに存在する（制約に違反する行がすでに存在する）場合はエラーとして終了するのではなく、対象行へ何の処理も行わずにそのまま完了します。
[PostgreSQL 9.5新機能レビュー UPSERT（ON CONFLICT句）とGROUPING SETS句/CUBE句/ROLLUP句の実装](https://thinkit.co.jp/article/9586)
[【PostgreSQL】psqlのCOPYコマンドによるCSVインポートで重複エラーを回避する](https://qiita.com/yaju/items/3adfb67100a59916ae8b)

今回はテーブルのテーブル構造をコピーしてテンポラリーテーブル(CREATE TEMP TABLE LIKE)を制約なしに作成します。
[テーブル構造をコピーして新しいテーブルを作成する(CREATE TABLE ... LIKE)](https://www.dbonline.jp/postgresql/table/index14.html)

CSVデータをテンポラリーテーブル側に挿入した後に、INSERT INTO SELECTで対象テーブルに挿入します。
何故このようにしているのかというと、実際のデータ登録では1日2回CSVデータを取り込むようになっており、状況によってはデータが重なることがあるからです。


```:test5.bat
@echo off
cd %~dp0

SET PGPATH=C:\PostgreSQL\9.6\bin\
SET PGPASSWORD=postgres
%PGPATH%psql.exe -U postgres -d test -f test3.sql
```

```sql:test3.sql
SET CLIENT_ENCODING TO 'UTF8';
BEGIN;
CREATE TEMP TABLE mybook_temp (LIKE test.mybook INCLUDING DEFAULTS) ON COMMIT DROP;
\COPY mybook_temp(id,name) FROM 'C:/Temp/Test/test1.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY mybook_temp(id,name) FROM 'C:/Temp/Test/test2.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY mybook_temp(id,name) FROM 'C:/Temp/Test/test3.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY mybook_temp(id,name) FROM 'C:/Temp/Test/test４.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY mybook_temp(id,name) FROM 'C:/Temp/Test/test５.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
\COPY mybook_temp(id,name) FROM 'C:/Temp/Test/test６.csv' WITH(FORMAT CSV,HEADER ON,FORCE_NULL(id,name));
INSERT INTO test.mybook SELECT * FROM mybook_temp ON CONFLICT DO NOTHING;
COMMIT;
```

## 結果⑤ INSERTのON CONFLICT DO NOTHING
一意制約違反となるデータのみ対象外となり、それ以外のデータが全て登録されます。

```
SET
BEGIN
CREATE TABLE
COPY 1
COPY 1
COPY 1
COPY 3
COPY 2
COPY 2
INSERT 0 7
COMMIT
続行するには何かキーを押してください . . .
```

```sql
select count(*) from mybook
７
```

|id|name|
|:--|:--|
|1|テスト|
|2|テスト2|
|3|テスト3|
|4|テスト4|
|5|テスト5|
|6|テスト6|
|7|テスト7|

# 最後に
今回は検証として1つのテーブルを複数ファイルで行っていますが、一般的な使用では1つのファイルに1つのテーブルになると思います。
エラーの制御はクエリ単位で今回はCOPY句ですが、INSERT句でも同様な結果になります。

|オプション|説明|
|:--|:--|
|-c, --command=コマンド|単一の（SQLまたは内部）コマンドを一つだけ実行して終了|
|-f, --file=FILENAME|ファイルからコマンドを読み込んで実行後、終了|

psqlの-cと-fオプションは、どちらも実行後に終了なんですよね。最後の`COMMIT` or `ROLLBACK`のどちらかを後で別途コマンド実行させることは出来ないです。
試しに`BEGIN`とファイル実行し、別途コマンドで`COMMIT`すると「WARNING:  there is no transaction in progress」エラーになり、結果はロールバックされた状態になります。
中身を確認して自分の意思で`COMMIT` or `ROLLBACK`を決めたい場合は「対話式モード」なら可能です。


デフォルト状態でエラーを無視して続行する目的なら検証①で問題無いです。一意制約違反しかない状態なら検証⑤が有効でしょう。
元々この記事としては中身を確認した上で、`COMMIT` or `ROLLBACK`を選択させたかったんだけど、非対話式モードでは出来ないってことが分かった。