やりたいこと
Redshiftに蓄積しているデータを、日次処理で
既存の行を置き換えてマージ操作を実現する にあるような方法で、更新していきます。
-- 更新するデータを準備
create temp table stage (like target);
insert into stage
select * from source
where source.filter = 'filter_expression';
-- 集計済みデータから更新されるレコードを削除
begin transaction;
delete from target
using stage
where target.primarykey = stage.primarykey;
-- 更新したデータを投入
insert into target
select * from stage;
-- ※deleteが成功してinsertが失敗した時にcommitされるのは困る
end transaction;
RedshiftにPostgreSQLの cli psql で -f オプションや \iコマンドでスクリプトを実行した場合に、複数のSQL文があり途中で失敗した場合に止まることなく、最後のSQL文が実行されてしまいます。
トランザクションを開始後に任意のDMLが失敗した場合は、 psqlを終了させ、psql が終了した時点で (コミットしていないので)トランザクションをロールバックさせたいです。
TL;DR
$ psql --set ON_ERROR_STOP=on -h db_host -U db_user -d database_name -p 5439 -f path/to/scripts.sql
ON_ERROR_STOP オプション
psqlの ON_ERROR_STOP オプションを利用して、DMLでエラーが発生した場合に処理を中断させます。
デフォルトではエラーの後もコマンド処理は続行されます。 この変数がonに設定されていると、代わりに即座に停止します。 対話モードではpsqlはコマンドプロンプトに戻ります。 これ以外ではpsqlは終了し、エラーコード1を返す致命的エラー条件と区別できるように、エラーコード3を返します。 どちらの場合でも、現在実行中のスクリプト（トップレベルのスクリプト、もしあれば関連性を持つ他のスクリプトすべて）は即座に終了します。 トップレベルのコマンド文字列が複数のSQLコマンドを含む場合、その時点のコマンドで処理は終了します。
スクリプトファイル内で指定する方法
例えば、スクリプトファイルの先頭に \set コマンドで指定します。
\set ON_ERROR_STOP
-- 更新するデータを準備
create temp table stage (like target);
〜 以下同じ 〜
例えば、target テーブルに insert intoで追加するSQL文で stagingテーブルを no_found_table と誤った場合は下の様になります。
=> \i load_data.sql
CREATE TABLE
INSERT 0 2525484
BEGIN
DELETE 2525484
psql: load_data.sql:16: ERROR: relation "no_found_table" does not exist
=>
DELETE したレコード数の次はSQL文のエラーで止まっています。
psqlコマンドの引数に指定する方法
引数 --set で指定します。
$ psql --set ON_ERROR_STOP=on -h db_host -U db_user -d database_name -p 5439
前述の スクリプトファイル内で指定する方法 と同じ実行結果になります。
ON_ERROR_STOP で失敗したことを検知したい。
対話モードでなければ、psqlの戻り値が 3 に設定されます。 -fオプションでスクリプトフィアルを指定するか、 標準入力から渡します。
$ cat load_data.sql | psql -h db_host -U db_user -d database_name -p 5439
CREATE TABLE
INSERT 0 2525484
BEGIN
DELETE 2525484
psql: load_data.sql:16: ERROR: relation "no_found_table" does not exist
$ echo $?
ON_ERROR_STOPは下のいずれかの方法で指定します。
- スクリプトファイル内に
\set ON_ERROR_STOPを記載する - psqlに
-set ON_ERROR_STOP=onオプションを指定する
psql終了後に echo $? を実行すると、戻り値が3であることを確認できます。
BEGINで開始したトランザクションは、スクリプトがエラーになった時点でpsqlが終了し、その契機でロールバックが自動的に行われます。その結果 DELETEしたレコードがロールバックされてています。