Go to Qiita Advent Calendar 2024 Top
LoginSignup
4
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@yaju(やじゅ)

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

Last updated at Posted at 2020-09-22

はじめに

サーバーリプレース作業にて、データベースを Oracle 11g から PostgreSQL 9.6 に移行作業をしています。
OracleからPostgreSQL移行について

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回　トランザクションについて

事象 PostgreSQL Oracle
トランザクション中にエラー発生 トランザクション自体がエラーにされる 単に無視される、トランザクション全体には影響を及ぼさない
DDL実行時の動作の違い DDLもトランザクションの一部である
例えばトランザクション中にDROP TABLEを実行し最後にROLLBACKすれば、DROPしたテーブルが元に戻る		 DDLはトランザクションの一部にはならない
例えばトランザクション中に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５に同じデータが含まれる

検証用テーブル

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
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
続行するには何かキーを押してください . . .

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 のトランザクション制御の違い

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
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
続行するには何かキーを押してください . . .

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
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
続行するには何かキーを押してください . . .

select count(*) from mybook
0

検証④ ON_ERROR_ROLLBACK=on

psqlでは、1度エラーが出ると以降のSQLは全てエラー(commit/rollbackするまで)になります。エラーは無視して、OKなSQLだけ実行したい場合、ON_ERROR_ROLLBACK onを付けます。
仕組みはエラーが発生した時に自動でロールバックします(これはバックエンドの機能ではありません)。これは、psqlが内部で、すべてのクエリの実行をSAVEPOINT/RELEASEで括ることで実現しています。
エラーの後にロールバックしなければいけない理由

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
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
続行するには何かキーを押してください . . .

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句の実装
【PostgreSQL】psqlのCOPYコマンドによるCSVインポートで重複エラーを回避する

今回はテーブルのテーブル構造をコピーしてテンポラリーテーブル(CREATE TEMP TABLE LIKE)を制約なしに作成します。
テーブル構造をコピーして新しいテーブルを作成する(CREATE TABLE ... LIKE)

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
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
続行するには何かキーを押してください . . .

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を選択させたかったんだけど、非対話式モードでは出来ないってことが分かった。

4
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?